Excel 2003 help

trinnylilac

I am in a new job and I am expected to know more about Excel than I really do .

I have managed charts and simple formulas etc but can anyone help me with the following

1) I have a workbook with lots of worksheets for 2009. I need to create a duplicate to use for 2010 ( hoping this is easy )

+

2) In this workbook there are 10 worksheets, on each worksheet ( bar the first) there are amounts that are totalled up from lots of invoices.

On the 1st worksheet I have a list of all the totals from the other 9 worksheets. Is there anyway I can use a formula to automatically update the 1st worksheet with the total amounts from the other 9 worksheets. I'm not even sure this is possible but my boss seems to think it is :eek:

Please help if you can x

Mattjimf

1) Copy the 2009 workbook and rename it 2010, either by selecting save as and renaming 2010, or copy paste, then delete the info that you don't need (remember to close down all workbooks then only open the 2010 one, to save deleting a whole years work!)

2) In the cell you want the data to appear use the following:
=Sheet1!$A$1

Where Sheet1 is the worksheet the figure is on and A1 is the cell the figure is in. The $ is to make the cell absolute, so that it always looks for that cell.

trinnylilac

Lovely thanks so much. Going to have a practice now.

With the 2nd part bit confused ( doesn't take much) ..

For example on sheet 2 I have a formula that adds up lots of amounts as it goes along and imagine the total added up was £120
I want the figure to automatically show on sheet 1 in a certain place would the above formula make that happen ?

Mattjimf

Yeah if you put the formula in the cell it will automatically update if any figures change (checked using an example as i wanted to make sure it was right).

So if you want the £120 to show up in cell B6 on Sheet 1, and it's in cell F12 (for sake of argument), in cell B6 you would put:
=Sheet2!$F$12

Then if anything happens to change the £120 in F12 on sheet 2, B6 on sheet 1 will be updated.

trinnylilac

Had a practice and I've got it

Thanks so much, They will be dead impressed at work ..
As sad as it may seem you have made my day

Mattjimf

I spent two years playing with excel at work, my work mates were constantly surprised about what i could do.

Glad i can help someone else.

tocsin

One extra (dead simple) tip for working with multiple worksheets:

Click on the first tab of a range, then shift-click on the last tab of a range - this selects them all, and you can delete/add fixed values/format/whatever all the cells in the worksheets at once.

Click on any tab to unselect the group when finished.

Be careful that you don't overwrite stuff you want to keep - the first response (to make a copy first) was a good one!