MrExcel Publishing
Your One Stop for Excel Tips & Solutions

linking


Posted by Anthony on December 27, 2001 7:15 AM

Happy Holidays to all! I have a spreadsheet consisting of numerous worksheets in which all of their totals roll-up onto ONE worksheet. I need to link each cell from the roll-up worksheet to each of the corresponding worksheets. Is there an easier way to link than by simply linking one cell at a time?

Any advice will be greatly appreciated.

Thank You

Anthony


Posted by JAF on December 27, 2001 7:40 AM

For this to work, the sheets you want to summarise must have EXACTLY the same layout.

If your workbook consists of 6 data sheets and a summary sheet, the formula you would need is:
=SUM(Sheet1:Sheet6!A1)

This sums the contents of Cell A1 on worksheets Sheet1 to Sheet6 inclusive.

NB: The actual names of the worksheets is irrelevant. You cound use:
=SUM(Marketing:Admin!A1)
which would sum A1 on all sheets from Marketing to Admin inclusive.

If you add new sheets, they will automatically be included in your summary sheet as long as they are added between the first and last sheets named in the 3D reference.

Hope this helps.


JAF

Posted by Anthony on December 27, 2001 11:31 AM

Worked like a charm..........Thanks!!!!