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.

 

[cmowla]

Glad that Fluff's formulas worked for you. But at least someone who has older versions of office can use my approach to tackle the same issue. (And it's good to know that a helper column is needed after all.) Win-win.

This is wonderful!

I have spent hours on this and you came swooping in with an alternative formula! Thank you!

If you would have spent just minutes reading and following my instructions, you would have had what you wanted. But it's better for the long-term to not have to keep clicking a button.

 

[Ev1lZer0]

With a helper column like

Fluff.xlsm
	A	B	C
1	Date of Trial	Column1	Column2
2	09/06/2022	1
3	09/06/2022	1
5	10/09/2022	1
7	12/09/2022	1
8	12/09/2022	1
9	12/09/2022	1
10	12/09/2022	1
11	12/09/2022	1
12	12/09/2022	1
13	12/09/2022	1
14	12/09/2022	1
15	12/09/2022	1
16	12/09/2022	1
17	12/09/2022	1
18	12/09/2022	1
19	12/09/2022	1
20	12/09/2022	1
21	13/09/2022	1
22	19/09/2022	1
23	19/09/2022	1
24	20/09/2022	1
25	21/09/2022	1
26	26/09/2022	1
27	26/09/2022	1
28	26/09/2022	1
29	26/09/2022	1
30	28/09/2022	1
31	30/09/2022	1
32	01/10/2022	1
33	02/10/2022	1
34	03/10/2022	1
Main

