show tab names as text in spreadsheet

R

Rick Schultz

I'm looking for a way to show a tab name, within the same
workbook, in a cell as text. The tab name already exists
as a reference in a formula in the worksheet where I want
to see the name.

For example, if one tab in the workbook is named "Sales",
I'll have a formula already in the worksheet like:
='Sales'!S31.
I need to show, on the same line as that formula, what
tab in the workbook that amount comes from.

Any ideas?
 
R

Rick Schultz

Frank,

Almost there. While that formula gives me the name of
the sheet I have open, I'm looking for the sheet name I'm
referencing, through a formula, elsewhere in the
workbook. (that is, I want to see "Sales", the name of
the worksheet, on a different worksheet (say, "summary")).

Thanks for the very quick response - much appreciated.
 
D

David McRitchie

Hi Rick,
You've lost me, it sounds like you are asking what is the sheetname of
a sheet named "Summary".

=CELL("filename",summary!A1)

for the sheetname

=MID(CELL("filename",summary!A1),FIND("]",CELL("filename",Summary!A1))+1,255)

The only thing you get with this formula is that the sheetname returned will
change if the name of the sheet is changed from "Summary" to something else.

If you are trying to extract "sales"
B3: ='Sales'!S31.
C3: =magicalformula(B3) display Sales
you would need a user defined function (VBA coding)
and you would have to know exactly how the formula is contructed
i.e.
B3: = 'Sales'!S31 + 'Bargains'!S31
C3: = magicalformula(B3) display ???????

If you wanted to see the formula used in another cell then see
http://www.mvps.org/dmcritchie/excel/formula.htm#getformula

C3: =personal.xls!GetFormula(B3) display ='Sales'!S31
 
Top