3

I have a spreadsheet of containers that are constantly moved around to various locations on my site which we track on this spreadsheet.

I have formulas to count the number of containers in each column (Cells 7-43), with exceptions in the formula to exclude counting the location headers and blanks.

At some point, as things are moved around on the spreadsheet, the cell range will change on its own. (For example in the photo, it changed the range from L7:L43 to L7:L26.) I've tried locking the range using $, but that has not worked. I've seen tips on using INDIRECT to lock a range, but I'm not sure how to use it with the exceptions.

Every time I try to add INDIRECT I get a too few or too many arguments error. How can I fix this?

Spreadsheet with altered formula shown:

Spreadsheet with altered formula

Improve this question
New contributor
Jessica Hale is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • 1
    I won't leave an answer because I don't know why it's changing the cell references but I can help with the INDIRECT() part: =LET(rng,INDIRECT("L7:L43",TRUE),COUNTIFS(rng,"<>*TRACK*",rng,"<>*LEAD*",rng,"<>*COAL*",rng,"<>*CHLORINE*")-COUNTBLANK(rng)) Commented 15 hours ago
  • 2
    For moving the cells, are you using mouse or clipboard? Commented 14 hours ago

1 Answer 1

Reset to default
4

You can use INDIRECT(). Below I use SUM() but you can use COUNTIF(), etc. and it should work as well:

=SUM(INDIRECT("A1:A2"))

(Note in the formula, surround the range with quotes. It won't work if you do SUM(INDIRECT(A1:A2)).)

Then, if the cells in A1:A2 are moved, the formula will not change.

example1

example2

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.