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

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

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

use of the indirect function?
 

You probably have spaces in your sheet name so you must use this idea with
the ' placed properly
=VLOOKUP(F10,INDIRECT("'"&E11&"'!L2:N13"),3,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CaroRaw27" wrote in message
...
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

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