absolute cell references

[Rusty]

Hi Guys,

I collect 3 columns of data every day. I have about 25 days worth of data,
and I store it in columns A,B,C E,F,G I,J,K etc. The columns go up to
CW,CX,CY or so. Each day I delete the oldest data (in Cols A,B,C). All the
data moves to the left, and I add the latest day’s data in Cols CW,CX,CY,
which are then empty.

On a second worksheet I reference this data. My problem is that as I delete
columns in the first worksheet, the references in the second worksheet
change. I don’t want the references to change. I have tried using the $
sign for this, but it doesn’t work here. I’m using Excel 2003. Any
suggestions?

Rusty.

 

[Duke Carey]

1) You can use indirect() with the cell address in quotation marks so it is
a constant, or
2) Prior to deleting columns, you can select all the formulas that reference
that worksheet and Search/Replace the leading = sign with '= (single quote &
=) to convert the formulas to text, then after the column deletion do another
search/replace to undo the '=