Conditional Formatting with Multiple Drop Downs

[Closed]
Report
Posts
3
Registration date
Wednesday November 27, 2013
Status
Member
Last seen
April 2, 2014
-
 iwnixon -
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?

8 replies

Posts
7
Registration date
Thursday July 18, 2013
Status
Member
Last seen
January 24, 2015
2
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.
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
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.
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.
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
Hi Iwnixon,

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

Let me know how that works out for you.

Best regards,
Trowa
That's perfect!! That's exactly what I needed! Thank you so much for your help!
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?
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
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
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?