Conditional Formatting w/ Formula Is and multiple sources?

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
Here is my problem: I have multiple choices for each day of the week, 5 Mondays through 5 Fridays (morinings and afternoons too). I have tried several variants of Formula Is such that if each Monday morning is empty, or blank, then the conditional formatting will be for the cell on another worksheet to be filled in grey. I can do this for one choice but not multiple selections, anyone have an idea here?
Muster 01.xls
DEFGHIJK
2DAYOFCLASSBEGINTIMEAMBEGINTIMEPMENDTIMEAMENDTIMEPMAMHoursPMHoursSESS.HOURS
3Mon
4Tue
5Wed
6Thu
7Fri
8Mon
9Tue
10Wed
11Thu
12Fri
13Mon
14Tue
15Wed
16Thu
17Fri
18Mon
19Tue
20Wed
21Thu
22Fri
23Mon
24Tue
25Wed
26Thu
27Fri
Sch


The W/S that has to be greyed out if all 5 parts of a day are blank on the schedule will follow. Thanks. :biggrin:
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
Here is the Muster that will actually get the COnditional Formattting based upon the schedule:
Muster 01.xls
GHIJKLMNOPQRSTUV
8MonTuesWedThurFri
9DateDateDateDateDate
10     
11AMPMAMPMAMPMAMPMAMPM
12
Muster
 

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
Here goes an example of what I have unsuccessfully tried:

I have put onto the worksheet Muster an Insert-Name-Define of AAA
and =(Sch!$E$5+Sch!$E$10+Sch!$E$15+Sch!$E$20+Sch!$E$25)=""

Meaning I desire to see if the cells on the schedule, as noted, are blank I want a conditional format to kick in and make those cells filled with grey.

That I've done by putting a Conditional Format of Formula Is = AAA then
make it grey, see? Alas, it hasn't had the desire effect. :(

I can do it by choosing just one cell on the schedule like E5 but not multiple selections, hence the title of the post? Any ideas here?
:eek:
 

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
I got it!

=AND(Sch!$E$3="",Sch!$E$8="",Sch!$E$13="",Sch!$E$18="",Sch!$E$23="")

For my example I can use any cell on the Muster and have it shade grey based upon the conditional formatting if all 5 multiple selections, or any one of them contains data. I am SOOOoo pleased. :biggrin:

I apologize for the ---> in my posts. Perhaps I suffer from Adult ADD? :rolleyes: I just get so excited when asking for help and when I resolve an issue. :)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
mpartyka said:
I apologize for the ---> in my posts.
This is a recognized problem - see the link at the top of the forum to see how to avoid the &nsbp.
 

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
Thanks Norie, I saw that after I had posted my topic and after I had the ability to edit it. (Learning curve).

However on my problem I still have a problem because I have 5 Mondays, 5 Tuedays, 5 Wednesdays, 5 Thursdays, and 5 Fridays. Seems that the Formula bar when define a Forumla under Insert-Name-Define has it's limit.
So, what I intend to do is to add 5 more cells to the schedule which will look at the 5 sets of 5 weekdays and then take those 5 cells and use the conditional formatting with just them. Seems there are always solutions when using Excel and I have to learn a bit more patience. :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top