Linking worksheet functions and arrays - Doozie

[gsimmons2005]

Doozie of a question - workbook, formula linking

Hey guys,

So I have this workbook with tons of different worksheets, wit
different names. I also have a summary page with summaries of th
answers from these sheets.

Is there any easy way to make the summary formulas automatticall
include any new worksheets that are added?

I was thinking if I could do a list of the worksheets, then I coul
just tell it somehow to say

= sum({list} for cell b56) - this calcs the sum for all the b56 cell
in that list.

I tried to do this through concatenate, but it comes out as text an
errors. There seems to be no way to link to another worksheet withou
pyshically clicking into it (this makes it a pain, since I need t
change every cell for each new sheet) Now if I could get it to jus
do:

Say the worksheets to sumarize from are red,blue, green. I want to b
able to list these vertically and have excel do

sum(Red56,Blue56,Green56) and when I add Yellow to the list to jus
make it sum(Red56,Blue56,Green56, yellow56)

Anyone

 

[Henry]

gsimmons2005,

You can use a macro to do this.

Sub mysub()

Dim ws As Worksheet

'Set summary values to nothing before you start

Sheets("Summary").Range("B56").Value = ""
Sheets("Summary").Range("C56").Value = ""

'Loop through each worksheet in the workbook
For Each ws In ActiveWorkbook
'You don't want to add the summary figure to itself so ignore the sheet
named "Summary"
If ws.Name <> "Summary" Then
'Add this sheet's value to the summary sheet
Sheets("Summary").Range("B56").Value = _
Sheets("Summary").Range("B56").Value + _
ws.Range("B56").Value

Sheets("Summary").Range("C56").Value = _
Sheets("Summary").Range("C56").Value + _
ws.Range("C56").Value

End If

'Next sheet
Next ws

'Done
End Sub


"gsimmons2005" <[email protected]>
wrote in message
news:[email protected]...