Excel question

cubix

In an excel file I have 10 sheets. As an example if I have a figure of 6 in say C21 of sheet 10 is it possible to do up a sum so excel will put that figure in a colum of my choosing in say sheet 1/2/3 etc. Or is it only possible to do up a sum in sheet 10 that will only work on sheet 10??

Thanks

Thomas_S_Hunterson

Yep it's possible,

Easiest way is to click the sum button, and then navigate to the sheet with the cells you want to add and select them (as you would if you were working on the same sheet). It should do it automatically.

Tails142

You can use the following format to specify cells on another sheet


=Sheet1!A1+Sheet2!A1

Basically just [SheetName]![CellRef]

charlesD

Yes you can.

Say for example you have 3 sheets as follows

sheet1 a1 = 23
sheet2 a1 = 23
to get the totals of these sheets you can use this formula in sheet3(or anywhere) "=SUM(Sheet1!A1,Sheet2!A1)"

cubix

Cheers lads for the quick answers. All the answers are spot on but I left out some info.
I am using it for doing invoices and have managed to set up auto sum so it will add up figures on a page like Materials/labour/vat and give me a total. When I open new sheets I just copy and past the invoice template I saved with all the auto sum settings. In each excel file(I have one for each month)there is a sheet I have renamed "Payment" which has colums in it with
Customer/total/labour/material/vat etc. I put the figures of each invoice I do for that month into the payment sheet. IT allows me to see with a glance at the end of the month whats what.
Not sure if I am making any sense but if I setup an auto sum for sheet2 to put the figure for labour for that invoice into the labour colum in the payment section. It wont work for the next sheet/invoice as this will be number 3 and the auto sum I would have set up would be for sheet 2. Which would mean I would have to change the auto sum each time to the number of the sheet I was working on, correct/incorrect??

Thanks

Thomas_S_Hunterson

Sorry for being a bit harsh here but that's not really a great way to do it.

A better way would be to use a plain table/spreadsheet to input all the details of the transaction in columns, and then somehting like a mail-merge to pull it out and put it into a preformatted Word document for the invoices. Creating a new spreadsheet for each invoice and then trying to add up sales figures retrospectively is a very inefficient way of doing it, and it's very hard to keep track of things.

Ideally, you could use a database to input the data, and with queries, you could have it print the invoices easily enough, and it's a lot easier to analyse the information.

I can appreciate that even thinking about this sort of stuff is daunting for someone not used to it, but if you're doing regular business, you'll want to keep proper, easy to manage, records of transactions &c.

cubix

Your spot on Sean, its not the best way to do it. Hoping to make the jump to sage/quick books etc but for the mean time was hoping where possible I could setup formula's to save me having to put the same figures into different places. I am wondering each time I open up a new sheet in excel and paste the invoice template i have made is it possible to have excel open my template instead of a blank sheet??

Thanks