Index Match question

[JG2021]

I am trying to index match to another excel doc in order to get supply totals. The other doc will always be named after today's date. I was wondering if there was a way to break into the title of the doc in the index match to have it constantly updating. Here's what I would like to do

=INDEX(CONCAT("'[",MONTH(TODAY()),".",DAY(TODAY()),".",RIGHT(YEAR(TODAY()),2),".xlsm]",MONTH(TODAY()),".",DAY(TODAY()),"'!$I:$I"),MATCH($A:$A,CONCAT("'[",MONTH(TODAY()),".",DAY(TODAY()),".",RIGHT(YEAR(TODAY()),2),".xlsm]",MONTH(TODAY()),".",DAY(TODAY()),"'!$A:$A"),0))

Is there a way to do this or not?

Thank you.

 

[jasonb75]

You need to use the INDIRECT function for that, e.g.

INDIRECT(CONCAT("'[",MONTH(TODAY()),".",DAY(TODAY()),".",RIGHT(YEAR(TODAY()),2),".xlsm]",MONTH(TODAY()),".",DAY(TODAY()),"'!$I:$I"))

Note that it will only work when the other document is open. There is no way around that.

 

[6StringJazzer]

There is a way to do it using the INDIRECT function. I believe you want this:

=INDEX(INDIRECT(CONCAT("'[",MONTH(TODAY()),".",DAY(TODAY()),".",RIGHT(YEAR(TODAY()),2),".xlsm]",MONTH(TODAY()),".",DAY(TODAY()),"'!$I:$I")),MATCH($A:$A,INDIRECT(CONCAT("'[",MONTH(TODAY()),".",DAY(TODAY()),".",RIGHT(YEAR(TODAY()),2),".xlsm]",MONTH(TODAY()),".",DAY(TODAY()),"'!$A:$A")),0))

But without your files to test with I can't guarantee it.

Edit: Jason is correct, the INDIRECT function requires an external file to be open. When it is closed you will get a #REF error. The only alternative that allows reference to a closed file is to use a macro to build the formula.

 

[JG2021]

Alright great thank you both I will try that!