MS Excel (2k3) Help.

rhysduck · 5 Feb 2010 at 09:32

Morning all...

I have a weekly time sheet that I've setup.
With each week as a seperate worksheet in 1 workbook.

For each week I work, I earn A/L (Annual Leave). This accumlates each week.
I have the forumlae dialled in and working to calculate how much A/L I work up at the end of each week.

I want to be able to carry this total through each week.
Each worksheet is named: "1st Feb", "8th Feb". (Mondays date for each week).

=SUM(First:Last!P12) (where P12 is the recurring cell in each worksheet that houses the total A/L for that week).

This formula open a dialogue box to select a file entitled: "Update Values: Last".

Can anyone help please?

wozencl · 5 Feb 2010 at 10:27

You need to use the names of the sheets.

SUM(Sheet1:Sheet4!P12)

in your case it would be

SUM(01.02.10:22.02.10!P12)

rhysduck · 5 Feb 2010 at 10:32

wozencl said:
You need to use the names of the sheets.

SUM(Sheet1:Sheet4!P12)

in your case it would be

SUM(01.02.10:22.02.10!P12)

But the names of the sheets will be ever changing. Pointless having a formula that you have to edit each time a new sheet is added.
I want it to calculate the sum of the cell in ALL worksheets, from the first worksheet to the last worksheet.

So even if I add a new sheet in the middle, it will include it. Or add a new sheet on the end (for a new week) it will include it.

Hence my =SUM(First:Last!P12) formula that I thought should have worked

s

wozencl · 5 Feb 2010 at 10:39

Excel will automatically update the formula when you rename sheets. If you add sheets in between the first and last they too will be included.

rhysduck · 5 Feb 2010 at 10:54

On the 1st sheet (1st Feb) it says "#NAME?"
On the 2nd sheet (8th Feb) it says nothing.

It's blank for each subsequent worksheet that I add.... Any ideas?

wozencl · 5 Feb 2010 at 11:05

I missed the '.

The formula should be SUM('1st Feb:22nd Feb'!P12)

Obviously the 1st/22nd Feb should reflect the names of the sheets.

rhysduck · 5 Feb 2010 at 11:21

wozencl said:
I missed the '.

The formula should be SUM('1st Feb:22nd Feb'!P12)

Obviously the 1st/22nd Feb should reflect the names of the sheets.

Thanks. Well it's worked for the first two sheets. But when I COPY a worksheet (Copy & MOVE to End) I get a #REF!.

I rename the worksheet to the new data - but still the same

wozencl · 5 Feb 2010 at 11:31

If you're adding sheets add them between the first and the last. Excel is referencing the first, last and those in between so if you update them or anything in between it knows to automatically update the formula. However, if you add a sheet to the end it's outside the range of sheet that the formula references and as such will not be included.

dazzerd · 5 Feb 2010 at 12:33

If you know what the names of the sheets are going to be in future you can use the indirect function, this will automatically look in the correct sheet.