Vlookup with variables

[mpwouden]

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

 

[Aladin Akyurek]

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.

 

[mpwouden]

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

 

[Aladin Akyurek]

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...

=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...

=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