Register
Log In
Unanswered
Home
New Member
June 28, 2016
Offline
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.
Steve
See Answer
July 16, 2010
Offline
Hi Steve,
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)
Mynda
Answers Post
June 27, 2016
Offline
Hi Mynda,
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?
Dugald
July 16, 2010
Offline
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
Most Users Ever Online: 57
Currently Online: clark shao
13 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
