Get tab name formula

[Guest]

I pulled this formula from the web somewhere - it returns the tab name
=MID(CELL("filename"), FIND("]",CELL("filename"))+1,
LEN(CELL("filename"))-FIND("]", CELL("filename")))

But if I copy it to various sheets, F9 does not recalculate it correctly. I
actually have to go to each sheet and do Shift+F9 or F9 to make it display
the right name. Even if my workbook is set to Automatic Calculation, this
cell will not update until I go to that tab and F9 it.

Any advice?

 

[Peo Sjoblom]

You need to reference a cell (anyone will do even the cell the formula is
in)

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

it can be simplied since a sheet name only can have a certain number of
characters

 

[Guest]

Thanks Peo, but I don't quite understand (sorry!).

When I copy this formula into any cell, including A1, it just returns a
#VALUE! error. I'm missing something, right?

Daniel

You need to reference a cell (anyone will do even the cell the formula is
in)

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

it can be simplied since a sheet name only can have a certain number of
characters

--

Regards,

Peo Sjoblom

I pulled this formula from the web somewhere - it returns the tab name
=MID(CELL("filename"), FIND("]",CELL("filename"))+1,
LEN(CELL("filename"))-FIND("]", CELL("filename")))

But if I copy it to various sheets, F9 does not recalculate it correctly. I
actually have to go to each sheet and do Shift+F9 or F9 to make it display
the right name. Even if my workbook is set to Automatic Calculation, this
cell will not update until I go to that tab and F9 it.

Any advice?

 

[Guest]

Sorry, my fault, I didn't save the file. now it works.
Thanks Peo.

You need to reference a cell (anyone will do even the cell the formula is
in)

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

it can be simplied since a sheet name only can have a certain number of
characters

--

Regards,

Peo Sjoblom

I pulled this formula from the web somewhere - it returns the tab name
=MID(CELL("filename"), FIND("]",CELL("filename"))+1,
LEN(CELL("filename"))-FIND("]", CELL("filename")))

But if I copy it to various sheets, F9 does not recalculate it correctly. I
actually have to go to each sheet and do Shift+F9 or F9 to make it display
the right name. Even if my workbook is set to Automatic Calculation, this
cell will not update until I go to that tab and F9 it.

Any advice?