[Solved] Keep formula from changing if columns are deleted

[micksch@datasync.com]

How do I keep =COUNTA(C4:BZ4) from changing when I delete columns?

[FJCC]

There may be a more elegant way, but this works.

Code: Select all

=COUNTA(INDIRECT("C4:BZ4"))

[Zizi64]

How do I keep =COUNTA(C4:BZ4) from changing when I delete columns?

It may depends on the fact that you want delete column
- after the referenced cell range,
- before the referenced cell range,
- inside the referenced cell range.

[MrProgrammer]

Hi, and welcome to the forum.

How do I keep =COUNTA(C4:BZ4) from changing when I delete columns?

It's not possible to give a proper answer without knowing more details about the structure of your sheet and especially about which columns you are deleting. It is also not clear if keep from changing refers to value of the formula, or to the formula itself.

Without more details the most direct answer to your question would be =COUNTA(INDIRECT("C4:BZ4")), but this has the disadvantage that if you copy the formula to other rows the it does not adjust as one might want. For example, copying the formula down a row, it remains =COUNTA(INDIRECT("C4:BZ4")) and doesn't adjust to =COUNTA(INDIRECT("C5:BZ5")).

If you need that adjustment you could use =COUNTA(OFFSET($A4;0;2;1;76)), which will adjust to =COUNTA(OFFSET($A5;0;2;1;76)) if copied down a row. The formula will reference 76 columns C to BZ as long as you don't delete column A. I couldn't test this formula with your spreadsheet because you didn't attach it. Column C is the 3rd one (1+2). Column BZ is the 78th one (1+2+76-1).

If you need any additional assistance attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet. Explain what changes you will be making and which cell's formula or which cell's value is to remain unchanged.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[micksch@datasync.com]

OFFSET is what I needed. Thanks.