Defining Cell Row Value Remotely in Formula not Code
 

Would like to figure out how to do a formula where the row number
portion of the cell location being referenced is defined elsewhere.
i.e. =CONCATENATE(AA127," ",AB127," ",AC127)
or =AA127
where the '127' value is obtained from the value of another cell
or from the location of another named cell

The defining can be done via macro, but I wish the formula's to be in
place, and not being written by the macro.

If was using macro to write would look something like this:
Range("AA" & cellrow)
where ("AA" & cellrow) returns with value of 'AA127' due to 'cellrow'
being assigned a value of 127

In other words I wish to be able to put a row # value in a designated
cell, and have all the formulas use that row number in their
calculations, or alternately have a macro designated a defined name to
a desired cell of the row # desired and get the row value that way, or
maybe I need a whole new perspective on the solution.

I have roughly 200 cells which would be updating via this method, and
writing to them all directly from code is slow, which is why I want to
limit the writing from code.

Using Excel 2002 on WinXP

TIA,
Danny

Defining Cell Row Value Remotely in Formula not Code
 

=INDIRECT("AA"&A1)

If the value of A1 is 127

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Danny" wrote in message
om...
Would like to figure out how to do a formula where the row number
portion of the cell location being referenced is defined elsewhere.
i.e. =CONCATENATE(AA127," ",AB127," ",AC127)
or =AA127
where the '127' value is obtained from the value of another cell
or from the location of another named cell

The defining can be done via macro, but I wish the formula's to be in
place, and not being written by the macro.

If was using macro to write would look something like this:
Range("AA" & cellrow)
where ("AA" & cellrow) returns with value of 'AA127' due to 'cellrow'
being assigned a value of 127

In other words I wish to be able to put a row # value in a designated
cell, and have all the formulas use that row number in their
calculations, or alternately have a macro designated a defined name to
a desired cell of the row # desired and get the row value that way, or
maybe I need a whole new perspective on the solution.

I have roughly 200 cells which would be updating via this method, and
writing to them all directly from code is slow, which is why I want to
limit the writing from code.

Using Excel 2002 on WinXP

TIA,
Danny