Here’s the situation: groups of lines of data (in this instance, appearing on alternate rows; however, sometimes the rows are odd; sometimes they’re even; there’s currently an empty row between data sets, but that may disappear).
I need a way of resetting the “$” line-number for a count-function that in Cell AO5 looks like this:
=IF(COUNT(AP$5:AP7)<10,“P0”&COUNT(AP$5:AP7),“P”&COUNT(AP$5:AP7))
This keeps all my data strings in Column AO the same length. The important part is simply “COUNT(AP$5:AP7)” and the “P” prefix is a code for the type of data reported in the count.
Imagine a straight fill down Column AO: the count increments up, as expected, until the fixed starting point is manually reset to a new line. Counts currently vary between 1 and 27, and it’s a serious pain to manually reset this every time: I’m not very far into this, and already I’ve got about 9300 lines of data. GRRRR!
Let’s imagine the first data set is contained within the range AP5:AP11, followed by a data set within the range AP13:AP19, and so forth.
I’m hoping I can just (at least for now) put in a “reset command” column: when, for example, cell AN13 contains “RESET” — or if a “reliably filled” stack such as in column R is suddenly blank (column AP contains “empty” cells as well as cells being counted), then — the fill automatically changes in Cell AO13 to:
=IF(COUNT(AP$13:AP13)<10,“P0”&COUNT(AP$13:AP13),“P”&COUNT(AP$13:AP13))
I understand that there needs to be some sort of discriminator between the “=” and the “IF” in the above examples — perhaps I can do all this by using a nested “IF” function, but I don’t know the syntax for generating a discriminator that will work as I need.
I tried starting with
=IF(AND(AN5=“RESET”,COUNT(CELL(“ADDRESS”,AP5):AP5)<10),“P0”&COUNT(CELL(“ADDRESS”,AP5):AP5),IF(AND(AN5=“RESET”,COUNT(CELL(“ADDRESS”,AP5):AP5)>=10),“P”&COUNT(CELL(“ADDRESS”,AP5):AP5),IF(COUNT(AP$5:AP5)<10,“P0”&COUNT(AP$5:AP5),“P”&COUNT(AP$5:AP5))))
which doesn’t work:
- When the adjacent cell (on the same line) in Column AN = “RESET”, it returns “P00” — which would be hilarious if I was in kindergarten, but it isn’t getting even a smile from me, right now; worst-case, it should at least alternate between 1 and 0, so I have no idea what’s going on, there.
- Any time the adjacent cell (on the same line) in Column AN doesn’t = “RESET”, the count begins from Cell AP5.
I don’t know how to (non-manually) integrate recurring anchors (the “$” cells) into the formula.