Index/Match across multiple sheets and workbooks

[nymets5485]

Good Afternoon! I work at a medical practice that is made up of 4 sites. Each site has an excel workbook that they enter daily statistics into. I am trying to create another workbook to act as a "dashboard" to display the critical data from each sites workbook. I am trying to use an index and match formula so when the dates match it will display the corresponding cell value. I think i am close but am getting a #value! error.

So when the date in J1 matches the date on the site workbook i want the corresponding number in the patient visit column to display.

This is the formula that im trying and getting the error : =INDEX('[MWR - 2023 Stats.xlsx]Jan 23:Dec 23'!$E$9:$E$31,MATCH((J1),'[MWR - 2023 Stats.xlsx]Jan 23:Dec 23'!$B$9:$B$31,0))

Happy to provide follow up info. Thanks!

 

[Fluff]

You cannot use 3D references in index.
How about

=INDEX(VSTACK('[MWR - 2023 Stats.xlsx]Jan 23:Dec 23'!$E$9:$E$31),MATCH(J1,VSTACK('[MWR - 2023 Stats.xlsx]Jan 23:Dec 23'!$B$9:$B$31),0))

 

[nymets5485]

That eliminated the #value! error but getting a #name? error.

 

[Fluff]

Do you have the VSTACK function?

 

[nymets5485]

It looks like I dont but i do have 365 subscription. Is it an add on that i have to do? Appreciate the help btw!

 

[Fluff]

No it's part of the last update (if your on the semi-annual channel). Is this a company computer?

 

[nymets5485]

Yes

 

[Fluff]

In that case IT have probably not released the latest update.
Will the other workbook always be open?

 

[nymets5485]

For the most part yes. At the very least they will be opened up a few times per day when data entries are made. The "dashboard" workbook will be put in a teams group as a tab so will always be opened. I did access the excel document via the web and that formula did work! Looks like once IT releases the update i should be good to go

 

[Fluff]

Can you use it on the web in the mean time?
The other option is to use indirect which is slow, volatile & won't work if the other workbook is closed.