use of the indirect function?

[CaroRaw27]

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&"!B38"), 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

 

[dhstein]

Your VLOOKUP is using A2 as the sheet name and then a range of B38 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]

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.

 

[Don Guillett]

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)

 

[dhstein]

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.