Placing the sheet's name in a cell

[borolo222]

Hi,

Is it possible to make a reference in a cell to the sheet's name?

I use a workbook with lots of sheets and i have to use their name inside the respective sheets.

thanks,

Emilio

 

[jim may]

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

 

[ljungren13]

Would something like this work?

Function SheetName()

   SheetName = ActiveSheet.Name

End Function

 

[prabby25101981]

This is how you can pickup a sheet's name in a cell -

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

Then you can always use INDIRECT function along with concatenate or address to create a range and use it in the formula. Do you need help with that ?

 

[borolo222]

Thanks for your quick response. I'll give them a shot.

Tried the three of them and all worked. don't understand how that formula works, can you explain it to me please?

thanks

 

[ljungren13]

Thanks for your quick response. I'll give them a shot.

Tried the three of them and all worked. don't understand how that formula works, can you explain it to me please?

thanks

=CELL("filename",A1)

tells you the "filename" of cell A1

ex:C:\Documents and Settings\User\My Documents\[Test.xls]Sheet1

the rest is just cropping that string to say just "Sheet1" by searching for "]"

 

[borolo222]

Great. Didn't know that function "cell".

thanks