F
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?
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?