VLOOKUP with INDIRECT - Need some help!

[chefdt]

trying to write a formula in F7 and autofill the length of the sheet, using VLOOKUP with INDIRECT.

I have approximately 150 sheets (Summary Sheet Below) that have a value in C1 that I need copied to column F in the example below. Sheet names are in column A.

I tried a Vlookup(indirect(a7, blah blah), x, false) Always came away with a #REF error.

Any takers?

DT

	A	B	C	D	E	F
MA	MA Name	District	District Name

<tbody>
[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: center"]MCCO Opp $[/TD]
[TD="align: center"]60% Opp $[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="bgcolor: #CCFFFF, align: center"]SCA [/TD]
[TD="bgcolor: #CCFFFF, align: center"]CUSTOMER SERVICE[/TD]
[TD="bgcolor: #CCFFFF, align: center"]DCA[/TD]
[TD="bgcolor: #CCFFFF, align: center"]CUSTOMER SERVICE[/TD]
[TD="bgcolor: #CCFFFF, align: center"]0.01[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]SAA [/TD]
[TD="align: center"]DAY OTWELL,KRISTIN[/TD]
[TD="align: center"]DHA[/TD]
[TD="align: center"]GRAHAM, KEVIN[/TD]
[TD="align: center"]17.38[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="bgcolor: #CCFFFF, align: center"]S1Z [/TD]
[TD="bgcolor: #CCFFFF, align: center"]WARREN, JAMIE[/TD]
[TD="bgcolor: #CCFFFF, align: center"]DNA[/TD]
[TD="bgcolor: #CCFFFF, align: center"]AUDAS, BOBBY[/TD]
[TD="bgcolor: #CCFFFF, align: center"]560.27[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]S2F [/TD]
[TD="align: center"]KRAM, KEVIN[/TD]
[TD="align: center"]DNA[/TD]
[TD="align: center"]AUDAS, BOBBY[/TD]
[TD="align: center"]48.64[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]

</tbody>

 

[AlanY]

the indirect () part should be

INDIRECT(A7&"!C1")

 

[chefdt]

Forgot to mention something important A1 of each sheet also contains the sheet name.

I tried vlookup(a7,indirect(A7&"!$A$1:$C$1",3,false))

Got #ref

Ugh!

 

[AlanY]

how about

vlookup(a7,indirect(A1&"!$A$1:$C$1",3,false))

 

[Superstar31]

Forgot to mention something important A1 of each sheet also contains the sheet name.

I tried vlookup(a7,indirect(A7&"!$A$1:$C$1",3,false))

Got #ref

Ugh!

close out your indirect?

 

[Superstar31]

Seems pretty simple but the information you provided isn't complete

What exactly is your lookup value?

What exactly is the range you want to look up

Just make a vlookup withOUT the indirect 1st and post that

Or provide us with a larger sample of your data...

 

[Superstar31]

trying to write a formula in F7 and autofill the length of the sheet, using VLOOKUP with INDIRECT.

I have approximately 150 sheets (Summary Sheet Below) that have a value in C1 that I need copied to column F in the example below. Sheet names are in column A.

I tried a Vlookup(indirect(a7, blah blah), x, false) Always came away with a #REF error.

Any takers?

DT

	A	B	C	D	E	F
MA	MA Name	District	District Name

<tbody>
[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: center"]MCCO Opp $[/TD]
[TD="align: center"]60% Opp $[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="bgcolor: #CCFFFF, align: center"]SCA [/TD]
[TD="bgcolor: #CCFFFF, align: center"]CUSTOMER SERVICE[/TD]
[TD="bgcolor: #CCFFFF, align: center"]DCA[/TD]
[TD="bgcolor: #CCFFFF, align: center"]CUSTOMER SERVICE[/TD]
[TD="bgcolor: #CCFFFF, align: center"]0.01[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]SAA [/TD]
[TD="align: center"]DAY OTWELL,KRISTIN[/TD]
[TD="align: center"]DHA[/TD]
[TD="align: center"]GRAHAM, KEVIN[/TD]
[TD="align: center"]17.38[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="bgcolor: #CCFFFF, align: center"]S1Z [/TD]
[TD="bgcolor: #CCFFFF, align: center"]WARREN, JAMIE[/TD]
[TD="bgcolor: #CCFFFF, align: center"]DNA[/TD]
[TD="bgcolor: #CCFFFF, align: center"]AUDAS, BOBBY[/TD]
[TD="bgcolor: #CCFFFF, align: center"]560.27[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]S2F [/TD]
[TD="align: center"]KRAM, KEVIN[/TD]
[TD="align: center"]DNA[/TD]
[TD="align: center"]AUDAS, BOBBY[/TD]
[TD="align: center"]48.64[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]

</tbody>

=VLOOKUP(A7,INDIRECT("'"&A7&"'!$A$1:$C$1"),3,FALSE)

you're missing the ' and ! in there

 

[chefdt]

how about

vlookup(a7,indirect(A1&"!$A$1:$C$1",3,false))

Too many arguments, with the highlite on the "false" condition.

 

[Superstar31]

Too many arguments, with the highlite on the "false" condition.

Use this one instead

=VLOOKUP(A7,INDIRECT("'"&A7&"'!$A$1:$C$1"),3,FALSE)

 

[chefdt]

use this one instead

=vlookup(a7,indirect("'"&a7&"'!$a$1:$c$1"),3,false)

#ref!