Issue with Formula

Hi all,

Just wondering if someone can help me with the following issue:

I have one workbook which has 50+ worksheets - one of the worksheets is a recap of all 50+; therefore in one columns I'm summing up the total for say Revenue, now the formula I have is the following:

=+'Campaign 1 - blahblah'!C11+'Campaign 2 - blahblah'!C11+'Campaign 3 - blahblah'!C11+'Campaign 4 - blahblah'

Now the above formula continues to include all 50+ worksheets; however, when I attempt to input the above into a cell on the recap worksheet Excel keeps telling me that the formula is too long! Can someone please advise on what I can do to resolve the above issue.

Many thanks.

HI
try
=sum('Campaign 1 - blahblah'!C11:'Campaign 50 - blahblah'!C11)
or paste the following codes in the macro window(Alt F11)

sub total()
dim a as integer
cells(1,1) = ""
for a = 1 to sheets.count
cells(1,1) = cells(1,1) + worksheets(a).cells(11,3)
next a
end sub

Run the macro. it will give total in cell A1.
Ravi

if it is allways the same cell and the sheets are grouped together then

=SUM(Sheet1:Sheet3!A1)

will sum cell a1 in sheet1, all the sheets after sheet1 and before sheet3 and sheet3

Does that help?

Regards

Dav

Hi Rav,

Just to confirm would the following be correct:

sub total()
dim a as integer
cells(1,1) = ""
for a = 1 to sheets.count
cells(1,1) = cells(1,1) + worksheets(a).cells(11,3) + worksheets(b).cells(11,3) and so on.....
next a
end sub

Thanks

Hi
YOur modification is not correct. It is a loop where value of "a" changes from 1 to 50 (or whatever the no of sheets you have). cell A1 keeps on accumulating (or totalling) C11 with every cycle adding value from one sheet. Use the original code
Ravi

Hi,

Okay, thanks for that.

In relation to the the cell range (recap worksheet) that I want to populate it goes from cells C4:C34 Therefore would a modification of the macro be required to pre-populate those cells?

Thanks

HI
try this modified macro

sub total()
dim a,b as integer
Range("A1:A31).clearcontents
for b =  4 to 34
for a = 1 to sheets.count
cells(b-3,1) = cells(b-3,1) + worksheets(a).cells(11,3)
next a
next b
end sub

Ravi