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
Bookmarks