Conditional Formatting with Multiple Drop Downs [Closed]

iwnixon 3 Posts Wednesday November 27, 2013Registration date April 2, 2014 Last seen - Nov 27, 2013 at 01:05 PM - Latest reply:  iwnixon
- Dec 3, 2013 at 11:20 AM
I have a spreadsheet that has multiple drop downs. I'm trying to get Excel to highlight errors made in one column if a particular option is chosen based on what is selected in a separate column. Here is the exact situation:

In Column B I have a drop down titled "Service" with 7 options. In Column C I have a drop down titled "Ratio" with 8 options. Depending on what "Service" option is chosen in Column B, there can only be a few possible "Ratio" options in Column C. If for whatever reason the incorrect ratio is chosen in Column C, I'd like Excel to highlight that cell.

Example: I choose "FHG 4" in the "Service" column. The only correct options in the "Ratio" Column are "2 to 1", "3 to 1" or "4 to 1". If any other Ratio is chosen, I'd like it to be highlighted in red.

How do I get it so that each of the 7 "Service" options in column B have a right and a wrong "Ratio" in Column C?
See more 

8 replies

Darenza 8 Posts Thursday July 18, 2013Registration date January 24, 2015 Last seen - Nov 27, 2013 at 11:07 PM
0
Helpful
Thanks so much for your inquiry regarding 'multiple formatting.' However, I'm not tet familar with the ramifications of these precedures and how they may react in any given manner ir format, considering what information you've already supplied about your particular system. I hope that you not give up on finding something that will help in this situation, however.
TrowaD 2286 Posts Sunday September 12, 2010Registration dateContributorStatus November 7, 2017 Last seen - Nov 28, 2013 at 11:07 AM
0
Helpful
Hi Iwnixon,

Your query is not clear.

When "FHG 4" is selected from "Service" Drop Down List (DDL), then only "2 to 1", "3 to 1" or "4 to 1" are available from "Ratio" DDL, right?

Then how can a wrong option be chosen?

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0
Helpful
TrowaD,

I apologize. You're correct in that I wasn't clear. The options in the "Ratio" DDL are 1 to 1 all the way through 8 to 1, giving me 8 options. Each one of the 7 options in the "Service" DDL has a corresponding ratio. So the ratios for "FHG 4" could only be 2 to 1, 3 to 1, or 4 to 1, whereas the ratios for "FHG 8" could be only be 5 to 1, 6 to 1, 7 to 1, or 8 to 1.

I want to eliminate the Ratio options that it cannot be when I select a particular Service. I hope that makes sense.
TrowaD 2286 Posts Sunday September 12, 2010Registration dateContributorStatus November 7, 2017 Last seen - Nov 28, 2013 at 11:35 AM
0
Helpful
Hi Iwnixon,

Here is a link to a query where I explain how to link you DDL's:
http://ccm.net/forum/affich-638292-inserting-text

Let me know how that works out for you.

Best regards,
Trowa
0
Helpful
That's perfect!! That's exactly what I needed! Thank you so much for your help!
0
Helpful
TrowaD,

Now that I've got that taken care of, is it possible to have a second DDL determine data based on a DDL that has already been used?

So I used the "Service" DDL to determine the numbers to choose from in the "Ratio" DDL. Now I'd like to have a second DDL, titled "ISP Goal", be based on the "Service" DDL. When I go to put the info in using Shift, Control, F3, it replaces the original info that I put in to determine the "Ratio" list.

Can you help?
TrowaD 2286 Posts Sunday September 12, 2010Registration dateContributorStatus November 7, 2017 Last seen - Dec 3, 2013 at 11:03 AM
0
Helpful
Hi Iwnixon,

I don't think there is an easy way to do this.

If you are willing to share all your DDL's in it's entirety, then I can try to write a supporting macro.

Could you upload your file with just the DDL's in the right place? Use a filesharing site like www.speedyshare.com or ge.tt for that.

Best regards,
Trowa
0
Helpful
Hey Trowa,

Here is the link for the file: [code]http://speedy.sh/RneNU/OTB-2014-New-Service-Note.xls[/code]

I found a video that shows exactly how to do what I'm trying to do, but I haven't applied it yet.

I do have one final query that I'm totally lost on. It may be possible, it may not be. I'm trying to prevent any overlap of time based on the date. So if I put in 12/2/2013 and then go over to the "Time In/Out" DDL's and put 9:30a as the "Time In" and 12:30p as the "Time Out", and I move down a line and put 12/2/2013 again, but I try to put the "Time In" as 11:30a, I'd like to flag it or prevent it.

Does that make sense?