This is getting close to what I’m trying to do. I have created two macros/functions which increment columns G and H depending on a comparison between columns B and A. Columns D and E increment by adding the results of column G+I and H+J.

Columns I and J are historical data which I need to add, otherwise the count/increment would start from 1 .

**The problem is**, I insert a column daily, in this case it will be between between B and C the formula in columns D and E increment the column names, (D2 becomes E2) which is great, but the two columns G and H don’t increment and I can’t understand why. All I can think is that it’s because I’m calling a function within the formula.

Any help appreciated. Thanks

G and H don’t increment

Why should references to columns `A`

and `B`

being updated, if you insert a column **after** column B? References are being updated, if the cells being referenced change by the insert or delete action and this is not the case, if you insert **after** column `B`

. Hence all formulas referencing `A2`

or `B2`

keep unchanged. This got nothing to do with your functions (whatever they do; functions `downtlw()`

and `uptlw()`

are not part of the sample file)

Hi thanks for taking the time to reply. OK, I’m missing something. But when I insert a collumn after B all other collumns move up. Collumn D knows it’s moved and changes the cell formula from =sum(G$2+I$2) to =sum(H$2+J$2). why does this collumm change and not collumns G or H.

Thanks

Collumn D knows it’s moved and changes the cell formula from =sum(G$2+I$2) to =sum(H$2+J$2).

Column `D`

doesn’t know anything: the process is as follows:

- You tell Calc to insert a column after column
`B`

- Calc now knows that all cells right of
`B`

need to be shifted to the right, to free column C (don’t follow the common picture of “inserting” a column, but freeing the column and moving its content to the adjacent column). - This means for formulas in column
`D`

: All references of formulas need to to be shifted as well. So it is the shift process (or if you like: the “*insert column function*” of Calc) which updates cell content to reflect that old column`G`

will be`H`

after the shift and column`I`

will be`J`

after the shift operation.

All this affects only cells **right** of the inserted column. columns `A`

and `B`

don’t experience any shift, so there is no reason to adapt any formula referencing to these cells.

Ahh !!! now I get it, of course, A + B didn’t move. I need to look for another way of incrementing the the collumns. Still I’m pretty pleased with myself for getting as far as I have. Thanks for all your patience.

Dave

As a matter of interest, How do I declare this question as answered.

How do I declare this question as answered.

*If you consider your question answered click the check mark () next to the answer. That’s how question are declared answered on this site (see my answer summarizing the discussion in comments). Thanks in advance …*

For the records:

- You tell Calc to insert a column
**after**column B - Calc now knows that all cells
**right**of B need to be shifted to the right, to free column C (don’t follow the common picture of “inserting” a column, but freeing the column and moving its content to the adjacent column). - This means for formulas in column D: All references of formulas need to to be shifted as well. So it is the shift process (or if you like: the “
*insert column function*” of Calc) which updates cell content to reflect that old column`G`

will be`H`

after the shift and column`I`

will be`J`

after the shift operation.

All this affects only cells right of the inserted column. Columns `A`

and `B`

don’t experience any shift, so there is no reason to adapt any formula referencing to these cells.

**Ref.** LibreOffice Help - Addresses and References, Absolute and Relative