AUTOMATICALLY NAME A FIELD WITH THE TAB NAME in excel
 

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

AUTOMATICALLY NAME A FIELD WITH THE TAB NAME in excel
 

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.


"jygong" wrote:

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

AUTOMATICALLY NAME A FIELD WITH THE TAB NAME in excel
 

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

In article ,
John Bundy (remove) wrote:

After saving the workbook

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

AUTOMATICALLY NAME A FIELD WITH THE TAB NAME in excel
 

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


On Mon, 29 Oct 2007 15:25:00 -0700, John Bundy (remove)
wrote:

After saving the workbook

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

AUTOMATICALLY NAME A FIELD WITH THE TAB NAME in excel
 

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


"John Bundy" wrote:

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.


"jygong" wrote:

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

AUTOMATICALLY NAME A FIELD WITH THE TAB NAME in excel
 

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


On Wed, 8 Jul 2009 10:49:01 -0700, JamesI
wrote:

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


"John Bundy" wrote:

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.


"jygong" wrote:

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