Indirectly referencing another spreadsheet

[eti3nne]

I have 3 spreadsheets, YTD Jun08.xls, YTD May08.xls and ONLYJun08.xls.

I need to subtract YTD May08 from YTD Jun08 to get ONLYJun08 for each of the cells in the spreadsheet.
This I can do. Howvwer next month I will need to do the same thing with YTD July08 and YTD Jun08 to generate ONLYJuly08.

Is there a way to generalize my subtracting spreadsheet so that I dont have to rebuild it every month?

I am hoping I can use some sort of indirect reference eg in cell A1 on my ONLYJun08 spreadsheet I could enter [YTD Jun08] and have last months reference in say A2 as [YTD May08]. This way I could easily update each month.

Each spreadsheet has 9 sheets (Sheet1 to Sheet9) in it and each sheet has about 500 values (A9 to L88) to obtain the differences from.

Any ideas?

Thanks for looking.

Steve.

 

[mintjulep]

Like maybe the INDIRECT() function?

 

[SkipVought]

Hi,

Are there no dates, at all, in your DATA?

Pleas post a sample of data from a sheet.

How is each of the 9 sheets in your workbook diffrent from the others?

This is not a trivial problem. Your workbook structure has you in quite a predicament. There are better ways of storing data.

Skip,

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

 

[eti3nne]

mintjulep,

If only!

SkipVought,

Unfortunately, I do not have control of the data format, it is distributed in this particular format and as cumulative data.

Good news!!
I (OK, so it was Tracey A' in an adjacent office who suggested this!) have a solution!

Using Edit, Links, Change Source. I just alloacte a new linked source, eg to the July08 data instead of Jun08 data next month. Works a treat!

Thanks for your thoughts, both.

 

[xlbo]

did you try the INDIRECT function? for your particular problem, it will work BUT Skip is correct - you are STORING your data as you are REPORTING on it. Data should be stored in 1 file as far as possible. Reporting from it can be done in a wide variety of ways to make your reports look as they need to but you should remember that data does not need to be stored as it is reported...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question