Vlookup with variables

mpwouden

New Member
Joined
Jun 18, 2010
Messages
2
Here's the issue.

Each day I create a spreadsheet we use for our build plan. While creating this build plan I VLookup back to the previous day's build plan spreadsheet to pull statuses. This forces me to run the Vlookup function each time I create the spreadsheet.

I have a template worksheet that I paste the data from SAP into. What I would like to do is be able to enter into a cell the date of the previous build plan (saved in the same location each time) and have the vlookup auto update to look in that excell file.

All my files are named Build Plan mmddyyyy.xlsx

In theory the Vlookup would look like:
=VLOOKUP(A1,'[Build Plan Variable.xlsx]Build Plan'!$A:$O,13,FALSE)

Where Variable = a cell which I would type in the previous date mmddyyyy which can be text.

I have seen where I can nest a variable in the Vlookup such as
=VLOOKUP(C9,Variable,13,FALSE)
Where Variable should = '[Build Plan mmddyyyy.xlsx]Build Plan'!$A:$O

I used the naming function = a cell which contains the text above of which all I have to change is the mmddyyyy portion.

But it doesn't work.

So I guess the bottom line question is this:
With files named: Build Plan mmddyyyy
Using a static Template where I paste information into.
How can I create a process where, in a separate cell, I can enter the date of the file I want to reference (mmddyyyy) and auto update and populate a vlookup to the file desired?

Thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
If you can keep all Build Plan files open...

Let B1 house the variable part...

=VLOOKUP(A1,INDIRECT("'[Build Plan "&B1&".xlsx]Build Plan'!A:O"),13,0)

Otherwise, you need something like INDIRECT.EXT of the morefunc.xll add-in.
 
Upvote 0
That works with the files open. Thanks much. The nesting of things is crazy. Since I want to auto pull but some part numbers need to be pulled from a second sheet and I don't want to see #NAs the ending formula looks like this:

=IF(ISNA(IF(A4="F110",VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]F110 Build Plan'!$B:$O"),14,FALSE),VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]Fuels Build Plan'!$C:$O"),13,FALSE))),"",IF(A4="F110",VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]F110 Build Plan'!$B:$O"),14,FALSE),VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]Fuels Build Plan'!$C:$O"),13,FALSE)))

It works like a charm but does anyone have a slightly more truncated method to try?

Mike
 
Upvote 0
That works with the files open. Thanks much. The nesting of things is crazy. Since I want to auto pull but some part numbers need to be pulled from a second sheet and I don't want to see #NAs the ending formula looks like this:

=IF(ISNA(IF(A4="F110",VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]F110 Build Plan'!$B:$O"),14,FALSE),VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]Fuels Build Plan'!$C:$O"),13,FALSE))),"",IF(A4="F110",VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]F110 Build Plan'!$B:$O"),14,FALSE),VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]Fuels Build Plan'!$C:$O"),13,FALSE)))

It works like a charm but does anyone have a slightly more truncated method to try?

Mike

If the VLOOKUP bit is expected to return a text value, try...
Rich (BB code):
=IF(A4="F110",LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",
   VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]F110 Build Plan'!$B:$O"),14,0),
   VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]Fuels Build Plan'!$C:$O"),13,0))),"")

If the VLOOKUP bit is expected to return a text value, try...
Rich (BB code):
=IF(A4="F110",LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,
   VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]F110 Build Plan'!$B:$O"),14,0),
   VLOOKUP(C4,INDIRECT("'[Fuel Controls MPS Tracking "&$O$2&".xlsx]Fuels Build Plan'!$C:$O"),13,0))),0)
And if so desired, custom format the formula cell as: [=0]"";General
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top