Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2001
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    variable in lookup (Excel 2003)

    Is there a way to insert a variable into a VLookup string? For instance, I have the following formula in a cell:
    =VLOOKUP(A6,'E:COOKROOMDailyInv[020105.xls]Inventory'!$A$3:$G$85,7,0)
    I would like to be able to substitute the 020105.xls with a cell reference. I would like the formula to look at cell b6, for instance, to get the document name. Is that possible?

    What I am trying to do is to reference existing daily inventory worksheets and be able to put the beginning inventory date in one column and the ending inventory date in the next column to get the difference in inventory between the 2 days. The daily worksheets are named consistently with the date as their name. Any better ideas to accomplish this task?

    Thanks in advance for your assistance! Linda

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: variable in lookup (Excel 2003)

    Hans,
    One small typo - you missed out INDIRECT! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    =VLOOKUP(A6,INDIRECT("["&B6&"]Inventory!$A$3:$G$85"),7,0)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: variable in lookup (Excel 2003)

    Thanks to Rory for pointing out stupid mistake - I omitted the essential part from the formula, the INDIRECT function. Now corrected.

    You can use the INDIRECT function:
    <code>
    =VLOOKUP(A6,INDIRECT("["&B6&"]Inventory!$A$3:$G$85"),7,0)
    </code>
    Note: INDIRECT doesn't work if the workbook referred to is closed, so there is no need to include the path. The formula will return #REF if the other workbook is closed, but it will update itself the moment the other workbook is opened. Laurent Longre's free add-in MoreFunc (recommended!) contains a function INDIRECT.EXT that does work if the workbook referred to is closed.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: variable in lookup (Excel 2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Small? <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thanks for that! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  5. #5
    Lounger
    Join Date
    Feb 2001
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: variable in lookup (Excel 2003)

    Worked like a charm. Thanks so much!

Posting Permissions

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