Extending conditional formatting in combination met merged cells

[jackhays]

Hi guys,

Im new on the forum so I hope im in the right section to ask my question. It basically comes down to this:

A1:A3 are merged and relate to B1, B2, and B3. A4:A6 are merged and relate to B4, B5, B6, and so on. If merged cell A1:A3 is any value I want conditional formatting to highlight cells B1, B2, B3. The same goes for if there is any value in merged cell A4:A6, the I want the unmerged cells B4, B5, B6 to light up.
Eventually I want to be able to extend the sequence by dragging the conditional formatting down column B. However whatever I try changing in the rule or the range applied, it will not continue the sequence (one cell merged of three, relating to the single cells). Please help me

Kind regards,

Jack

[Beamernsw]

Hi Jack, try using this for the conditional formula.

=INDIRECT("A"&INT(ROW()/3+0.9)*3-2)>0

[jackhays]

Thanks it works great! A little follow up question, now lets say I want to use the same jumps it now makes but the condition isn't just 'any value' but a AND or OR condition. Were do I fit that into your formula? and again thanks a million!

[jackhays]

I guess instead of the '>0' ?

[Beamernsw]

Maybe something like:-

=AND(INDIRECT("A"&INT(ROW()/3+0.9)*3-2)>0,INDIRECT("A"&INT(ROW()/3+0.9)*3-2)<15)
to colour the cells if the number is in the range of 1 to 14

or

=OR(INDIRECT("A"&INT(ROW()/3+0.9)*3-2)="Fred",INDIRECT("A"&INT(ROW()/3+0.9)*3-2)="Wilma")
to colour the cells if the cell contains Fred or Wilma

[jackhays]

Beamernsw thank you very much for your help. Im trying to make it work but I must be doing something wrong.

These were my two conditional format rules that I could not extend.
=AND(F$4="Energy", J$4="Yes")
=OR(F$4="IMS", F$4="AFW", F$4="FI")

Where F4:F6 and J4:J6 are the merged cells. This is what I filled in with your suggestions:
=AND(INDIRECT("F"&INT(ROW()/3+0.9)*3-2)="Energy",INDIRECT("J"&INT(ROW()/3+0.9)*3-2)="Yes")
=OR(INDIRECT("F"&INT(ROW()/3+0.9)*3-2)="IMS",INDIRECT("F"&INT(ROW()/3+0.9)*3-2)="AFW",INDIRECT("F"&INT(ROW()/3+0.9)*3-2)="FI")

To bad it is not working for me. Any suggestions what I am doing wrong?

[Beamernsw]

Hey jack, you're right it doesn't work. I didn't test it before I typed it in here, I honestly thought it would work. It should work. The only thing I'm guessing that somehow the AND & OR functions don't like the INDIRECT.....which just seems bizarre.
I have another idea, will reply back shortly.

Edit: nup..fail

So what range are these groups of 3. You want it to check F4 for "Energy" and then F7 and F10?
And what is the range that the colour is to be applied to?
Ahh sorry, just re-read your OP....makes complete sense.

I will keep trying.

[skjnair]

Hey everyone

Is it possible to nest an indirect funtion in an "if" function? I want the data validation to reference a different name if the indirect function is trying to reference a nonexistent name.

[Beamernsw]

Hi skjnair, sorry I have no idea I've tried to think on this and I can't get it to work.
I think you should re-post this as a new post in the formulas forum, you'll get more people looking at it and therefore a faster response.
Sorry I couldn't help

[jackhays]

Yes its weird because it does work for just a single condition. Yes that's what im looking for, so the start is F4 and then it goes on to F7, F10 and so forth. in my document the columns A to L are all merged in rows of three, from the column N the rows split into three. The merged columns in A to L determine the formatting of the columns from N onwards and then for the three cells together. So for instance, value in A4:6 and a value in D4:6 determine which columns for the row 4, 5, and 6 from N onwards light up. for some columns the condition can be an AND or a OR condition. hope this helps

[Beamernsw]

AND works in Conditional Formatting, INDIRECT works in Conditional Formatting, both work together in a cell but they won't work together in Conditional Formatting. That kinda sucks.
This formula in a cell copied down works fine:-
=IF(AND(INDIRECT("F"&INT(ROW()/3+0.9)*3-2)="Energy",INDIRECT("J"&INT(ROW()/3+0.9)*3-2)="Yes"),"Colour","")
It places the word "Colour" in each of the three cells that correspond to "Energy" in column F and "Yes" in column J.
But it refuses to work with a Conditional Format.
Hopefully some smart cookie reading this might have another idea

[Beamernsw]

YAY!! I consulted the google god again rephrasing my question and had a win.
Well, for the AND statement at least. Try this:-
=(INDIRECT("F"&INT((ROW()+2)/3)*3-2)="Energy")*(INDIRECT("J"&INT((ROW()+2)/3)*3-2)="Yes")


Edit:- I just came up with and OR alternative based on the above.
=(INDIRECT("F"&INT((ROW()+2)/3)*3-2)="Energy")+(INDIRECT("J"&INT((ROW()+2)/3)*3-2)="Yes")

Whereas multiplying the 2 queries results in true (1) or false (0), adding the 2 queries together will result in true (1) even if only 1 query is true 0+1=1

[jackhays]

I truly love you bro.

[Beamernsw]

LOL, you're most welcome. I'm loving learning new stuff like this on these forums, its a great experience.

[jackhays]

Hi Beamernsw, i'm back again with a new challenge. Hope your willing to help me out once again

It still has something to do with irregular extension of a formula. The formula is supposed to count the number of times a certain combination is made. So I started out with the basic =AND(COUNTIF(A1,"Yes"))*(COUNTIF(A2,"On time")), the next comparison I want it to make to see if it should count it is: =AND(COUNTIF(A4,"Yes"))*(COUNTIF(A5,"On time")) and then onwards to A7 and A8 and so on. I hope I am clear enough

Thanks a million!

[bebo021999]

Another option for CF with merge cells as per your rasing request in #6:

=AND(LOOKUP("zzz",F$1:F4)="Energy",LOOKUP("zzz",J$1:J4)="Yes")

=OR(LOOKUP("zzz",F$1:F4)="IMS",LOOKUP("zzz",F$1:F4)="AFW",LOOKUP("zzz",F$1:F4)="FI")

with LOOKUP("zzz",F$1:F4) refer to most last cell in range F$1:F4 containing text

[Beamernsw]

Hey Jack, sorry I took so long to get back in here.
bebo021999's lookup looks pretty nice and way less complicate then mine

But since I already modified my old clunky formula (because I didn't see the 2nd page where bebo had answered) I'll paste it anyway. I didn't test it either, I just change the numbers. Should still work though.

Please Login or Register  to view this content.