If formula and Indirect formula

[Ev1lZer0]

I am trying to get color bands on similar dates while having alternating colors on the rest of the table. I started by using an IF formula and had "=IF(A1=A2,B1,NOT(B1))" I also had come conditional formatting involved.
If you need more information, I am using the process shown on the website. "Colour Bands in Excel Table Based on Dates – Contextures Blog"

It works really well, except for when I insert or delete rows. Which will happen frequently.

When I add rows, I get an inconsistent formula error; when I delete rows I get a #REF error. These errors always start BELOW the inserted row.

---

To resolve this, I tried using an INDIRECT formula with the IF formula and ended up with this. =IF((ADDRESS(ROW()-1,COLUMN()-1))=(ADDRESS(ROW(),COLUMN()-1)),B9,NOT(B9)) it does not work. It is now alternating my true false statement incorrectly. in addition, the [value if false] and [value if true] statement is still referencing the cell above which ends up breaking the formula in the same way I stated earlier.
When I add rows, I get an inconsistent formula error; when I delete rows I get a #REF error. These errors always start BELOW the inserted row.

---

lastly, I tried changing the formula to "=IF((ADDRESS(ROW()-1,COLUMN()-1))=(ADDRESS(ROW(),COLUMN()-1)),(ADDRESS(ROW()-1,COLUMN())),NOT(ADDRESS(ROW()-1,COLUMN())))" My hope was to resolve the #REF error and Inconsistent formula error and address the issue where this formula seems to not correctly label true false.

--

I'm pulling out my hair and desperately need help

The end goal... to alternate color bands based on similar dates like the image below. The true and false should be alternated while similar dates should be grouped together and alternated.

And to state again, the solution needs to work when adding or deleting cells - mine in the past have not.

 

[Fluff]

What on earth do you think the formula in col B is? It's clearly visible in the image & the OP gave the actual formula.
Believe it or not this site isn't about you (and not every post is aimed at you), it's about helping those people who ask questions.

 

[cmowla]

What on earth do you think the formula in col B is? It's clearly visible in the image & the OP gave the actual formula.

Okay, now your previous post makes sense that your original statement about helper columns was directed at the OP and not me.

But:
I can understand if you posted just minutes after I did (that you were possibly writing up a response and wasn't able to read my post), but you posted 9 hours after I posted. So I "stupidly" assumed the small possibility that you were referring to me in your post. I know you're in England and I'm in the US, but there was still a good chance that you had time to read a post that was in no way posted after you began to write up one yourself. And you assumed that the OP knew what the term "helper column" means (but I didn't make that assumption). Hence why I believed what I believed.

But thank you for explaining your reasoning. I hope you can understand mine.

Believe it or not this site isn't about you (and not every post is aimed at you), it's about helping those people who ask questions.

Well, see it from my point of view. My first post not only satisfied the original request, but also the actual request (even though it required VBA, which I made a post about soon after the first). Your first post begins with a statement that can be interpreted in at least 2 ways. (Again, how did I know that you didn't read my post, considering that it was 9 hours after mine?) And you post a solution something that does less than what mine does/breaks quite easily (with no counteraction to make up for it), where, again, you begin that post with a statement that could be interpreted in 2 ways (given the context of the postings in the thread and time).

So I suggest you leave your ego behind when posting here.

I generally only post in threads in which there is no response so that I can avoid such conflict. I understand that it's your job to go review our postings, so it's required that you do the opposite.

But sorry. That's not how the human body works. Ego is required for a man to function, period. If I cannot function, I certainly won't be able to think and write up a technical solution for someone. But could I learn to take less offense from you in particular in the future, now that I know you better? Sure.