Can INDIRECT hold only the column value constant?

[Guest]

Can INDIRECT hold only the column value constant?

In a previous post I'm was trying to create a formula that will return the
value of a cell based on its column position in the spreadsheet even if I
insert another
column to the left of it.

Example
Formula in Cell C2 is: =A2+B2.
I insert a new column A.
This moves everything one column right and the formula in cell D2 is now:
=B2+C2.
What I'd like is a formula that references the row/column position in the
spreadsheet regardless of if columns are inserted or deleted so that after
inserting a new column A my formula is the value of the new information in A2
& B2. I'd like the formula that moved to D2 to still give me the value for:
=A2+B2

Thanks to Peo Sjoblom who suggested =INDIRECT("A2")+INDIRECT("B2")
This worked.

NEW QUESTION:
I would now like be able to hold the column value constant but allow the row
to adjust to the destination row so I can paste the formula down the column.

Example:
Formula in cell C2 is now =INDIRECT("A2")+INDIRECT("B2")
I insert a new column A. No problem. Formula in cell D2 is now
=INDIRECT("A2")+INDIRECT("B2").

However now I’d like to revise this formula so I can copy it into cell D3
and get the value of =INDIRECT("A3")+INDIRECT("B3").
Any ideas? Thanks!

 

[Guest]

=INDIRECT("A" & ROW(B2))+INDIRECT("B" & ROW(A2))

Can INDIRECT hold only the column value constant?

In a previous post I'm was trying to create a formula that will return the
value of a cell based on its column position in the spreadsheet even if I
insert another
column to the left of it.

Thanks to Peo Sjoblom who suggested =INDIRECT("A2")+INDIRECT("B2")
This worked.

NEW QUESTION:
I would now like be able to hold the column value constant but allow the row
to adjust to the destination row so I can paste the formula down the column.

Example:
Formula in cell C2 is now =INDIRECT("A2")+INDIRECT("B2")
I insert a new column A. No problem. Formula in cell D2 is now
=INDIRECT("A2")+INDIRECT("B2").

However now I’d like to revise this formula so I can copy it into cell D3
and get the value of =INDIRECT("A3")+INDIRECT("B3").
Any ideas? Thanks!

 

[Guest]

Thanks. That worked.

=INDIRECT("A" & ROW(B2))+INDIRECT("B" & ROW(A2))

 

[Tom Ogilvy]

=INDIRECT("A" & row())+INDIRECT("B" & row())

 

[Tom Ogilvy]

row(A2) is verbose if the formula will be entered in the second row. row()
will suffice.