Sheet Name on Worksheet

[sachar]

Dear All,

In course of apply the INDIRECT formula, the sheet name required on a worksheet column for the "ref_text" . Are there any formula to bring sheets name on a worksheet in one column.

Please help me?

 

[Hui]

You can use
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,999)
for the current sheets names

or
=MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,999)

for Sheet2's name

 

[bosco_yip]

Or try,

1] Define name >>

Name, enter : shtname

Refer to, enter : =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,31)

2] In B2, formula copy down :

=IF(ROWS(A$1:A1)>COUNTA(shtname),"",INDEX(shtname,ROWS(A$1:A1)))

Regards
Bosco