excel function to get tab name

[Don]

On the first tab in my worksheet, I have several cells in a colum in which
the cell reference returns the value of cell A1 from the subsequent tabs.
For example, the workbook has 10 tabs named Tab1, Tab2...Tab10, and in Tab1,
the formula in cell A2 is "=Tab2!A1", and the formula in cell A3 is
"=Tab3!A1"....cell A10 is "Tab10!A1". Sometimes, I will add and delete
these subsequent tabs (Tab2 through Tab10) and then have to manually re-link
the references to A1 on each of these tabs back to the column on Tab1. Is
there some way to do this with a relative reference formula? I know there is
a way to get this with some kind of vb/vba/macro, but I was looking for some
way to do it with a formula. Thanks.

 

[Don Guillett]

try
=INDIRECT("Tab"&ROW(A1)&"!A1")

 

[Harlan Grove]

For example, the workbook has 10 tabs named Tab1, Tab2...Tab10, and
in Tab1, the formula in cell A2 is "=Tab2!A1", and the formula in
cell A3 is "=Tab3!A1"....cell A10 is "Tab10!A1".   Sometimes, I
will add and delete these subsequent tabs (Tab2 through Tab10) and
then have to manually re-link the references to A1 on each of these
tabs back to the column on Tab1.  Is there some way to do this with
a relative reference formula?  I know there is a way to get this
with some kind of vb/vba/macro, but I was looking for some way to
do it with a formula.  Thanks.

Relative worksheet addressing in Excel, the last of the 2D
spreadsheets (Excel workbooks are at best collections of 2D
spreadsheets with a wee tiny bit of 3D-like reference syntax),
requires either VBA or XLM, so will trigger macro warnings whenever
you load workbooks containing such code.

Is this allowable? If so, search the Google Groups archive for the
term "relative worksheet". There have been several posts in this in
the past.

 

[Don]

Can't get that formula to work as the tab names may change, be moved, or
deleted. I'm basically looking for some kind of forumla that will return the
name of a spreadsheet tab, but not the tab I'm currently in, but the name of
the next tab, or the tab after that one, or the one after that one, etc. etc.
etc.

 

[Don Guillett]

You said, Tab1, Tab2, etc

 

[Harlan Grove]

You said, Tab1, Tab2, etc

....

And how long have you been following newsgroups that the concept of
OPs oversimplifying examples in their postings comes as a surprise?

Even so, if the OP did start off with worksheets named Tab1, Tab2,
etc., then deletes Tab3, how does your approach automatically adjust
for that?

Here's another new concept for you: answer the question the OP asks
rather than the question you want to answer.

 

[Don Guillett]

To make Harlan happy.
Sub listsheetsa1()
Dim i As Long
Dim lr As Long
Dim ws As Worksheet

With Worksheets("Main")
lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range(Cells(2, 1), Cells(lr, 3)).ClearContents
For i = 1 To Worksheets.Count
lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Set ws = Worksheets(i)
If UCase(ws.Name) <> "MAIN" Then
.Cells(lr, 1).Value = ws.Name
.Cells(lr, 2).Value = ws.CodeName
.Cells(lr, 3).Value = ws.Range("a1")
End If
Next i
End With
End Sub

 

[Don Guillett]

Wouldn't this do that, assuming all named Tab1, Tab2, etc as in the OP.
Of course, it would leave a blank row
=IF(ISERR(INDIRECT("Tab"&(ROW(A1)&"!A1"))),"",INDIRECT("tab"&ROW(A1)&"!a1"))

 

[Harlan Grove]

Wouldn't this do that, assuming all named Tab1, Tab2, etc as in the
OP. Of course, it would leave a blank row

....

1. You're still insisting that the OP's actual worksheet names are
Tab1, Tab2, etc.

2. Your formula would handle worksheet deletion but not worksheet
insertion. OP did say "Sometimes, I will **ADD** and delete these
subsequent tabs" [emphasis mine].

 

[Harlan Grove]

To make Harlan happy.

....

Happier I'd be if you had provided something dynamic that would
automatically change when the user changes anything rather than a
macro that would need to be rerun manually.

Let me help you with some examples.

VBA udf approach:
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/da0f6e91b058ac38
(or http://tinyurl.com/4unhtr )

Use the showoff function in formulas like

Tab1!A2: =showoff($A$1,ROWS(A$2:A2))

filled down into Tab1!A10, so Tab1!A2:A10 would evaluate to the values
in Tab2:Tab10!A1, and would change automatically when worksheets were
inserted, deleted, renamed or rearranged.


XLM approach:
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/77d17cad6db2d825
(or http://tinyurl.com/4vq3dl )

Use the defined name WSLST in formulas like

Tab1!A2: =INDIRECT("'"&INDEX(WSLST,ROWS(A$1:A2))&"!A1")

with behavior similar to that of the previous udf when worksheets are
inserted, deleted, renamed or rearranged.

As I already told the OP, this was in the Google Groups archive.