Reference value in A1 as part of file name

[surkdidat]

Hi

I need to be able to reference a cell reference (which is a date such as 20230213) in cell A1, as par of a filename in a formula

So, in B1, I have =COUNTIF('[Table export20230213.xlsx]'!$A:$A,1)

However, what I would like to do is to be able to substitute the date part of the above to A1. (So, I can then just drag and drop down so all spreadsheets saved down will be able to be looked up without having to manually input each reference.

 

[Fluff]

How about

=COUNTIF(INDIRECT("'[Table export"&A1&".xlsx]'!$A:$A"),1)

or

=COUNTIF(INDIRECT("'[Table export"&TEXT(A1,"yyyymmdd")&".xlsx]'!$A:$A"),1)

 

[surkdidat]

Apologies, I did not include the sheet reference, so I cannot get it to work...

=COUNTIF('[Table export20230213.xlsx]Lookup Table export'!$A:$A,1)

How about

=COUNTIF(INDIRECT("'[Table export"&A1&".xlsx]'!$A:$A"),1)

or

=COUNTIF(INDIRECT("'[Table export"&TEXT(A1,"yyyymmdd")&".xlsx]'!$A:$A"),1)

 

[Fluff]

What did you try?

 

[surkdidat]

=COUNTIF(INDIRECT("'[Table export"&A1&".xlsx]'!Lookup Table export'!$A$A"),1)

But comes up with the #REF! erro

What did you try?

 

[Fluff]

if you remove the '! from after the ] then it should be ok.

 

[surkdidat]

Thank you for this - the formula works, but only wen the spreadsheet is opened, otherwise it goes back to #REF! - Is there anyway it can work so that it "Saves" whatever data was the result when the sheet was last opened please?

(So, I would like to save down the spreadsheet with the name, the results store in my master spreadsheet, and then I close it down. A process I can repeat every day, so I can have the master sheet with a daily total in?

if you remove the '! from after the ] then it should be ok.

 

[Fluff]

You can only use indirect with an open workbook. To save the data you would have to copy & paste as values.