MS Excel (2k3) Help.

[rhysduck]

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?

 

[rhysduck]

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

 

[rhysduck]

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?

 

[rhysduck]

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