Maintain Relative Reference After Inserting a Column
 

A B
1 +A1
2

If column B1 has the formula +A1 in it, and I insert a colum between columnn
A and B, C1 then has the formula +A1 in it as a result of the column insert.

A B C
1 +A1
2

How do I construct the original formula in B1 (+A1) so that when a column is
inserted between columns A and B, which moves the formula over to column C as
a result of the column insert, the formula in C1 references the cell next to
it as +B1 rather than still showing +A1 as the formula? I want the formula to
always reference the cell immediately next to itself without being affected
by column inserts.

I know there is probably a simple answer but I can't figure it out. Any help
is appreciated.

Thanks,
-Mark

Maintain Relative Reference After Inserting a Column
 

There are probably other ways of doing this, but I just experimented with
this tip I picked up from one of John Walkenbach's books, and it appeared to
do the trick. I created a Named formula, called it CellToLeft and had it
refer to the cell to the left using relative referencing. I was on Sheet 8
in cell D41, and created the following Named Formula (otherwise known as
Named Range.) When I tested it, and then inserted a column, it did what you
requested. Once I entered something into the newly inserted cell, the cell
with the formula =celltoleft correctly picked up that entry.
=Sheet8!C41

"Mark T." wrote:

A B
1 +A1
2

If column B1 has the formula +A1 in it, and I insert a colum between columnn
A and B, C1 then has the formula +A1 in it as a result of the column insert.

A B C
1 +A1
2

How do I construct the original formula in B1 (+A1) so that when a column is
inserted between columns A and B, which moves the formula over to column C as
a result of the column insert, the formula in C1 references the cell next to
it as +B1 rather than still showing +A1 as the formula? I want the formula to
always reference the cell immediately next to itself without being affected
by column inserts.

I know there is probably a simple answer but I can't figure it out. Any help
is appreciated.

Thanks,
-Mark

Maintain Relative Reference After Inserting a Column
 

Try this:
B1: =OFFSET(B1,0,-1)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mark T." wrote:

A B
1 +A1
2

If column B1 has the formula +A1 in it, and I insert a colum between columnn
A and B, C1 then has the formula +A1 in it as a result of the column insert.

A B C
1 +A1
2

How do I construct the original formula in B1 (+A1) so that when a column is
inserted between columns A and B, which moves the formula over to column C as
a result of the column insert, the formula in C1 references the cell next to
it as +B1 rather than still showing +A1 as the formula? I want the formula to
always reference the cell immediately next to itself without being affected
by column inserts.

I know there is probably a simple answer but I can't figure it out. Any help
is appreciated.

Thanks,
-Mark

Maintain Relative Reference After Inserting a Column
 

Worked perfectly. Thanks! -Mark

"Ron Coderre" wrote:

Try this:
B1: =OFFSET(B1,0,-1)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mark T." wrote:

A B
1 +A1
2

If column B1 has the formula +A1 in it, and I insert a colum between columnn
A and B, C1 then has the formula +A1 in it as a result of the column insert.

A B C
1 +A1
2

How do I construct the original formula in B1 (+A1) so that when a column is
inserted between columns A and B, which moves the formula over to column C as
a result of the column insert, the formula in C1 references the cell next to
it as +B1 rather than still showing +A1 as the formula? I want the formula to
always reference the cell immediately next to itself without being affected
by column inserts.

I know there is probably a simple answer but I can't figure it out. Any help
is appreciated.

Thanks,
-Mark