Results 1 to 4 of 4

Vlookup with variables

This is a discussion on Vlookup with variables within the Excel Questions forums, part of the Question Forums category; Here's the issue. Each day I create a spreadsheet we use for our build plan. While creating this build plan ...

  1. #1
    New Member
    Join Date
    Jun 2010
    Location
    Tucson AZ
    Posts
    2

    Exclamation Vlookup with variables

    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,898

    Default Re: Vlookup with variables

    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.
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    Jun 2010
    Location
    Tucson AZ
    Posts
    2

    Default Re: Vlookup with variables

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,898

    Default Re: Vlookup with variables

    Quote Originally Posted by mpwouden View Post
    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...
    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...
    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
    Assuming too much and qualifying too much are two faces of the same problem.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com