how to pick sheet name in a cell

[Ajinkya]

Dear Friends,


Could you please help to pick sheet name in a cell?


Thanks.

 

[NARAYANK991]

Hi Ajinkya ,


This works only if your workbook has already been saved.


1. Use the =CELL("filename") formula to get the complete filename , including the hard drive path name , the workbook name and the worksheet name.


2. Use the FIND function to find the "]" ( square right bracket ) in this text.


3. To the right of this , is the sheet name.


The full formula is :


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


To shorten this , what you can do is to create a named range , and call it File_Name , and in the Refers To box , type in the formula =CELL("filename"). Then the above formula for the Sheet Name becomes :


=RIGHT(File_Name,LEN(File_Name)-FIND("]",File_Name))


Narayan

 

[Ajinkya]

Dear Narayan,


Thanks, what a logic used to get it done.


thanks once again its done


Rgds,

Ajinkya

 

[jskushawah]

Try this formula


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