Formula changes when rows are deleted, how to prevent.

[OfficeUser]

I am using a simple formula such as this: =COUNTA(Sheet1!D2:D20)

The problem is that anytime a row is deleted from 1 to 20, the formula adjusts itself. How do I prevent this from happening? I have been tinkering with the INDIRECT function but have had no luck making it work. Thanks!

 

[mumps]

Try:
=COUNTA(Sheet1!$D$2:$D$20)

 

[OfficeUser]

Try:
=COUNTA(Sheet1!$D$2:$D$20)

It changes as rows are deleted. I had previously tried it but gave it another go, no luck. Thanks for trying.

 

[mumps]

The only thing that I can think of is instead of deleting the whole row, just clear the range in that row that has data.

 

[OfficeUser]

The only thing that I can think of is instead of deleting the whole row, just clear the range in that row that has data.

So this is all triggered as a user selects a label on a userfom, the userform set of data highlites green, the row is copied to another sheet, and the count goes up. If they click on that particular label again to deselect, the data goes back to black .backcolor and the row is removed from the sheet, and the count goes down, this is where the formula changes itself. I could write a simple bit of code to write the formula each time but I am trying to avoid it as I would think there was a less involved way. Thanks for the suggestion, I do appreciate it.

 

[mumps]

Sorry I couldn't be of more help. Re-writing the formula may be the way to go.

 

[OfficeUser]

Sorry I couldn't be of more help. Re-writing the formula may be the way to go.

You are probably correct, whats a few more lines of code when I think it is around 83k lines currently. This project has saved so much time and is about to save even more daily. We have also eliminated 100% of the paperwork in one functional area utilizing this spreadsheet.

 

[mumps]

Isn't technology great?!!!!