Formulas: Keeping same row/column reference when columns are inser
 

I'm trying to create a formula that will return the value of a cell based on
its row and column position in the spreadsheet even if I insert another
column.

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 repardless 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

I suspect that Index might work but haven't been able to figure it out yet.
Thanks!
--
Cheers,
MIke

One way

=INDIRECT("A2")+INDIRECT("B2")

--

Regards,

Peo Sjoblom

"Mike" wrote in message
...
I'm trying to create a formula that will return the value of a cell based
on
its row and column position in the spreadsheet even if I insert another
column.

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 repardless 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

I suspect that Index might work but haven't been able to figure it out
yet.
Thanks!
--
Cheers,
MIke

Hi

=SUM(OFFSET($A1,1,,1,2)
or
=SUM(OFFSET($A2,,,1,2)
will do, unless a column left to column A is added.


Arvi Laanemets


"Mike" wrote in message
...
I'm trying to create a formula that will return the value of a cell based
on
its row and column position in the spreadsheet even if I insert another
column.

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 repardless 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

I suspect that Index might work but haven't been able to figure it out
yet.
Thanks!
--
Cheers,
MIke

One way:

=SUM(INDIRECT("A2:B2"))

In article ,
"Mike" wrote:

I'm trying to create a formula that will return the value of a cell based on
its row and column position in the spreadsheet even if I insert another
column.

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 repardless 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

I suspect that Index might work but haven't been able to figure it out yet.
Thanks!

I suspect that's exactly what the OP meant by "inserting a new column
A"...



In article ,
"Arvi Laanemets" wrote:

unless a column left to column A is added.

Thanks Peo. It works! :-)

"Peo Sjoblom" wrote:

One way

=INDIRECT("A2")+INDIRECT("B2")

--

Regards,

Peo Sjoblom

"Mike" wrote in message
...
I'm trying to create a formula that will return the value of a cell based
on
its row and column position in the spreadsheet even if I insert another
column.

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 repardless 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

I suspect that Index might work but haven't been able to figure it out
yet.
Thanks!
--
Cheers,
MIke