M
Mike
Absolute and relative referencing is really used to
determine how formulas change when copying them between
cells. When you insert or delete rows and columns, it
doesn't matter what type of reference you use, the address
will change.
One way to prevent the change is to use the INDIRECT
function.
=INDIRECT("Sheet2!$C$5")
The INDIRECT function converts a text string into a valid
range, so can be used for all kinds of special
situations. It's most common use is to construct a valid
range using string concatenations based on values in other
cells. For example, you could have "Sheet2" entered in
cell A1, and "$C$5" in cell A2 and use:
=INDIRECT(A1&"!"&A2)
I use something like this for dated sheets (ie sheets
named 01, 02, 03 etc.) Simply changing the date in a
summary sheet cell (A1) will pull data from the correct
sheet. Enter this into any cell and it will pull data
from the same cell in the dated sheet.
=INDIRECT(RIGHT("0"&DAY($A$1),2)&"!"&ADDRESS(ROW(),COLUMN
())
OK, more than you were asking, but the INIRECT function is
quite useful in many other ways. Because it returns a
valid range, you can use it as a parameter in other
functions too. Not the best example, but this will allow
you to sum A1 to A10.
=SUM(INDIRECT("A1:A10"))
If you change the string to use a concatenation formula,
the possibilities become obvious.
determine how formulas change when copying them between
cells. When you insert or delete rows and columns, it
doesn't matter what type of reference you use, the address
will change.
One way to prevent the change is to use the INDIRECT
function.
=INDIRECT("Sheet2!$C$5")
The INDIRECT function converts a text string into a valid
range, so can be used for all kinds of special
situations. It's most common use is to construct a valid
range using string concatenations based on values in other
cells. For example, you could have "Sheet2" entered in
cell A1, and "$C$5" in cell A2 and use:
=INDIRECT(A1&"!"&A2)
I use something like this for dated sheets (ie sheets
named 01, 02, 03 etc.) Simply changing the date in a
summary sheet cell (A1) will pull data from the correct
sheet. Enter this into any cell and it will pull data
from the same cell in the dated sheet.
=INDIRECT(RIGHT("0"&DAY($A$1),2)&"!"&ADDRESS(ROW(),COLUMN
())
OK, more than you were asking, but the INIRECT function is
quite useful in many other ways. Because it returns a
valid range, you can use it as a parameter in other
functions too. Not the best example, but this will allow
you to sum A1 to A10.
=SUM(INDIRECT("A1:A10"))
If you change the string to use a concatenation formula,
the possibilities become obvious.