Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to use the INDIRECT funtion with a relative Row reference | Excel Discussion (Misc queries) | |||
Sort as "reference column" | Excel Discussion (Misc queries) | |||
Macro to change Chart Range when inserting a column | Charts and Charting in Excel | |||
return cell reference from any column | Excel Worksheet Functions | |||
reference cell value from fixed column with variable row | Excel Discussion (Misc queries) |