Absolute Address

[FishingIsMoreFun]

Hi

Not sure If I have my mind around it, so after reading a question on here the other day it made me think.

When you have a cell with a value that you want some code or a formula to access all the time consistently, there is a problem of other users or yourself removing rows cells or columns.

So your value or formula then has a new address.

Is there anyway to make a value or formula stick to that address rather than moving when rows are deleted.

Sometimes I think having a value that has a static address could be useful in code.

Not sure If there is a better way around this just interested.

Cheers

 

[sheetspread]

Does naming the cell work? I've also heard that indirect is useful when rows change because of sorting.

 

[shg]

If you're not going to delete the referenced cell, then I agree with using a named range.

If you always and forever want to reference Sheet2!C5 come hell or high water, you can use

=INDEX(Sheet2!$1:$1048576, 5, 3)

 

[mikerickson]

Put "cat" in B8.

Is your goal to always refer to that cell that contains "cat", if so naming the cell will keep track of insertions and deletions.

Is you goal to always refer to the 8th row of the second column, no matter what is inserted or deleted? If so, INDIRECT is the solution.

 

[mikerickson]

Shg,
Is there any advantage of using the non-volatile INDEX with Sheet2!$1:$1048576 as the precedent cells over using the volatile INDIRECT?

 

[FishingIsMoreFun]

Thanks for the reply's, very helpful.

I'll go back and check those out.

I am pretty rusty on Excel these days, so its nice to get some feedback before wandering up the garden path of
impossible macros.


Cheers

 

[Scott Huish]

INDIRECT won't change if you delete a row.

=INDIRECT("C5") will always refer to C5.

If you delete a row above this, what used to be in C6 will now be in C5 and the formula will still reference this value.

Before deletion:

Excel Workbook
	C	D	E
5	5		5
6	6
Sheet1

After deletion:

Excel Workbook
	C	D	E
4	5		6
5	6
Sheet1

 

[FishingIsMoreFun]

Once again thanks, very helpful.

 

[shg]

Shg,
Is there any advantage of using the non-volatile INDEX with Sheet2!$1:$1048576 as the precedent cells over using the volatile INDIRECT?

Only that INDIRECT is volatile, as you point out, Mike.

 

[mikerickson]

But if the whole sheet is the precedent, wouldn't both volatile and non-volatile functions recalculate every time any cell is changed?