AUTOMATICALLY NAME A FIELD WITH THE TAB NAME in excel

[Guest]

How would I have the name of the spreadsheet tab (on the bottom) to
automatically appear in a cell field on the spreadsheet?

 

[Guest]

After saving the workbook

=RIGHT(CELL("filename"),(LEN(CELL("filename"))-FIND("]",CELL("filename"))))

 

[JE McGimpsey]

Careful - using

CELL("filename")

will return the value of the last sheet *calculated*, even if it's not
the sheet that the formula is entered in.

Instead, use the complete syntax, which provides a reference to a cell
in the sheet, e.g.,:

CELL("filename",A1)

The formula below can be written with fewer function calls:

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

where 255 is just a big number (anything over 30 will do...)

For more options, see

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

 

[Gord Dibben]

John

There is a problem associated with your formula.

Use this instead.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

To see the reason for adding the cell reference see Bob Phillips's site.

http://www.xldynamic.com/source/xld.xlFAQ0002.html


Gord Dibben MS Excel MVP

 

[JamesI]

This is exactly what I was looking for. Thank you.
JamesI

After saving the workbook

=RIGHT(CELL("filename"),(LEN(CELL("filename"))-FIND("]",CELL("filename"))))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

How would I have the name of the spreadsheet tab (on the bottom) to
automatically appear in a cell field on the spreadsheet?

 

[Gord Dibben]

That is NOT exactly what you were looking for and can give an erroneous
result.

You must use a cell reference in the formula like so

=RIGHT(CELL("filename",A1),(LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))

If you omit a cell reference the results will be incorrect if you switch to
another sheet, calculate then switch back to original sheet.

Easy enough to experiment with the two formulas to see the results.


Gord Dibben MS Excel MVP

This is exactly what I was looking for. Thank you.
JamesI

After saving the workbook

=RIGHT(CELL("filename"),(LEN(CELL("filename"))-FIND("]",CELL("filename"))))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

How would I have the name of the spreadsheet tab (on the bottom) to
automatically appear in a cell field on the spreadsheet?