INDIRECT FUNCTION TO GIVE FILE NAME in linked formula

[Anne Troy]

This is the linked formula:

='[90602.xlsm]Page 1'!$B$10

90602 is the invoice number.

How can I change the formula so that the 90602 comes from the cell to the left of the formula?

="'["&INDIRECT(A2)&".xlsm]Page 1'!$B$10" returns a #REF. Cells A2 and B10 both exist and contain the data I want.

Help!! THANKS in advance.

 

[Fluff]

How about

=INDIRECT("'["&A2&".xlsm]Page 1'!$B$10")

 

[Anne Troy]

How about

=INDIRECT("'["&A2&".xlsm]Page 1'!$B$10")

Still a #REF. Thank you!

 

[Fluff]

Is the other workbook open?

 

[Anne Troy]

Nevermind!

I don't know why it's putting XLSM as the file extension, when it's XLS. Thank you, that worked!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Anne Troy]

Sure did. Found she's got thousands of files to go through so getting a macro.