Summing VLookup from a variable number of sheets

[Allen]

On Worksheet "Record Sheet" cell C5 I need a formula which will do a VLookup
on a variable number of sheets (all of which have a name that begins with
"Round " followed by a number) to search rows 8 through 23, column B (in the
Round worksheets) for the value on the recordsheet in cell A5 and return the
value of the cell in column BG on the Round sheets. The number of round
sheets can vary between 4 and 25 or so but there are also additional sheets
that I don't want searched after the Round worksheets. Programmatically, I
would use a For Loop but I would like for it to update the RecordSheet when
each of the Round worksheets are automatically updated rather than use a
program. Thanks.

 

[L. Howard Kittle]

Hi Allen,

This may work for you if I understand your post. Got this from Peo Sjoblom
in 2006. I'm sure there will be line wrapping since it is pretty long.

It looks across eight worksheets in my example. If the value is not on the
first it goes to the next and so on.

Poe e-mailed me a two page explanation which I can almost, but not fully,
understand how it works.

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)>0),0))&"'!A2:C200"),3,0)

The look_up value is in cell A1 of the "Summary" sheet.
The lookup_table is A2:C200 of each of the eight sheets.
"MySheets" is a named range of a list of the eight worksheets.
The 3 near the end returns column C of the look_up range, and the 0 at the
end is for an exact match.
This is an array formula and you use Ctrl + Shift + Enter to commit, Excel
puts curly brackets { } around the formula.

The formula would look like this if you did not use a named range for the
worksheets. This one returns column B as you can see by the 2 near the end.
Also array entered.

=VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A1)>0),0))&"'!A2:C200"),2,0)

I'm at a bit of a loss on how to account for from 4 to 25 worksheets unless
you included all 25 in the named range.

Post back if I can do more 'splaining...

HTH
Regards,
Howard

 

[L. Howard Kittle]

Hi again Allen,

Played around with the 4 to 25 worksheet issue and it seems I may have a
solution for that if the aforementioned formula will work for you.

Regards,
Howard

 

[Allen]

Oops. I left something off of my original explanation. I want it to do a
Vlookup on ALL of the sheets and SUM the resulting cells. Like I said, I'm
not sure it's a formula problm since I have a variable number of cells. I
may need to create some cells (25 of them to account for all possible issues)
down further in the Record Sheet worksheet to bring in the values and then
sum them from there. Is there a way to SUM a group of cels, some of which
may contain errors because they are doing a VLookup that tries to access a
non-existent worksheet?

Sorry for the ommission.

 

[L. Howard Kittle]

Well, from your last reply, I have to say "I don't know".

Peo's solution of looking across many sheets works great, but it has no
provision to add multiple lookup's let alone even produce them.

Sorry

Regards,
Howard