Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
use of the indirect function?
hi
i am trying to reference the same range in a vlookup on a variety (20+) worksheets. i can reference a particular cell using the indirect function but i want to link to an area (say B3 to D8) on each of the worksheets and then conduct a vlookup on this grid. i have a list of the worksheet names on a summary sheet and am attempting to pull data from the same area on each individual worksheet onto the summary tab. my attemp which was =vlookup(B2,indirect(A2&"!B3:D8"), 17,false) doesn't work. for some context b2 contains an item of output which comes from the a2 processing option i have a long list of processing options with items / outputs and i want to bring in the costs calculated on the worksheets for each of these processing options |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
use of the indirect function?
Your VLOOKUP is using A2 as the sheet name and then a range of B3:D8 as your
lookup table. That's only 3 columns, so 17 is out of range in your lookup table. Also as a general rule, use absolute addressing in a Vlookup range like this: =vlookup(B2,indirect(A2&"!$B$3:$D$8"), 3,false) doesn't work. "CaroRaw27" wrote: hi i am trying to reference the same range in a vlookup on a variety (20+) worksheets. i can reference a particular cell using the indirect function but i want to link to an area (say B3 to D8) on each of the worksheets and then conduct a vlookup on this grid. i have a list of the worksheet names on a summary sheet and am attempting to pull data from the same area on each individual worksheet onto the summary tab. my attemp which was =vlookup(B2,indirect(A2&"!B3:D8"), 17,false) doesn't work. for some context b2 contains an item of output which comes from the a2 processing option i have a long list of processing options with items / outputs and i want to bring in the costs calculated on the worksheets for each of these processing options |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
use of the indirect function?
sorry. yes. i switched between my fake example and real problem.
hypothetically i want to reference column 3. a2 is the name of the worksheet i wish to reference the area b3 to d8 in. "dhstein" wrote: Your VLOOKUP is using A2 as the sheet name and then a range of B3:D8 as your lookup table. That's only 3 columns, so 17 is out of range in your lookup table. Also as a general rule, use absolute addressing in a Vlookup range like this: =vlookup(B2,indirect(A2&"!$B$3:$D$8"), 3,false) doesn't work. "CaroRaw27" wrote: hi i am trying to reference the same range in a vlookup on a variety (20+) worksheets. i can reference a particular cell using the indirect function but i want to link to an area (say B3 to D8) on each of the worksheets and then conduct a vlookup on this grid. i have a list of the worksheet names on a summary sheet and am attempting to pull data from the same area on each individual worksheet onto the summary tab. my attemp which was =vlookup(B2,indirect(A2&"!B3:D8"), 17,false) doesn't work. for some context b2 contains an item of output which comes from the a2 processing option i have a long list of processing options with items / outputs and i want to bring in the costs calculated on the worksheets for each of these processing options |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
use of the indirect function?
Your example works fine for me - it uses the Sheet name in cell A2 and does a
lookup of the range on that sheet. So I must not understand your question/requirement. "CaroRaw27" wrote: sorry. yes. i switched between my fake example and real problem. hypothetically i want to reference column 3. a2 is the name of the worksheet i wish to reference the area b3 to d8 in. "dhstein" wrote: Your VLOOKUP is using A2 as the sheet name and then a range of B3:D8 as your lookup table. That's only 3 columns, so 17 is out of range in your lookup table. Also as a general rule, use absolute addressing in a Vlookup range like this: =vlookup(B2,indirect(A2&"!$B$3:$D$8"), 3,false) doesn't work. "CaroRaw27" wrote: hi i am trying to reference the same range in a vlookup on a variety (20+) worksheets. i can reference a particular cell using the indirect function but i want to link to an area (say B3 to D8) on each of the worksheets and then conduct a vlookup on this grid. i have a list of the worksheet names on a summary sheet and am attempting to pull data from the same area on each individual worksheet onto the summary tab. my attemp which was =vlookup(B2,indirect(A2&"!B3:D8"), 17,false) doesn't work. for some context b2 contains an item of output which comes from the a2 processing option i have a long list of processing options with items / outputs and i want to bring in the costs calculated on the worksheets for each of these processing options |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
Using SUM with INDIRECT function | Excel Worksheet Functions | |||
INDIRECT function | Excel Worksheet Functions | |||
Indirect function help please | Excel Worksheet Functions | |||
INDIRECT function inside AND function | Excel Worksheet Functions |