Results 1 to 10 of 10

Variable in referenced worksheet call

Threaded View

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    87

    Variable in referenced worksheet call

    I have the following formula, which works, in a worksheet 'Report 2011':

    =VLOOKUP($B19,'\\192.168.4.2\admin share\Ron\[RUG RATES.xlsx]CJNH 2011'!$B$3:$AA$119,MATCH($C$3,'\\192.168.4.2\admin share\Ron\[RUG RATES.xlsx]CJNH 2011'!$B$1:$O$1,0),FALSE)
    In Rug Rates.xlsx, there are two worksheets: CJNH 2011 and LFCL 2011. They are set up exactly the same but contain different data.

    What I'd like to do is allow the user to select either "CJNH" or "LFCL" in cell B2 in the worksheet 'Report 2011' and then have the formula return the value from the corresponding worksheet. I've tried to modify the above-referenced formula in this manner:

    VLOOKUP($B19,'\\192.168.4.2\admin share\Ron\[RUG RATES.xlsx]$B$2&" 2011"'!$B$3:$AA$119,MATCH($C$3,'\\192.168.4.2\admin share\Ron\[RUG RATES.xlsx]$B$2&" 2011"!$B$1:$O$1,0),FALSE)
    That is, I've tried changing the 'CJNH 2011' in the formula to B2&" 2011", which would return either "CJNH 2011" or "LFCL 2011". This has not worked. I can understand why it doesn't work, but I don't know how to fix it.

    As always, thank you in advance.

    Adam
    Last edited by hektisk; 04-06-2011 at 07:01 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1