excel function to get tab name

D

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.
 
H

Harlan Grove

Don said:
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.
 
D

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.
 
H

Harlan Grove

Don Guillett said:
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.
 
D

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
 
D

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"))
 
H

Harlan Grove

Don Guillett said:
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].
 
H

Harlan Grove

Don Guillett said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top