use of the indirect function?

C

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&"!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
 
D

dhstein

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.
 
C

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.
 
D

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)
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top