Function to get worksheet name

S

ssuryarao

Its seems pretty simple.

1. which functions returns parameters like worksheet name ?

Same way can i get what is file name ?

Thanks in advance..
 
H

Harlan Grove

[email protected] wrote...
Its seems pretty simple.

1. which functions returns parameters like worksheet name ?

Same way can i get what is file name ?
Click to expand...

The only built-in function that returns these is CELL. You could use
CELL("Filename",A1) to get both the workbook filename and the worksheet
name corresponding to the range reference 2nd argument *IF* you've
already saved the file. In that case, it'll return something like

D:\test\[foo.xls]bar

You can parse the worksheet name using

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

the base filename using

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

and the full pathname using

=SUBSTITUTE(LEFT(CELL("Filename",A1),FIND("]",
CELL("Filename",A1))-1),"\[","\",LEN(CELL("Filename",A1))
-LEN(SUBSTITUTE(CELL("Filename",A1),"\[","\")))
 
K

KL

Hi,

Just in case here is the formula that takes care of the possible situation
whereby you have only one sheet in the workbook and its name is the same as
the workbook's. Also, CELL("filename") will only work if the file has been
saved at least once and if the file is opened in a different language
system, the argument "filename" will need to be changed manually to the
corresponding word (e.g. in Spanish "nombrearchivo"):

=LOOKUP(REPT("z",255),SUBSTITUTE(MID(CELL("filename"),1+FIND({"*","]"},SUBSTITUTE(CELL("filename"),"\","*",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\","")))),255),".xls",""))

Regards,
KL
 
Top