Conditional formatting...beyond basics (Excel 2003)

**dmcnab** · 2006-02-13 19:47

I am attaching a sample of a page from an incomplete workbook. I want to assign jobsites to various employees; that is done on a different worksheet. The attached sheet is a sampe of the sheet that employees view to see their assignments; a pink cell = a full-day job, a blue cell = a multi-day job and a yellow cell = an unfinished job. I can only use 3 colours b/c conditional formatting only permits 3 items; the cell colour is triggered by placing asterisks in front of the person's name.

In the attached file, you will see on Jan 4, Mr. Black is at job site 1 (an all day job). Black cannot see the lower 1/2 of the page; he looks at the upper 1/2, under his name, to see where he is assigned, in cell J8.

I tried to use a COUNTIF($F26:$L26,J20)=1 formula to turn cell J8 blue, but it doesn't work...maybe b/c the asterisks are wildcards..??...I don't know....but I need a formula for use in conditional formatting that will cause J8 to turn blue, or, cell I8 to turn yellow based on G27 being ***White........any suggestions ??...thanks....see sample attached

**dmcnab** · 2006-02-14 01:05

Thank you for your help, Hans......I should apologize--when I saw your reply, I realized that I should have put a more detailed sample with my post.....I was trying to keep things simple, but probably ended up making more work-----I have attached a 'fully detailed' sample to this reply----you will see that the layout of the page is different than in my first sample....there are almost 24 employees, and only 18 or 19 job sites, so you will see that the columns of name & sites are offset.....I'm not sure but I imagine that this would prevent your suggested formula from working properly ??..??......if you could look at the new attachment (Sample 2.xls) you'll see what I mean.....if your earlier formula will still work, then I'm sorry to bother you again.....and if not, then could I ask you to give this some more thought?...Thank you.

**sdckapr** · 2006-02-14 02:12

Select E6:T13, then add this formula:

=LEN(IF(ISNUMBER(E6),INDEX($E17:$O17,E6),""))-LEN(SUBSTITUTE(IF(ISNUMBER(E6),INDEX($E17:$O17,E6) ,""),"*",""))=1

Then follow the rest of Hans' directions.

Row 6 goes with row 17 in the lookup and the range goes from E to O. Change as neccessary

Steve

**dmcnab** · 2006-02-14 03:53

Attn: SDCKAPR: thank you for your help, altho your formula only worked on the sample that I attached.........when I applied it to the real worsheet, it didn't work----what happened was that, using 1 (blue) for example, it coloured any cell found in the "Other Info" columns that are adjacent to the Job Site columns, so long as the cell was empty.....it should have colored the cells under Job Site where a name was preceded by 1 asterisk, but instead, seemed to offset itself and colourd each cell in the first row under the Other Info columns, unless the cell had something in it......I have attached a portion of the actual worksheet...it seems that the blue cells under the Other Info columns turn blue if they have 0 in them; if I remove the 0, the blue disappears....also, check the assignments for WWW.....if I cancel it, then the blue disappears from the row, but if I re-enter the assignment, the blue returns.......I am stumped......see: Sample for sdckapr.xls attachment

**HansV** · 2006-02-14 07:24

Select E6:J13.
Select Format | Conditional Formatting...
In the first dropdown, select Formula Is.
In the box next to it, enter the formula

=LEN(IF(ISNUMBER(E6),INDEX($E22:$J22,E6),""))-LEN(SUBSTITUTE(IF(ISNUMBER(E6),INDEX($E22:$J22,E6) ,""),"*",""))=1

Click Format..., activate the Pattern tab and specify blue, then click OK.
Click Add >>
Repeat the above steps, but with =2 and Pink.
Click Add >>
Repeat the above steps, but with =3 and Yellow.
Finally click OK.

See attached workbook.

**HansV** · 2006-02-14 07:50

Why do you have 0 in all those "blank" cells? It would be a lot easier without all those 0s. With them, you must build in an extra check to exclude 0s. See attached version.

**sdckapr** · 2006-02-14 09:53

<hr>altho your formula only worked on the sample that I attached.........when I applied it to the real worsheet, it didn't work<hr>

This is why it is important to make sure your sample is representative of the real worksheet... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Steve

**dmcnab** · 2006-02-14 13:07

Thank you Hans...and sdckapr.....I don't think that I can attach a copy of the real sheet b/c it is for a whole year and the file size limit if 100KB....if you look at the Sample for sdckapr.xls file, you will see that there is data in the Other Info columns......in the real worksheet, this data comes into these cells due to a formula in the cells that draws the data from another sheet in the workbook [the "Scheduling" worksheet].....the sheet that I have attached is a copy of the sheet that employees look at to find out their assignments and any pertinent info for that day (eg: use side door etc etc).....anyway, it seems that when I made up the sample by copying & pasting, it put zeros into those cells....in reality, those cells have formulas in them.....when I used your latest cond formatting formula, it didn't work, perhaps b/c my real sheet has formulas (??) rather than zeros..??.....I could send you the entire worksheet (ie: for the year) as an attachment, but not likely by posting it here....I suspect that I would have to send it to you directly via email....

**HansV** · 2006-02-14 13:11

Perhaps you should rethink the problem first. Or study the workbooks already posted and adapt them for your "real" workbook.

**dmcnab** · 2006-02-14 23:11

Hello again Hans...I have re-done my workbook and would like to attach a small part of the actual...it is 119KB in size...is there any leeway on file size, b/c if I cut it down any further, it will really start to lose it's resemblance to the actual workbook ?

**HansV** · 2006-02-14 23:13

Have you zipped it?

**dmcnab** · 2006-02-14 23:31

Haven't zipped it....I have winzip, altho I hiave only used it to unzip....is it eay to zip?

**HansV** · 2006-02-14 23:40

Right-click your workbook in Windows Explorer and select WinZip | Add to name.zip where name is the name of the workbook. That's it!

**sdckapr** · 2006-02-14 23:41

Usually you can right-click and select WINZIP from the right click menu and chose "add to <filname.zip>" and it will create a file with the same name as the current file only zipped.

Steve

**dmcnab** · 2006-02-15 00:16

...ok...thanks guys.....I have now zipped it...can't really tell how much it shrunk it, but it is only 119kb unzipped....anyway, here is a real sample from my actual workbook...I have only shown the bare minimum, just enough to give you an idea of how it works.....I do the scheduling on the "Assignments" page; I do the Daily Notes on that page and they copy over onto the Yearly page....the Yearly page is the one that employees look at.....they can see where there job assignment is and anything relevant to that day.....the assignments get from the Assignments page to the Yearly page thru the (hidden) columns on the Yearly page...if you look at it , you'll see what I am trying to explain.....I tried to recreate the formala that Hans gave me, but things seem offset......and some of the colours don't come though......

Thread: Conditional formatting...beyond basics (Excel 2003)

Thread Tools