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
Bneed 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 columnGwill beHafter the shift and columnIwill beJafter 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
Gwill beHafter the shift and columnIwill beJafter 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