formula change automatically

[weegieavlover]

Hi All,

I have a formula in a few cells that is like this:

=F201-'Overview - 0878'!$O7

Each week the spreadsheet is saved with a new name.
Last week it was called:
Mar - Wk11 Volumes Added 2011.xlsx

This week it is called:
Mar - Wk12 Volumes Added 2011.xlsx

When I save the file to another name it changes the formula to:
=F201-'[Mar - Wk12 Volumes Added 2011.xlsx]Overview - 0878'!$O7

How do i stop this happening?

Col

 

[MrKowz]

Try:

=F201-INDIRCT("'[Mar - Wk11 Volumes Added 2011.xlsx]Overview - 0878'!$O7")

 

[weegieavlover]

Try:

=F201-INDIRCT("'[Mar - Wk11 Volumes Added 2011.xlsx]Overview - 0878'!$O7")

Hey man,

I don't want it to change so it refers to Wk11 or Wk12 I want it just to stay as "Overview - 0878".

The "Overview - 0878" is another tab/sheet within the same work book I don't want it to reference the older spreadsheet.

Does that make sense or am I not explaining myself very well?

 

[MrKowz]

Ahh, I think I see what you mean now... try:

=F201-INDIRECT("'Overview - 0878'!$O7")