Cell Formulas
Range	Formula
B2:B3,B5,B7:B34	B2	=SUBTOTAL(103,[@[Date of Trial]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C34	Expression	=ISODD(MATCH($A2,UNIQUE(FILTER($A$2:$A$34,$B$2:$B$34=1)),0))	text	NO
A2:C34	Expression	=ISEVEN(MATCH($A2,UNIQUE(FILTER($A$2:$A$34,$B$2:$B$34=1)),0))	text	NO

This solution also resolved being able to filter out data and still have the banded colors correct! Thank you once again! I wish I could mark multiple answers as 'right'.

 

[Ev1lZer0]

Glad that Fluff's formulas worked for you. But at least someone who has older versions of office can use my approach to tackle the same issue. (And it's good to know that a helper column is needed after all.) Win-win.


If you would have spent just minutes reading and following my instructions, you would have had what you wanted. But it's better for the long-term to not have to keep clicking a button.

Thank you for your assistance, your guidance on VBA assisted me with another solution on the same spreadsheet.

 

[Fluff]

Glad to help & thanks for the feedback.

(And it's good to know that a helper column is needed after all.)

Beg to differ, there was no need to use a helper column for what the OP was originally trying to do.

 

[cmowla]

Beg to differ, there was no need to use a helper column for what the OP was originally trying to do.

I don't know why you "beg to differ" because in my first post (with the helper column), I specifically mentioned that my solution (which used helper columns) works if:

• This conditional formatting will work, even if rows are filtered/hidden.

Note that I didn't say:

This request requires helper columns, even if you don't need it to work when rows are filtered.

But then you came and said:

Another option without any helper columns

And I replied:

but your solution doesn't hold if rows in the table are filtered. I assume that this is a desired/expected feature (since the table in the OP has filters).

And of course you said:

The OP made no mention of filtering the data & the formula I suggested replicates what the OP is doing with the helper column.

Then the OP mentioned:

. . . I will have to experiment with the filters to see if keeping the CF is actually important.

To be honest, I hope it's not, since this seems like a much simpler way of 'fixing things' than ]@cmowla's way.

And, after playing with it, the OP came to the realization that having it work when filtering the table is actually desired:

With the CF not holding while I'm filtered in the table, I'm confident this will be fine. But if I wanted to make the CF hold true even while filtering, can this be done with this formula and a VBA that does something?

I briefly looked into @cmowla's formula, but it got too complicated and I couldn't figure out what exactly wasn't working.

then you said:

With a helper column like

Therefore, when I mentioned that:

This can be done with (at most) 2 helper columns + 2 conditional formatting rules.

It was with the feature that it could work with rows hidden. Even though the OP didn't specifically ask for it, it's clearly what he/she wanted. Why wouldn't the OP want that? If he/she wanted it to be applied to a table with filters . . . can you state one reason? More importantly, can you state one reason why it would be better (in any way) for it not to work with filters being applied? The cells in the table are going to retain color when filters are applied, right?

Have you forgotten that a client who knows little about programming will make assumptions that different features naturally appear in an implementation, even though that they technically (and most likely will not) show up in the implementation because they are technically additional features that cost more time and thought to add? I went the extra mile (and stated what the extra mile requires), but got told that I'm "making it too complicated".

So at the end of the day, I gave what the OP wanted in full at the start (with requires helper columns), but I didn't use array formulas so that VBA wouldn't be required. It's good that you use array formulas to save a lot of headache, but I would appreciate if you would not continue to insist that said:

This request requires helper columns, even if you don't need it to work when rows are filtered.

Because I never said that. I knew what the OP really wanted required them.

Thank you.

 

[Fluff]

I don't know why you "beg to differ" because in my first post (with the helper column), I specifically mentioned that my solution (which used helper columns) works if:

I was not talking to the OP NOT you, at no time did I ever mention you or your solution, therefore your rant is totally irrelevant.

I knew what the OP really wanted required them.

Shame you not quite so prescient all the time, maybe you need to give your crystal ball a good clean.

 

[cmowla]

What? You quoted me in your post. Sure you were talking to me! Totally relevant.

Beg to differ, there was no need to use a helper column for what the OP was originally trying to do.

 

[Ev1lZer0]

I don't know why you "beg to differ" because in my first post (with the helper column), I specifically mentioned that my solution (which used helper columns) works if:


Note that I didn't say:


But then you came and said:

And I replied:

And of course you said:

Then the OP mentioned:

And, after playing with it, the OP came to the realization that having it work when filtering the table is actually desired:

then you said:


Therefore, when I mentioned that:

It was with the feature that it could work with rows hidden. Even though the OP didn't specifically ask for it, it's clearly what he/she wanted. Why wouldn't the OP want that? If he/she wanted it to be applied to a table with filters . . . can you state one reason? More importantly, can you state one reason why it would be better (in any way) for it not to work with filters being applied? The cells in the table are going to retain color when filters are applied, right?

Have you forgotten that a client who knows little about programming will make assumptions that different features naturally appear in an implementation, even though that they technically (and most likely will not) show up in the implementation because they are technically additional features that cost more time and thought to add? I went the extra mile (and stated what the extra mile requires), but got told that I'm "making it too complicated".

So at the end of the day, I gave what the OP wanted in full at the start (with requires helper columns), but I didn't use array formulas so that VBA wouldn't be required. It's good that you use array formulas to save a lot of headache, but I would appreciate if you would not continue to insist that said:

Because I never said that. I knew what the OP really wanted required them.

Thank you.

@cmowla, Please understand that I saw the significance of what you were showing me and I was impressed by the extra mile you went through. I was really floored that I got such a thorough response so quickly! Unfortunately, it was my lack of experience that made your solution a bit too complicated, and I have no doubt that when I tried your solution and it didn't work, it was user error.

When I saw @Fluff's response, it was much more in line with what I was used to, and it worked after a couple of adjustments. When I decided I did want the formatting to hold while filtering, I reached back out and asked additional questions that once again, brought me a solution. I decided to go with @Fluff's solution because I was more familiar with that process and felt if I needed to make minor adjustments with time, I could do so more comfortably.

That being said, I still have no idea what the helper column is actually doing... I tried to dissect it, and I can't make sense of it. But it works, so who am I to judge?

 

[Fluff]

@cmowla You really do like to twist things don't you, I only said that in response to your post.
My original post in this thread was NOT directed you (despite what you might think) & was replicating EXACTLY what the OP was trying to achieve.
When the OP asked for it to work when filtered I happily supplied a solution to that to that as well.

 

[cmowla]

@Ev1lZer0 , that's perfectly understandable. When I mentioned the statement about "clients", it wasn't a rant against clients. It's just a fact of life. If you ever decide to program for a living, you will understand that very well. (Even when I become my own "client", this happens in that, I may not know what I want fully entails until it's completed.)

@cmowla You really do like to twist things don't you, I only said that in response to your post.

And it was in my first post that I said all that I said about requiring helper columns. So how am I "twisting" things?

My original post in this thread was NOT directed you (despite what you might think)

Did the OP mention the term "helper column"? In your original post, the first thing you said was:

Another option without any helper columns

How can that not be directed at my postings? Can you explain so that I may better understand how you think for the future?