Naming Ranges and Spreadsheets

[EliseFreedman]

Hi there

I have been asked to improve a spreadsheet which was originally created by one of the managers at work.

There is a spreadsheet for each week in the year. They are named FW 01 Data, FW 02 Data, FW 03 data etc. The first 3 weeks all have the same grids containing audit findings. Each spreadsheet has a totals grid and then the last spreadsheet for the month has what he has called a Rollup Grid in which I have added a formula to collate the totals from each individual weeks totals i.e week1+week2+week3+week4
e.g
T45 S4 FW08

N/A 0 0 0 0 0
Amber 2 0 2 2 2
Green 0 0 0 0 0

My problem is that I have done the formulas for weeks 1-4. Then having done that I realised that I was going to have to repeat the same process for the remaining 48 weeks. Is there any way of using named ranges or something so that the formulas can be duplicated each time without having to go through and manually change the formulas to refer to the next 4 weeks worksheets e.g I am going to have up update week 8 formulas to refer to weeks 5, 6, 7, and 8 instead of 1,2,3 and 4. Any help or advice would be appreciated

 

[GlennUK]

Well, the first thing I've got to say is that splitting data into different sheets sounds like making life hard for yourself. I think most of the guys on here would recommend that you keep all of your data in one place and then perform reporting and analysis on that one chunk of data.

That said, I'd point you in the direction of the INDIRECT function, so that you can use week calculations to point at various sheets.

Be warned, if you have a lot of formulae using INDIRECT can slow your spreadsheet down a lot.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Elsie,

Mega dittos to Glenn's first comment. You shoot yourself in the foot, choppiing data up like this. Put ALL your similar data in ONE TABLE. Then the reporting and analysis is a piece of cake!

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[Gavona]

Agree with what has been said already. However:
If you select the first 4 sheets and copy them you could then re-label the copied sheets as FW 05 to 08. Might be simpler than re-constructing loads of formulae.
I can't test it right now but I think that will work. If however the formulae still point back to FW 01 etc (or if they refer to named ranges) then you can do it by saving a copy of the workbook, moving the sheets from the copy to the original and then re-labelling them.

Still a nightmare if you realise that you want to change the formulae. (Edit Replace is your friend). Better to do what Glenn and Skip suggest.

Gavin

 

[Markus4]

If you prefer to use named ranges, then you might consider this:

In general a named range is "visible" in the whole workbook.

But: when naming the range, if you put the worksheet-name and a "!" before the desired name of your range, this name will only be "visible" on this particular worksheet.

In Excel 97 you can see this effect: in the range naming dialog the worksheet-name appears next to the range-name. You cannot see the range-name on other worksheets, neither in the naming-dialog nor when pressing F3, but you can easily refer to it manually.

In conjunction with Gavonas suggestion: I would make 4 worksheets with ranges named as described above, copy and rename them and replace the sheet-names with Search-And-Replace.

But in general: I agree with Skip and Glenn.

HTH.

Markus