I’m having some difficulty with vlookup returning the desired results.
I have a master file stored at this location: I:AccountingFinance Statements PlantFY 2016May 2016'[GL Tie In May 16.xlsx]Table 1'!$A$1:$E$3700
Each month a new file is created and is saved to this location. However, the file name is changed to correspond with the current month.
My challenge is that I need to retrieve certain information from the file located at this location and update a second file stored at a different location. I would like the month to be dynamic.
Attached is the file I need updated and what I have so far. Please note the formula in cell D3, which I would like to make dynamic, is based on the date entered in cell O19.
Your help would be appreciated.
Steve
Hi Steve,
You need to use the INDIRECT function to resolve the address:
=VLOOKUP(B3,INDIRECT("'I:AccountingFinance Statements PlantFY 2016"&O19&"[GL Tie In "&O19&".xlsx]Table 1'!$A$1:$E$3696"),5,FALSE)
Mynda
Hi Mynda,
when looking at the work book file address:
'I:AccountingFinance Statements PlantFY 2016May 2016'[GL Tie In May 16.xlsx]Table 1'!$A$1:$E$3700
the date formats are different mmm yyyy vs mmm yy .
If I read your formula correct the "&O19&" would insert the date in the mmm yyyy in both insistence.
If that was the case would you get a error as there will not be a file there with that address.
Steve did use =TEXT(D2,"mmm yy") to change the date format required. Is this able to be incorporated into your formula?
Dugald
Good point, Dugald. I didn't notice the slight difference in date formats but I figured he'd build the text string using a combination of the text formulas he already had in the file as opposed to actually linking to cell O19.
I started to make this point in my reply and then deleted it as it looked like he had a handle on that already.
Thanks for clarifying for others though.
Mynda
Thank you Mynda . . . you are terrific!
