Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Linking worksheet functions and arrays - Doozie
Doozie of a question - workbook, formula linking Hey guys, So I have this workbook with tons of different worksheets, with different names. I also have a summary page with summaries of the answers from these sheets. Is there any easy way to make the summary formulas automattically include any new worksheets that are added? I was thinking if I could do a list of the worksheets, then I could just tell it somehow to say = sum({list} for cell b56) - this calcs the sum for all the b56 cells in that list. I tried to do this through concatenate, but it comes out as text and errors. There seems to be no way to link to another worksheet without pyshically clicking into it (this makes it a pain, since I need to change every cell for each new sheet) Now if I could get it to just do: Say the worksheets to sumarize from are red,blue, green. I want to be able to list these vertically and have excel do sum(Red56,Blue56,Green56) and when I add Yellow to the list to just make it sum(Red56,Blue56,Green56, yellow56) Anyone? -- gsimmons2005 ------------------------------------------------------------------------ gsimmons2005's Profile: http://www.excelforum.com/member.php...o&userid=26385 View this thread: http://www.excelforum.com/showthread...hreadid=396646 |
#2
|
|||
|
|||
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" wrote in message news:gsimmons2005.1txfmc_1124312723.0299@excelforu m-nospam.com... Doozie of a question - workbook, formula linking Hey guys, So I have this workbook with tons of different worksheets, with different names. I also have a summary page with summaries of the answers from these sheets. Is there any easy way to make the summary formulas automattically include any new worksheets that are added? I was thinking if I could do a list of the worksheets, then I could just tell it somehow to say = sum({list} for cell b56) - this calcs the sum for all the b56 cells in that list. I tried to do this through concatenate, but it comes out as text and errors. There seems to be no way to link to another worksheet without pyshically clicking into it (this makes it a pain, since I need to change every cell for each new sheet) Now if I could get it to just do: Say the worksheets to sumarize from are red,blue, green. I want to be able to list these vertically and have excel do sum(Red56,Blue56,Green56) and when I add Yellow to the list to just make it sum(Red56,Blue56,Green56, yellow56) Anyone? -- gsimmons2005 ------------------------------------------------------------------------ gsimmons2005's Profile: http://www.excelforum.com/member.php...o&userid=26385 View this thread: http://www.excelforum.com/showthread...hreadid=396646 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
Arrays parameters in Functions | Excel Worksheet Functions |