VLOOKUP with INDIRECT - Need some help!

C

chefdt

Board Regular
Joined
Jul 1, 2008
Messages
163
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

ABCDEF
MAMA NameDistrictDistrict 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>
 
Sort by date Sort by votes
MAMA NameDistrictDistrict NameOpportunity # of Items under
60th Percentile
#REF!

<tbody>
[TD="colspan: 7, align: center"]Account Types : TRS,TRP[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/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: #CCFFFF, align: center"]0[/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="align: center"]0[/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: #CCFFFF, align: center"]32[/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="align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/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]
[TD="bgcolor: #CCFFFF, align: center"]5[/TD]

</tbody>






tanner.dale@ok.sysco.com
BRAZORIA CO DETENTION SHERIFF 2404135 4/5 LB

<tbody>
[TD="align: right"]256.65[/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: #CACACA, align: center"]4[/TD]
[TD="bgcolor: #CCFFFF"]BRAZORIA CO DETENTION SHERIFF[/TD]
[TD="bgcolor: #CCFFFF, align: center"]2.00[/TD]
[TD="bgcolor: #CCFFFF"] 1008192[/TD]
[TD="bgcolor: #CCFFFF"] 1/24CT[/TD]

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

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

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="bgcolor: #CCFFFF"]BRIANS BARBEQUE [/TD]
[TD="bgcolor: #CCFFFF, align: center"]2.00[/TD]
[TD="bgcolor: #CCFFFF"] 1860295[/TD]
[TD="bgcolor: #CCFFFF"] 16/3#AVG
[/TD]

</tbody>
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
What... are you familiar with vlookup and how it works?

Your data does not contain a matching entry to what you have it looking up

In other words

If i'm having my vlookup search for the name Marc

marc has to be listed in the first column of the data i'm searching (somewhere in the first column)
 
Last edited:
Upvote 0
Here is a better representation of the data. I hide them via "white text" in the top row.

S2F

ABCDE
S2Ftanner.dale@ok.sysco.com
BRAZORIA CO DETENTION SHERIFF 2404135 4/5 LBXXX

<colgroup><col style="width: 30px;"><col style="width: 174px;"><col style="width: 92px;"><col style="width: 47px;"><col style="width: 59px;"><col style="width: 56px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]256.65[/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"]BRAZORIA CO DETENTION SHERIFF[/TD]
[TD="bgcolor: #CCFFFF, align: center"]2.00[/TD]
[TD="bgcolor: #CCFFFF"] 1008192[/TD]
[TD="bgcolor: #CCFFFF"]XXX[/TD]
[TD="bgcolor: #CCFFFF"]PACKER[/TD]

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

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

</tbody>

Formeln der Tabelle
ZelleFormel
C1=SUMIF(X:X,">0")


<tbody>
</tbody>

<tbody>
</tbody>





Summary Page - - - - - - --------------------------

Summary

ABCDEFG
MAMA NameDistrictXXXOpportunityXXXXXX
#REF!

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 138px;"><col style="width: 70px;"><col style="width: 146px;"><col style="width: 88px;"><col style="width: 88px;"><col style="width: 122px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="colspan: 7, align: center"]Account Types : TRS,TRP[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/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"]XXX[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]DAY OTWELL,KRISTIN[/TD]
[TD="align: center"]DHA[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]17.38[/TD]

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

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]
[TD="bgcolor: #CCFFFF, align: center"]DNA[/TD]
[TD="bgcolor: #CCFFFF, align: center"]AUDAS, BOBBY[/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]
[TD="bgcolor: #CCFFFF"]XXX[/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]XXX[/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="align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/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"]XXX[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]
[TD="bgcolor: #CCFFFF, align: center"]5[/TD]

</tbody>

Formeln der Tabelle
ZelleFormel
F10=VLOOKUP(A7,INDIRECT("'"&A7&"'!$A$1:$C$1"),3,FALSE)


<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
chefdt said:
Here is a better representation of the data. I hide them via "white text" in the top row.

S2F

ABCDE
S2Ftanner.dale@ok.sysco.com
BRAZORIA CO DETENTION SHERIFF 2404135 4/5 LBXXX

<colgroup><col style="width: 30px;"><col style="width: 174px;"><col style="width: 92px;"><col style="width: 47px;"><col style="width: 59px;"><col style="width: 56px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]256.65[/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"]BRAZORIA CO DETENTION SHERIFF[/TD]
[TD="bgcolor: #CCFFFF, align: center"]2.00[/TD]
[TD="bgcolor: #CCFFFF"] 1008192[/TD]
[TD="bgcolor: #CCFFFF"]XXX[/TD]
[TD="bgcolor: #CCFFFF"]PACKER[/TD]

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

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

</tbody>

Formeln der Tabelle
ZelleFormel
C1=SUMIF(X:X,">0")


<tbody>
</tbody>

<tbody>
</tbody>





Summary Page - - - - - - --------------------------

Summary

ABCDEFG
MAMA NameDistrictXXXOpportunityXXXXXX
#REF!

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 138px;"><col style="width: 70px;"><col style="width: 146px;"><col style="width: 88px;"><col style="width: 88px;"><col style="width: 122px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="colspan: 7, align: center"]Account Types : TRS,TRP[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/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"]XXX[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]DAY OTWELL,KRISTIN[/TD]
[TD="align: center"]DHA[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]17.38[/TD]

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

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]
[TD="bgcolor: #CCFFFF, align: center"]DNA[/TD]
[TD="bgcolor: #CCFFFF, align: center"]AUDAS, BOBBY[/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]
[TD="bgcolor: #CCFFFF"]XXX[/TD]
[TD="bgcolor: #CCFFFF, align: center"]XXX[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]XXX[/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="align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/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"]XXX[/TD]
[TD="bgcolor: #CCFFFF"] [/TD]
[TD="bgcolor: #CCFFFF, align: center"]5[/TD]

</tbody>

Formeln der Tabelle
ZelleFormel
F10=VLOOKUP(A7,INDIRECT("'"&A7&"'!$A$1:$C$1"),3,FALSE)


<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

Looking at your summary sheet

there needs to be a tab for each 3 letter word in column A starting from 7 down

it will give you a #ref when there isn't at this point
 
Last edited:
Upvote 0
Superstar31 said:
Looking at your summary sheet

there needs to be a tab for each 3 letter word in column A starting from 7 down

it will give you a #ref when there isn't at this point
Click to expand...


There are approximately 140 worksheets in the workbook.

Each sheet is named using a three character sequence, which can be found on the summary page in column A.

DT
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
VLOOKUP with INDIRECT - Syntax Help
Replies
2
Views
633
chefdt
C

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top