Cell References (Microsoft Excel 2003)

Topic

New Reply

I had a file w/auto filters and used the filters to shortlist the data to the specified information I wanted. Once I had completed deleting rows of data I didn’t want, I noticed that the cell refences in one column changed. Why did this happen? I thought all linked cell references were static and the corresponding row wouldn’t move.

Reply | Quote

Replies

Welcome to Woody’s Lounge!

What was the formula?

Reply | Quote

Hello, Hans.

The formulas in the affected Column had the the worksheet name, cell column and row..i.e…..”Input!Bx”.
I’ve just noticed that all other columns w/cell references simply have the column & row…..”Ax”.

Reply | Quote

I am not sure what you are indicating the problem is, but i will make a guess.

The references are linked to the ranges and will change as the cells in the range change.

If you have a reference (eg) in A1 which references Cell B5:
=B5

and you delete row 4 the reference in A1 will become:
=B4 since the former row 5 is now row 4.

If you want the reference to always be B5 even when rows get deleted, then you need to use indirect references:
=Indirect(“B5”)

Now even if rows are deleted or inserted it will always reference cell B5.

If I have misunderstood your problem could you elaborate on what it is?
Steve

Reply | Quote