Flexible Cell Reference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

To explain this I will use the R1C1 notation, but I would need to implement
it in A1 reference style.

Is there a way to make a "flexible" cell reference such that I could have a
value in A1 such as 3 and then have a cell reference somewhere else in the
sheet that was something like =R[-2]C[x] where the "x" is referenced to cell
A1? So, combining two different reference styles (please forgive me), the
formula would look something like =R[-2]C[$A$1].

The reason I would like to do this, if it helps explain my question, is that
I would like to predict the development costs for some land that will be
developed. So if I am going to sell 5 lots in August then the development
costs for that will occur 3,4, or 5 months earlier (which I will have in A1)
and I would like to be able to view the effects of longer or shorter
development times by simply changing cell A1. So if development costs are
$30,000 per lot, then the formula for development costs for a particular
month (looking at projected lot sales x number of months into the future)
would be =R[-2]C[$A$1]*30000 where A1 contains the number for x. Again,
sorry for the mixing of reference systems, but it seems to be the best way to
explain.

Thanks
 
I got to be honest, you lost me a little..

BUT look up 'data tables' it's a feature in excel pretty much designed for
that purpose.. you can have data tables with 1,2 & 3 variable parameters for
what if analysis..
 
Thanks bj, that is what I want to do. I guess I should have called it a
indirect cell reference. The only thing thing that still troubles me is
this: I can easily vary the row using the indirect worksheet function, such
as =INDIRECT("D"&(ROW()+$A$1)) but how do I vary the column? How can I get
the reference of the COLUMN() function into a letter? What I need to do is
=INDIRECT(&(COLUMN()+$A$1)&(ROW()-1)). Any Ideas?

bj said:
look at the indirect function in Help.
It describes fairly well what you want to do.

Brandt said:
To explain this I will use the R1C1 notation, but I would need to implement
it in A1 reference style.

Is there a way to make a "flexible" cell reference such that I could have a
value in A1 such as 3 and then have a cell reference somewhere else in the
sheet that was something like =R[-2]C[x] where the "x" is referenced to cell
A1? So, combining two different reference styles (please forgive me), the
formula would look something like =R[-2]C[$A$1].

The reason I would like to do this, if it helps explain my question, is that
I would like to predict the development costs for some land that will be
developed. So if I am going to sell 5 lots in August then the development
costs for that will occur 3,4, or 5 months earlier (which I will have in A1)
and I would like to be able to view the effects of longer or shorter
development times by simply changing cell A1. So if development costs are
$30,000 per lot, then the formula for development costs for a particular
month (looking at projected lot sales x number of months into the future)
would be =R[-2]C[$A$1]*30000 where A1 contains the number for x. Again,
sorry for the mixing of reference systems, but it seems to be the best way to
explain.

Thanks
Click to expand...
Click to expand...
 
With the indirect statement
= indirect(A1&A2)
if you entered "D" in cell A1 and 3 in Cell A2
the indirect statement would give the value in cell D3

It is starting to sound as though the offset function might work better for
you than the indirect. Please check the help section and see if this is close.





Brandt said:
Thanks bj, that is what I want to do. I guess I should have called it a
indirect cell reference. The only thing thing that still troubles me is
this: I can easily vary the row using the indirect worksheet function, such
as =INDIRECT("D"&(ROW()+$A$1)) but how do I vary the column? How can I get
the reference of the COLUMN() function into a letter? What I need to do is
=INDIRECT(&(COLUMN()+$A$1)&(ROW()-1)). Any Ideas?

bj said:
look at the indirect function in Help.
It describes fairly well what you want to do.

Brandt said:
To explain this I will use the R1C1 notation, but I would need to implement
it in A1 reference style.

Is there a way to make a "flexible" cell reference such that I could have a
value in A1 such as 3 and then have a cell reference somewhere else in the
sheet that was something like =R[-2]C[x] where the "x" is referenced to cell
A1? So, combining two different reference styles (please forgive me), the
formula would look something like =R[-2]C[$A$1].

The reason I would like to do this, if it helps explain my question, is that
I would like to predict the development costs for some land that will be
developed. So if I am going to sell 5 lots in August then the development
costs for that will occur 3,4, or 5 months earlier (which I will have in A1)
and I would like to be able to view the effects of longer or shorter
development times by simply changing cell A1. So if development costs are
$30,000 per lot, then the formula for development costs for a particular
month (looking at projected lot sales x number of months into the future)
would be =R[-2]C[$A$1]*30000 where A1 contains the number for x. Again,
sorry for the mixing of reference systems, but it seems to be the best way to
explain.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
bj,

that was EXACTLY what I was looking for. Thank You!


bj said:
With the indirect statement
= indirect(A1&A2)
if you entered "D" in cell A1 and 3 in Cell A2
the indirect statement would give the value in cell D3

It is starting to sound as though the offset function might work better for
you than the indirect. Please check the help section and see if this is close.





Brandt said:
Thanks bj, that is what I want to do. I guess I should have called it a
indirect cell reference. The only thing thing that still troubles me is
this: I can easily vary the row using the indirect worksheet function, such
as =INDIRECT("D"&(ROW()+$A$1)) but how do I vary the column? How can I get
the reference of the COLUMN() function into a letter? What I need to do is
=INDIRECT(&(COLUMN()+$A$1)&(ROW()-1)). Any Ideas?

bj said:
look at the indirect function in Help.
It describes fairly well what you want to do.

:

To explain this I will use the R1C1 notation, but I would need to implement
it in A1 reference style.

Is there a way to make a "flexible" cell reference such that I could have a
value in A1 such as 3 and then have a cell reference somewhere else in the
sheet that was something like =R[-2]C[x] where the "x" is referenced to cell
A1? So, combining two different reference styles (please forgive me), the
formula would look something like =R[-2]C[$A$1].

The reason I would like to do this, if it helps explain my question, is that
I would like to predict the development costs for some land that will be
developed. So if I am going to sell 5 lots in August then the development
costs for that will occur 3,4, or 5 months earlier (which I will have in A1)
and I would like to be able to view the effects of longer or shorter
development times by simply changing cell A1. So if development costs are
$30,000 per lot, then the formula for development costs for a particular
month (looking at projected lot sales x number of months into the future)
would be =R[-2]C[$A$1]*30000 where A1 contains the number for x. Again,
sorry for the mixing of reference systems, but it seems to be the best way to
explain.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top