INDEX/MATCH - looking up data in an externally closed file that name changes weekly

[judgeh59]

Howdy Gang;

I'm working on a file where I'm trying to do an INDEX/MATCH into a file that is closed and the file name changes weekly (on Mondays)...

Please Login or Register  to view this content.

this works perfectly for the 01-26-2015.

but this file is generated every Monday. Now, I already have a cell the sets the date in that cell to the monday of the week, no matter what day you are running this...

how do I change 01-26-2015 in the formula to the cell that contains the "Monday Date" IE: cell S5...

I'm probably overdoing everything I was trying and there is a real simple technique...

thanks in advance....

[FDibbins]

You would normally use INDIRECT() to include a (changing) file name into a reference, but INDIRWECT() requires the file to be open.

Take a look at using the MoreFunc add-in for this

[judgeh59]

Hi Ford;

thanks for the input...I did try INDIRECT but of course it only works on open files....I even tried a VBA solution using the .FormulaArray object but that object can only handle 255 characters...we think we have a totally different VBA solution that connects to the DB where the data is stored versus getting it from the file.

Anyway, thanks for taking a look....yes, I did look at the add-in but I would prefer not to go that direction....

on a side note: I twied the INDIRWECT() function, but it I kept getting a Baba Walters error....

[FDibbins]

sowwy, I was hunting wabbits at da time, was being vewy vewy kwiet lol

Have you looked at the Morefunc option as well?

[judgeh59]

thanks, I needed the laugh....Elmer Fudd singing Wagner was a classic....

I did, but there might be some API issue associated with it....I think we (I forgot to mention, I'm just helping somebody else) are going with an alternate technique....Not my choice actually.

I'm going to look into the MoreFunc add-in for other stuff though...

[FDibbins]

OK, good luck

[judgeh59]

thanks Ford