Indirect Formula to refer to a sheet

[howard]

I have the following formula below that returns #name?

 =[Sales_Report.2022.xls]INDIRECT("'"&G1&"'!"&C254)

G1 refers to the sheet name and C254 in the cell number on the sheet in [Sales_Report.2022.xls

I am trying to refer to the sheet name using the indirect formula but cannot get it to work

It would be appreciated if someone could kindly amend my formula

 

[Fluff]

How about

=INDIRECT("'[Sales_Report.2022.xls]"&G1&"'!"&C254)

 

[howard]

Thanks for the help Fluff. I get #Ref!

See links below of sample files

Extract Sales using Indirect.xlsx

1drv.ms

Sales_Report 2022.xls

1drv.ms

Kindly test and amend

 

[Joe4]

Are the files you are trying to reference open in your Excel session?

 

[Fluff]

Are you trying to get the value of C254, or does C254 have a cell reference?

 

[howard]

Both Files are open

 

[howard]

Are you trying to get the value of C254, or does C254 have a cell reference?

I am trying to extract the value in cell C254

 

[Fluff]

In that case try

=INDIRECT("'[Sales_Report.2022.xls]"&G1&"'!C254")

 

[howard]

Thanks for the help. Formula works perfectly

 

[Fluff]

Glad we could help & thanks for the feedback.