Cell References

[driver49]

This is sort've a "reverse absolute cell references" question.

I have a formula that references, say, cell F9.

I instert a row, the info moves to row 10, and the formula updates to F10.

I don't want it to do that. I will put new info in row 9, and I want the original formula to continue to work with the info in F9

$-signs in the formula won't do the trick. Is there any way to make the cell reference constant regardless of where the current cell content goes?

Thanks,

--PS

 

[Mark W.]

=INDIRECT("F9")

or

=OFFSET(F:F,8,,1,1)

or

=INDEX(F:F,9)

Note: The last one (INDEX) is probably the best because it isn't volatile.
This message was edited by Mark W. on 2002-11-06 11:12

 

[lenze]

Instead of F9, use INDIRECT("F9") in your formula

 

[driver49]

Wow... that was FAST. And helpful. "=INDIRECT" solves my problem.

Now, can somebody tell me what "INDEX" and "OFFSET" do?

Thanks,

--PS

 

[Mark W.]

INDEX

Returns a value or the reference to a value from within a table or range. There are two forms of the INDEX() function: array and reference. The array form always returns a value or an array of values; the reference form always returns a reference.

OFFSET

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.