June 28, 2016
I’m having some difficulty with vlookup returning the desired results.
I have a master file stored at this location: I:\Accounting\Finance Statements Plant\FY 2016\May 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.
July 16, 2010
You need to use the INDIRECT function to resolve the address:
=VLOOKUP(B3,INDIRECT("'I:\Accounting\Finance Statements Plant\FY 2016\"&O19&"\[GL Tie In "&O19&".xlsx]Table 1'!$A$1:$E$3696"),5,FALSE)
June 27, 2016
when looking at the work book file address:
'I:\Accounting\Finance Statements Plant\FY 2016\May 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?
July 16, 2010
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.
Most Users Ever Online: 57
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
John Ryan Kivela
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas