[Solved] Keep formula from changing if columns are deleted
-
- Posts: 2
- Joined: Mon Mar 18, 2024 4:53 am
[Solved] Keep formula from changing if columns are deleted
How do I keep =COUNTA(C4:BZ4) from changing when I delete columns?
Last edited by robleyd on Tue Mar 19, 2024 12:53 am, edited 2 times in total.
Reason: Add green tick
Reason: Add green tick
Re: Keep formula from changing
There may be a more elegant way, but this works.
Code: Select all
=COUNTA(INDIRECT("C4:BZ4"))
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Keep formula from changing
It may depends on the fact that you want delete columnHow do I keep =COUNTA(C4:BZ4) from changing when I delete columns?
- after the referenced cell range,
- before the referenced cell range,
- inside the referenced cell range.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
- MrProgrammer
- Moderator
- Posts: 4932
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Keep formula from changing
Hi, and welcome to the forum.
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
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.micksch@datasync.com wrote: ↑Mon Mar 18, 2024 5:15 am How do I keep =COUNTA(C4:BZ4) from changing when I delete columns?
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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 2
- Joined: Mon Mar 18, 2024 4:53 am
Re: Keep formula from changing
OFFSET is what I needed. Thanks.