Conditional formatting of "Singular days off"
Hi Friends,
I am currently creating a roster; I have hit a problem where I have created a formatting formula that will highlight singular blank cells if both adjacent cells are non-blank. However, it has become tedious to enter the conditional formatting to every single cell individually. Is there a way to expedite my current process? a paste all function? or a better formula?
In the example workbook you'll find my formulas in D7:Q7 of sheet "Roster"
Thank you in advance for your help
Shaz
Please Login or Register to view this content.
Ben Van Johnson
As a conditional format?
This highlights the cell before in the row ..
Last edited by shazzab; 08-11-2024 at 08:53 AM.
I cannot open your workbook. It's complaining about it being protected. Please remove any workbook/worksheet protection and attach it again. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There shouldn't be any protections; but i've re-uploaded. Hopefully that works.
Thank you
No - "the file couldn't open in protected view".
Can you save a copy from WITHIN the file and try uploading that?
This time copied from my office 365 account and not the computer drive
Same problem, but as I understand it, it's the same file!
Did you do as I suggested? Open the file - save a copy to a different location - upload the copy.
Sure did. Im wondering if its work related accessibility. Ive sent a copy to my home email, download to my phone and reuploading use my person phone.... maybe thats the key
Last edited by AliGW; 08-11-2024 at 09:49 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.
That has opened. I'll now have a look at the problem.
Right - that's an odd formula! Please explain EXACTLY what you think it is doing.
=IF(ISNUMBER(MATCH(C7,INDIRECT("'Formula'!$K$24:$K$25"),0)),SUM(COUNTIF(D7:E7,INDIRECT("'Formula'!$I$24:$I$43")))>0,FALSE)
Sorry that formula is one that works; that one will highlight cells if within a 3 day window of a night shift (NN1 or S-NIGHT).
The one I'm having trouble in is in the conditional formatting - this individual formatting of a cell will highlight the cell red if the previous and the next day have a shift in it. The aim is to highlight and "Island days off"
=COUNTIFS(C7,Formula!A2:A14,E7,Formula!A2:A14,D7,"")
I don't see any such formatting rule anywhere in the worksheet ...
But you could try this for cell C7:
=AND(ISNUMBER(MATCH(B7,TOCOL(HSTACK(Formula!$K$24:$K$25,Formula!$I$24:$I$43),1),0)),C7="",ISNUMBER(MATCH(D7,TOCOL(HSTACK(Formula!$K$24:$K$25,Formula!$I$24:$I$43),1),0)))
Applies to: =$C$7:$AD$70
Hmmm how frustrating that my conditional formulas did not even translate while uploading.
So what I ended up doing was individually formatting each cell like this. D7 =COUNTIFS(C7,Formula!A2:A14,E7,Formula!A2:A14,D7,"") and then E7 =COUNTIFS(D7,Formula!A2:A14,F7,Formula!A2:A14,E7,""), so on and so forth.
Thank you for your formula; Seems like it also does protonleah's does and highlights the previous cell regardless if it's blank or not.
I'm wondering if there is no work around for what I need it to do
Attachment 876657
Why not use this:
=COUNTIFS(C7,Formula!$A$2:$A$14,E7,Formula!$A$2:$A$14,D7,"")
for C7 and apply it to the whole range?
But, I confess, I am not sure what you think it actually does. COUNTIFS does not return TRUE or FALSE, which is what you would want for conditional formatting.
I'm not 100% sure what's supposed to happen as you haven't provided a manual mock-up - just failing formulae!
I don't see what is wrong with my formula:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
DE8 DE8 DE8 DE8 DE8 DE8 DE8 DE8 DE8 DE8
Sheet: Roster
Maybe you didn't apply it correctly?
Try, =COUNTIFS(B$5:D$5,">="&$C$5,B7:D7,"<>")=3
Ahh; I see what's happened. So yours does work the way it needs to but for some reason, it does not apply the same when copied to Microsoft365. Looks like the same translation issue I had when my own formula did not work for you.
What do you mean? I am using 365. Custom formatting needs setting up in the desktop app, but will then work in the browser version (although you can't edit it there).
Are you sure you're looking at the same file??? If the CF is correct on the desktop then it should be correct in the browser. I have NEVER known Excel lose formatting in this way.
I'm sorry; this does nothing on my worksheetOriginally Posted by josephteh
Yes - it works fine in the browser as well, as I said.
Please attach your workbook showing it does nothing.
Done thank you
Dang it; what am I doing wrong then
The latest attachment won't open - protected view message again.
Apply the rules in the desktop version and save. Then open that file in the browser. My formula works and updates just fine. I don't know what you are doing wrong, but I would not advise messing with CF formulae in the browser version.
That said, I can't break it in the browser! I have no idea what you are doing wrong ...
Last edited by AliGW; 08-11-2024 at 11:23 AM.
Oh okay, ill give that a go. Thank you for your help.Apply the rules in the desktop version and save. Then open that file in the browser. My formula works and updates just fine. I don't know what you are doing wrong, but I would not advise messing with CF formulae in the browser version.
That said, I can't break it in the browser! I have no idea what you are doing wrong ...
Also i have no idea why its protecting the view this time around. Ive uploaded the exact same way i got it to work last time :O
Very odd - never had that with a workbook from anyone else here (and I open loads of them every day). Let us know how you get on.
No luck so far; and what's worse is that I've found another fail in my original formatting. I've found that the cells only highlight when DE8 is present on both of the adjacent cells. If another shift is selected the empty cell in between is no longer highlighted ...
I will keep trying though.
And again, thank you for your help
My formula should work with any shift.
Your workbook in post #23 couldn't be opened in protected view.
Example of mine working with any shift.
It works for me
Hightlight C7:AD70, then use this CF formula:
Please Login or Register to view this content.
Quang PT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks