VLOOKUP with INDIRECT - Syntax Help

[chefdt]

I use VLOOKUP all the time, but just came across a problem that looks like I will need to perform the lookup across multiple sheets. I'm not familiar enough with the formula, and can't find any examples that closely match my needs.

I have about 140 sheets and a summary sheet. The names of the individual sheets appear in Column A of the summary sheet. I need to copy data from Cell (C1) of each data sheet, and bring it to the summary sheet in column F of the row that contains the sheet name in A.

Summary Sheet

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

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

[TD="align: center"]MCCO[/TD]
[TD="align: center"]OPP$[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/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"]3[/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: #CACACA, align: center"]4[/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"]5[/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: #CACACA, align: center"]6[/TD]
[TD="bgcolor: #CCFFFF, align: center"]S3G[/TD]
[TD="bgcolor: #CCFFFF, align: center"]FOJTIK, QUENTIN[/TD]
[TD="bgcolor: #CCFFFF, align: center"]DNA[/TD]
[TD="bgcolor: #CCFFFF, align: center"]AUDAS, BOBBY[/TD]
[TD="bgcolor: #CCFFFF, align: center"]70.99[/TD]
[TD="bgcolor: #CCFFFF"][/TD]

</tbody>

Data Sheet with C1 Value

	A	B	C	D	E
S3G
JACKS DRIVE INN	8142366	6/10#AVG	CAB

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

[TD="align: right"]3.79[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="bgcolor: #BFBFBF"]Customer Name[/TD]
[TD="bgcolor: #BFBFBF"]Equivalent Case
Volume (TW)[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Item[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Pack/Size[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Brand[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="bgcolor: #CCFFFF"]JACKS DRIVE INN[/TD]
[TD="bgcolor: #CCFFFF, align: center"]1.00[/TD]
[TD="bgcolor: #CCFFFF"] 5002104[/TD]
[TD="bgcolor: #CCFFFF"] 1/33#[/TD]
[TD="bgcolor: #CCFFFF"]WRIGHT[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: center"]1.00[/TD]

</tbody>

Beyond my comfort zone. Every formula I've tried gives me a #REF error.

Can anyone help me?
Dale

 

[Special-K99]

in Summary!F2
=INDIRECT(A2&"!C1")

and copy down
VLOOKUP not required

 

[chefdt]

in Summary!F2
=INDIRECT(A2&"!C1")

and copy down
VLOOKUP not required

Sweet. I knew this had to be easier than what I was trying.
Works perfectly!