Trying to SUM variable number of VLOOKUPs

[Friday]

I am trying to sum the values in a variable number of sheets and put the
totals on Sheet1 as follows:

Sheet1 (Master)
HEADING
HEADING
item1
item2
item3
item4
item5
.......
itemlast

Sheet2
item1 value value value
item2 value value value
item3 value value value

Sheet3
item1 value value value
item2 value value value

Sheet4 (does not exist)


Sheet5
item6 value value value
item8 value value value

...........

Sheet76
item1 value value value
item3 value value value
item4 value value value

================================================
Here's what I want a cell on Sheet1 (master) to do:

for each item# on the master sheet
VLOOKUP to every other sheet
search for the corresponding item# in column A
if the item# exists in this worksheet, accumulate value from appropriate
column
if the item# does not exist in this worksheet, skip it & go to next sheet
next sheet (until checked all sheets)

The closest I've come is something like this, but maybe there is a better way:

Mastersheet cell B3
=SUM(VLOOKUP(A3,'*'!A3100,2,FALSE))

Who's the smartest among you?

 

[Bernard Liengme]

This UDF will do the task

Function sheetsum(mycell)
iSheetCount = ActiveWorkbook.Worksheets.Count
For iSheet = 2 To iSheetCount
With Worksheets(iSheet)
For j = 1 To 10
If .Cells(j, "A") = mycell.Value Then
sheetsum = sheetsum + .Cells(j, "B") + .Cells(j, "C") + .Cells(j, "D")
End If
Next j
End With
Next iSheet
End Function
best wishes

 

[Jacob Skaria]

Try the below formula in Sheet1..B3 and copy down as required

=SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("2:76"))
&"'!A:A"),A3,INDIRECT("'"&"sheet"&ROW(INDIRECT("2:76"))&"'!B:B")))

OR

'withsheet names in J1:J75
=SUMPRODUCT(SUMIF(INDIRECT("'"& J1:J75
&"'!A:A"),A3,INDIRECT("'"& J1:J75 &"'!B:B")))

--Make sure you have all the sheet names within your workbook
--Better to test this formula with few number of sheets.

If this post helps click Yes

 

[Friday]

Jacob,
I tried both formulas on my small test sheet, but got the dreaded #REF! .
Any other ideas would be appreciated.
Steve

 

[Friday]

Bernard,
Thanks for your idea. Unfortunately I don't even know what UDF means, so I
didn't get a good result. I just typed everything into the cell and am
guessing there is a little different way of executing your instructions.
If you can tell me how to implement this I'd appreciate it.
If not, thanks for responding.

 

[Jacob Skaria]

--Try this in a fresh workbook with 3 sheets.
--Sheet names are 'Sheet2' 'sheet3'
--In Sheet1 cell A3 you have type a text say 'jacob'
--In sheet2 and Sheet3 in Col A you type the same text in any row and a
corresponding value in Col B
--The below formula should return the total of the values in Col B in sheet2
and Sheet3 for the text 'jacob' keyed in ColA

=SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("2:3"))
&"'!A:A"),A3,INDIRECT("'"&"sheet"&ROW(INDIRECT("2:3"))&"'!B:B")))

If this post helps click Yes

 

[Friday]

Voila!!
It worked perfectly Jacob. Now on to the next step:

My original plan was to have a separate sheet for each item#, probably about
100.
Also, I'd like to name the sheets with the item# which would actually be a
stock ticker symbol.

So far I don't have enough knowledge about your formula to understand
exactly what each piece means. Do you know a reference book or website that
might explain it?
Will your formula work if all the sheets have different names?
ieMSFT,GOOG,AAPL)
Someone on this discussion group showed how to use '*'! to identify all
sheets except the one containing the cell. Is there a way to sum the target
cell on every sheet (except master sheet1) without having the formula be a
mile long?
Thanks again. You're helping me learn this stuff!
Steve

 

[Jacob Skaria]

Check out my first post itself..You have a soluition in there

'withsheet names in J1:J75
=SUMPRODUCT(SUMIF(INDIRECT("'"& J1:J75
&"'!A:A"),A3,INDIRECT("'"& J1:J75 &"'!B:B")))

Try with a small number of sheets...all the cells referred in the formula
should contain valid sheet names (J1:J75) or else you will receive the
earlier REF# error.

If this post helps click Yes