Excel multi-spreadsheet question

baby-mechanic

Hi folks, hope you can help.

I want to try and total figures from multiple spreadsheets (set as individual months) within the one file (the whole year), over a maximum of 3 spreadsheets, so I can analyse them.

How do I total figures from cells in different spreadsheets ??

Many thanks for looking in .....

TTFN

Stuart

rmg1

Do you mean different tabs within the same file or completely different files?
What version of Excel are you using?

Stompa

http://office.microsoft.com/en-gb/excel-help/create-an-external-reference-link-to-a-cell-range-in-another-workbook-HP010102338.aspx

baby-mechanic

Sorry, yes different tabs within the same file, not different files.
I've each month on a seperate tab, and the file covers each year.

Office 2010

frostw29

if you are savvy with excel i will explain unless you want to send it me send i back


you will need to get a unique range the use a nested sumif function to collate all the figures across all the tabs

andrewf75

same way as you do it within a tab.

Type = then click the cells you want totalled (with + in between)

baby-mechanic

frostw29 wrote: »

if you are savvy with excel i will explain unless you want to send it me send i back


you will need to get a unique range the use a nested sumif function to collate all the figures across all the tabs

Thanks for the offer, but unfortunately it's 'inhouse' work.

What formula would you use for cells A1 on Tab 1, Tab 2, and Tab 3

Cheers

Stuart

rmg1

frostw29 wrote: »

if you are savvy with excel i will explain unless you want to send it me send i back


you will need to get a unique range the use a nested sumif function to collate all the figures across all the tabs

That's making things far more complicated than they need to be.

OP - assuming all the sheets are laid out in the same format, you can simply do the following:-
press =
change to the first month tab (Sheet1 for example) and click on the cell you want
Press +
change to the second month tab (Sheet2 for example) and click on the cell you want
Do this for each cell you want to add in to teh total.

Ypu'll end up with a formula that looks something like:-
='Sheet1'$A$1+'Sheet2'$A$1+'Sheet3'$A$1+'Sheet4'$A$1.......'Sheet99'$A$1
(Don't know why there's a space in the formula, it doesn't need to be there and it isn't there as I'm typing this)

If you then remove all the $ signs, you can drag the formula down and across and it will change automatically to the new cells.

paddyrg

Sounds like you want 'consolidate'

http://www.excel-easy.com/examples/consolidate.html

frostw29

rmg1 wrote: »

That's making things far more complicated than they need to be.

OP - assuming all the sheets are laid out in the same format, you can simply do the following:-
press =
change to the first month tab (Sheet1 for example) and click on the cell you want
Press +
change to the second month tab (Sheet2 for example) and click on the cell you want
Do this for each cell you want to add in to teh total.

Ypu'll end up with a formula that looks something like:-
='Sheet1'$A$1+'Sheet2'$A$1+'Sheet3'$A$1+'Sheet4'$A$1.......'Sheet99'$A$1
(Don't know why there's a space in the formula, it doesn't need to be there and it isn't there as I'm typing this)

If you then remove all the $ signs, you can drag the formula down and across and it will change automatically to the new cells.

Where in his comment did he say they were all laid out the same this would have covered all possible bases it's just the way I do things as an analyst

Stompa

rmg1 wrote: »

Ypu'll end up with a formula that looks something like:-
='Sheet1'$A$1+'Sheet2'$A$1+'Sheet3'$A$1+'Sheet4'$A$1.......'Sheet99'$A$1

Or you could just use =SUM(Sheet1:Sheet99!A1)

or even =SUM('*'!A1) for all sheets.