Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Passing tab name into formula (2000)

    Is it possible to pass a tab name from a cell location into a formula?

    i.e.
    =IF(ISERROR(VLOOKUP($C98,'[Mincron Data.xls]xDec'!$A:$C,2,FALSE))=TRUE,"",VLOOKUP($C98,'[Mincron Data.xls]xDec'!$A:$C,2,FALSE))

    This lookup grabs information from another workbook (Mincron Data.xls) with separate sheets labeled xJan, xFeb, xMar, ...........
    I'd like the formula to select the correct sheet based on the content of a cell at the top of the column with the formula. I've tried

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Passing tab name into formula (2000)

    You need the =INDIRECT() function, as in

    =INDIRECT("'[Mincron Data.xls]xDec'!$A:$C")

    Review INDIRECT in Excel Help and search here in the Excel Forum for examples.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Passing tab name into formula (2000)

    One thing to remember:
    When using the indirect to get info from ANOTHER workbook, the other workbook MUST be open. Indirect will NOT work with closed workbooks, so it can NOT link to the workbook like typing the name in directly.
    Steve

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Passing tab name into formula (2000)

    For the original poster, the preferred method of handling this issue is to directly embed the linking formula into another cell or cells in the Workbook, such as:

    Cell X1 contains =VLOOKUP($C98,'[Mincron Data.xls]xDec'!$A:$C,2,FALSE)
    Cell X2 contains =VLOOKUP($C100,'[Other Data.xls]YEAR'!$A:$C,2,FALSE)

    And then using local functions or IF statements to return what you want:

    =IF(condition,X1,X2)

    ... all hypothetical.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Passing tab name into formula (2000)

    Thanks for the quick response. I'm not sure whether my question was quite clear. Please take a look at the sample.
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Passing tab name into formula (2000)

    Hi Zeno,

    As per the advice from John & Steve, your formula would need an INDIRECT statement to build the link to a tab via the reference in P10 to the open source worksheet. To do this, you would replace:
    'P:MeloneJ[Mincron Data.xls]xDec'!$A:$C
    in your formulae with
    INDIRECT("'P:MeloneJ[Mincron Data.xls]"&P$10&"'!$A:$C")

    You could get around the limitation that INDIRECT only works if the source workbook is open by having a set of worksheets in the one you're using that explicitly link to the corresponding ranges in "'P:MeloneJ[Mincron Data.xls", and giving each of those worksheets the tabs described on row 10. Then you could replace
    'P:MeloneJ[Mincron Data.xls]xDec'!$A:$C
    in your formulae with
    INDIRECT(P$10&"'!$A:$C")

    Cheers

    PS: The formula
    ="x"&TEXT(P11,"mmm")
    on P10 could be simplified to
    TEXT(P11,"xmmm")
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Passing tab name into formula (2000)

    Zeno, Steve and Macropod are steering you straight; I had taken a quick look at the spreadsheet you posted but then the powers that sign my paycheck required my time. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> One other approach considering Steve's excellent warning is to create a table (like this fictitious one) in the same workbook as the one you posted (same or different sheet) that links to the other workbooks like this:

    <table border=1><td></td><td align=center>G</td><td align=center>H</td><td align=center valign=bottom>1</td><td valign=bottom>xJan</td><td valign=bottom>='[MyJan.xls]Sheet1'!$A$1</td><td align=center valign=bottom>2</td><td valign=bottom>xFeb</td><td valign=bottom>='[MyFeb.xls]Sheet1'!$A$1</td><td align=center valign=bottom>3</td><td valign=bottom>xMar</td><td valign=bottom>='[MyMar.xls]Sheet1'!$A$1</td><td align=center valign=bottom>4</td><td valign=bottom>xApr</td><td valign=bottom>='[MyApr.xls]Sheet1'!$A$1</td><td align=center valign=bottom>5</td><td valign=bottom>xMay</td><td valign=bottom>='[MyMay.xls]Sheet1'!$A$1</td><td align=center valign=bottom>6</td><td valign=bottom>xJun</td><td valign=bottom>='[MyJun.xls]Sheet1'!$A$1</td><tr><td align=center valign=bottom>7</td><td valign=bottom>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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