Cell formula Error

[sachar]

Dear All,

With reference to the attach sample file, May I know what is the wrong with CELL formula in the Cell”A2&A3” which highlighted with Yellow colour that is returning the “current Sheet name” only, however, there is 2nd Sheet name “01 Feb”. & 3rd “02 Feb”.

Basically, I’m trying to get the others Sheet’s name in column “A”.

Please help me?
Thanks.

 

[Hui]

A1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)
A2: =MID(CELL("filename",'01 Feb.'!A1),FIND("]",CELL("filename",'01 Feb.'!A1))+1,999)
A3: =MID(CELL("filename",'02 Feb.'!A1),FIND("]",CELL("filename",'02 Feb.'!A1))+1,999)

If you want to use a variable to contuinue to find all worksheets
in A2:
=MID(CELL("filename",INDIRECT("'0"&ROWS(A$2:A2)&" Feb.'!A1")),FIND("]",CELL("filename",INDIRECT("'0"&ROWS(A$2:A2)&" Feb.'!A1")))+1,999)

Copy down

 

[sachar]

Dear Sir,

Thanks to solved my problem. Can you explore, how it works this interlink
formula INDIRECT("'0"&ROWS(A$2:A2)&" Feb.'!A1") to get the result?

=MID(CELL("filename",INDIRECT("'0"&ROWS(A$2:A2)&" Feb.'!A1")),FIND("]",CELL("filename",INDIRECT("'0"&ROWS(A$2:A2)&" Feb.'!A1")))+1,999)

Thanks in advance.

 

[Hui]

Indirect makes a valid reference from the included text
In your case it is using the text of: "'0"&ROWS(A$2:A2)&" Feb.'!A1"

So in Row 2 Rows A2:A2 is 1 so the text becomes
"'01 Feb.'!A1"

In Row 3 "'0"&ROWS(A$2:A3)&" Feb.'!A1"
becomes
"'02 Feb.'!A1"
etc as it is copied down