formula in excel (1 Viewer)

[maxmangion]

i have an excel spreadsheet, in which i have a lot of worksheets inside this spreadsheet. Now i am using one of these worksheets as a summary to collect data from the various other worksheets and display some results in this sheet. Now if for instance i want to add the value of a particular fixed cell in each worksheet (except the one in the summary sheet) and display this answer in this summary sheet, do i have to call each cell individually in the sum formula, or is there an alternative method to call each cell in one call ?

Thanks

 

[Sergeant]

You can sum (same) cells from a range of sheets...
=SUM(Sheet2:Sheet10!A2)

I learned that on this forum.

 

[shades]

And even more versatile, insert two worksheets called "First" (without quotes) and "Last".

Then set up the worksheets like this:

Summary, First, Sheet1, Sheet2, Sheet3,... Last

Then use the formula:

=SUM(First:Last!A2)

The advantage is that you can add and subtract worksheets, just by moving them outside of the First and Last worksheets and your formulas update automatically.
________
Vapir no2 vaporizer

 

[maxmangion]

Thank you very much for both ideas. They work a treat

Thank you.

 

[MGumbrell]

Just for the hard of thinking like me is the A2 reference in:

=Sum(First:Last!A2)

The cell reference that is taken from all the worksheets between First & Last. In easy speak does it add all the results in range A2 from these worksheets.

Regards, Matt

 

[Sergeant]

Just for the hard of thinking like me is the A2 reference in:

=Sum(First:Last!A2)

The cell reference that is taken from all the worksheets between First & Last. In easy speak does it add all the results in range A2 from these worksheets.

Regards, Matt

Yes, it sums all the A2 cells in the range of sheets.