[Tab] - referring to sheet / tab name in a formula?

[NickS]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Does anyone have any suggestions? (This is something that can be easily put into a footer for printing purposes, but i would like to use the sheet name in a formula on the sheet).

Similarly is there a way of concatenating formulae in such a way that I can incorporate the sheet name (the sheet name would be listed in a cell separately) - I have a summary sheet where each row contains data from individual sheets - the only way I can do this at the moment is 'edit/replace'. For example I want to do a bunch of look-ups in row 1 of my summary table from 'Form 1'! - can I have Form 1 listed in the first column of my row. This would then be followed by Form 2 in the second row, etc...

 

[CyberTaz]

Hi Nick -

Since the sheet tab is always visible on screen & the sheet name can be
included in a H/F when printing I guess there doesn't seem to be much of a
need for such a function - at least I have never seen one. However, a little
creativity will produce what you want but it will take more than just a
"simple" built-in Fx

There's an Information Fx by the name of CELL which will produce the sheet
name but includes the complete path: =CELL("filename")

You'd then need build that into a Fx that would extract only the last
portion of the result. As a simple example, if the sheet name is Summary
(which contains 7 letters) you could use:

=RIGHT(CELL("filename"),7)


HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

Hi Nick -

Since the sheet tab is always visible on screen & the sheet name can be
included in a H/F when printing I guess there doesn't seem to be much of a
need for such a function - at least I have never seen one. However, a little
creativity will produce what you want but it will take more than just a
"simple" built-in Fx

There's an Information Fx by the name of CELL which will produce the sheet
name but includes the complete path: =CELL("filename")

You'd then need build that into a Fx that would extract only the last
portion of the result. As a simple example, if the sheet name is Summary
(which contains 7 letters) you could use:

=RIGHT(CELL("filename"),7)


HTH |:>)
Bob Jones
[MVP] Office:Mac

Well, here's a way that will work no matter what the workbook name is:
1. go to Insert-Name-Define
2. for name enter a meaningful name like "SheetName" with no quotes.
3. In the refers to box enter the following formula:
=mid(get.document(1),search("]",get.document(1))+1,100)
4. Now, if you want the worksheet name in a cell, enter "=sheetname" in that
cell. Or, you can use the name sheetname in any formula where the text of
the sheet name is needed.

This will work fine with the following Caveat: If you attempt to copy the
worksheet by right clicking on the worksheet tab and choosing make a copy,
Excel will crash. If you are not going to do this it will work fine.

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Does anyone have any suggestions? (This is something that can be easily put
into a footer for printing purposes, but i would like to use the sheet name
in a formula on the sheet).

Similarly is there a way of concatenating formulae in such a way that I can
incorporate the sheet name (the sheet name would be listed in a cell
separately) - I have a summary sheet where each row contains data from
individual sheets - the only way I can do this at the moment is
'edit/replace'. For example I want to do a bunch of look-ups in row 1 of my
summary table from 'Form 1'! - can I have Form 1 listed in the first column
of my row. This would then be followed by Form 2 in the second row, etc...

See

http://mcgimpsey.com/excel/formulae/cell_function.html

 

[JE McGimpsey]

Since the sheet tab is always visible on screen & the sheet name can be
included in a H/F when printing I guess there doesn't seem to be much of a
need for such a function - at least I have never seen one. However, a little
creativity will produce what you want but it will take more than just a
"simple" built-in Fx

There's an Information Fx by the name of CELL which will produce the sheet
name but includes the complete path: =CELL("filename")

You'd then need build that into a Fx that would extract only the last
portion of the result. As a simple example, if the sheet name is Summary
(which contains 7 letters) you could use:

=RIGHT(CELL("filename"),7)

I provided a reference to a better way in my other post, but there's an
important caveat to using CELL().

If you don't include the reference argument, e.g.,

=CELL("Filename",A1)

or

=CELL("filename",Sheet2!A1)

then CELL() will return the name of the last sheet CALCULATED, which may
not be the sheet the cell belongs to...

 

[CyberTaz]

By jingo, I figured I'd leave something out... It's been too long

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[NickS]

THANK YOU ALL FOR YOUR HELP - THE 'CELL' FUNCTION IS WORKING A TREAT...

NOW HOW ABOUT PART 2? GOING THE OPPOSITE DIRECTION - SO USING SOME FORM OF CONCATENATION TO REFER TO THE SAME CELL ON VARIOUS WORKSHEETS BY COMBING A CELL REFERENCE WITH A WORKSHEET NAME? EG ='Form 1'!&C10 - BUT INSTEAD OF HARD CODING 'Form 1' I RFER TO A CELL CONTAINING THE TEXT 'Form 1' EG CELL A5 is 'Form 1' AND I PUT A FORMULA IN CELL B5 WHICH IS =A5&C10 SO THAT THIS CELL RETURNS THE CONTENTS OF CELL C5 ON SHEET Form 1?

 

[Bob Greenblatt]

THANK YOU ALL FOR YOUR HELP - THE 'CELL' FUNCTION IS WORKING A TREAT...

NOW HOW ABOUT PART 2? GOING THE OPPOSITE DIRECTION - SO USING SOME FORM OF
CONCATENATION TO REFER TO THE SAME CELL ON VARIOUS WORKSHEETS BY COMBING A
CELL REFERENCE WITH A WORKSHEET NAME? EG ='Form 1'!&C10 - BUT INSTEAD OF HARD
CODING 'Form 1' I RFER TO A CELL CONTAINING THE TEXT 'Form 1' EG CELL A5 is
'Form 1' AND I PUT A FORMULA IN CELL B5 WHICH IS =A5&C10 SO THAT THIS CELL
RETURNS THE CONTENTS OF CELL C5 ON SHEET Form 1?

That is EXACTLY what the INDIRECT function is for. Check help for syntax,
post back if you are having trouble.

 

[NickS]

Hi Bob,

Thanks for all your help - I'm keeping you busy today!

I can't figure the syntax.

Here's what I'm putting:

=INDIRECT("'"&A29&"'!"&$D$8)

where cell A29 contains Form 1

I want to refer to the sheet / tab called Form 1 and cell $D$8 of that sheet....

If you could help me figure the correct quotation marks, exclamation etc that would be much appreciated.

Thanks again,

Nick

 

[Bob Greenblatt]

Hi Bob,

Thanks for all your help - I'm keeping you busy today!

I can't figure the syntax.

Here's what I'm putting:

=INDIRECT("'"&A29&"'!"&$D$8)

where cell A29 contains Form 1

I want to refer to the sheet / tab called Form 1 and cell $D$8 of that
sheet....

If you could help me figure the correct quotation marks, exclamation etc that
would be much appreciated.

Thanks again,

Nick

=indirect("'"&A29&"'!$d$8")

Be careful, as you are using a relative reference for a29 and an absolute
one for d8. This may be exactly what you want, but bear in mind what will
happen if you copy this cell.

 

[NickS]

Thank you again Bob - all works fine (and yes I did want one to be relative and one absolute - the relative one picks up the Form Number from each row of my summary and the absolute one then refers to the same cell on each of the forms 1, 2, 3, 4 etc).