Dynamically updating file path based on date cell within a VLOOKUP

Hi there,

I have a file which, using a VLOOKUP references a closed workbook(s) depending on the date in Col A.
For example, if the date in Col A is 11/1/2022, it will use the below formula to find the file located in that location and return the value which is working fine. The formula is in Col D:

Formula:

Please Login or Register  to view this content.

However, at the moment, each day I am manually changing 'Nov 01 2022' to the current day in Nov. Next month I will have to change 2022-11 to 2022-12 and also change 'Nov 30 2022' to ;Dec 01 2022' within the formula.

I want to have the formula written in a way that it automatically updates based on the current day or current parameters listed within the inputs in Col M&N or based on the date in Col A or Col H (same date just different layouts to try and match the file name structure).

My attempt at this which is returning an NA is in Col I:

Formula:

Please Login or Register  to view this content.

Any help would be hugely appreciated.

Thank you

To do that you need INDIRECT... but it does NOT work on closed sheets.

Options:

1. Consolidate all sheets into ONE file. Then there are no problems.

2. Set up a HORRENDOUS looking formula using the CHOOSE function to select the correct (closed) file.

3. Use VBA. If that's your chosen option, I will move the thread to the VBA sub-forum.

Exactly. It is NOT a lookup.

You still have 3 options... and ONLY 3 options.

or 4. Power query might do it too....