Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Here is how my workbook looks -
It has 7 sheets named Summary, 2000, 2001, 2002, 2003 etc In each sheet, there are 12 rows with headings - Jan, Feb, Mar etc In each sheet, there are 7 columns with headings - NA, EMEA, AP etc In the Summary Sheet, I need to pull up the value for, say 2002, Mar, EMEA. However, these values (2002, Mar and EMEA) are entered by the user in cells B1,B2 and B3. (I do not want to hardcode the values). Can anyone tell me how to do this? I tried MATCH function, but it does not accept a variable value for the sheet name. Other functions have similar issues. Thanks!! |
#2
![]() |
|||
|
|||
![]()
Try:
=OFFSET(INDIRECT(B1&"!A1"),MATCH(B2,INDIRECT(B1&"! A:A"),0)-1,MATCH(B3,INDIRE CT(B1&"!1:1"),0)-1) -- Vasant "MR" wrote in message ... Here is how my workbook looks - It has 7 sheets named Summary, 2000, 2001, 2002, 2003 etc In each sheet, there are 12 rows with headings - Jan, Feb, Mar etc In each sheet, there are 7 columns with headings - NA, EMEA, AP etc In the Summary Sheet, I need to pull up the value for, say 2002, Mar, EMEA. However, these values (2002, Mar and EMEA) are entered by the user in cells B1,B2 and B3. (I do not want to hardcode the values). Can anyone tell me how to do this? I tried MATCH function, but it does not accept a variable value for the sheet name. Other functions have similar issues. Thanks!! |
#3
![]() |
|||
|
|||
![]()
Hi!
This works for me: Summary sheet: B1 = 2002 B2 = Mar B3 = EMEA =VLOOKUP(B2,INDIRECT(B1&"!A1:D13"),MATCH(B3,INDIRE CT(B1&"! A1:D1"),0),0) An INDEX/MATCH/MATCH would also work. I suspect your problem is in using (or NOT using)INDIRECT to build the proper references. If you want to see a sample file of this post back with an email address and I'll be happy to send you one. Biff -----Original Message----- Here is how my workbook looks - It has 7 sheets named Summary, 2000, 2001, 2002, 2003 etc In each sheet, there are 12 rows with headings - Jan, Feb, Mar etc In each sheet, there are 7 columns with headings - NA, EMEA, AP etc In the Summary Sheet, I need to pull up the value for, say 2002, Mar, EMEA. However, these values (2002, Mar and EMEA) are entered by the user in cells B1,B2 and B3. (I do not want to hardcode the values). Can anyone tell me how to do this? I tried MATCH function, but it does not accept a variable value for the sheet name. Other functions have similar issues. Thanks!! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |