Solved

Complex Validation

Posted on 2013-02-06
14
154 Views
Last Modified: 2013-02-11
Hello Experts,

Please view the attached screenshot - EE
Cell B7, is a drop down.  The two items you can select are "Square / Rectangle" or "Cylinder". If the user has selected the first option, then I don't want anything to happen.

But, if the user selects "Cylinder", then between cells C7 and D7, ONLY ONE can accept a value.

The screenshot doesn't show this, but if "Cylinder" is selected, then "Width" & "Depth" change to "Diameter" & "Circumference".  In that case, the user should only be giving me one answer between both of those cells.

Any ideas?

Thank you in advance for your help!

~ Geekamo
0
Comment
Question by:Geekamo
  • 7
  • 7
14 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38859058
Hi, Geekamo.

Select C7 and D7. In the Data Validation dialogue...
 - Set "Allow" to "Custom"
 - In "Formula:" enter "=IF($B$7="Cylinder",COUNTIF($C$7:$D$7,"<>")<2,TRUE)"

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859111
@ Brian,

Two things...,

Am I able to remove the cell refernces and use named cells instead?

Also, I forgot to include another thing...

If the user selects "Square / Rectangle", then the Depth, cannot be larger then the Width.

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38859148
Geekamo,

Am I able to remove the cell refernces and use named cells instead?
Sure! Just post them here and I'll amend the formula accordingly.

If the user selects "Square / Rectangle", then the Depth, cannot be larger then the Width.
=IF($B$7="Cylinder",COUNTIF($C$7:$D$7,"<>")<2,IF($B$7="Square / Rectangle",IF(COUNTIF($C$7:$D$7,"<>")=2,$D$7<=$C$7,TRUE),TRUE))

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859164
@ Brian,

I attempted to put the named ranges in myself, but clearly idk what I'm doing and it resulted in an error.

=IF(Object="Cylinder",COUNTIF(Width:Depth,"<>")<2,IF(Object="Square / Rectangle",IF(COUNTIF(Width:Depth,"<>")=2,Depth<=Width,TRUE),TRUE))

~ Geekamo
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38859185
Geekamo,

It doesn't like "Width:Depth", so either use the following...
=IF(Object="Cylinder",COUNTIF($C$7:$D$7,"<>")<2,IF(Object="Square / Rectangle",IF(COUNTIF($C$7:$D$7,"<>")=2,Depth<=Width,TRUE),TRUE))
...or create a new name for "$C$7:$D$7".

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859199
@ Brian,

This is perfect, thank you!  I created a new named range.  "WAD".

=IF(Object="Cylinder",COUNTIF(WAD,"<>")<2,IF(Object="Square / Rectangle",IF(COUNTIF(WAD,"<>")=2,Depth<=Width,TRUE),TRUE))

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38859300
Geekamo,

We're not quite there yet - suppose a user has fully entered a "Square / Rectangle" and then changes to "Cylinder". We now have both a Diameter and a Circumference. A couple of possibilities ...
 - Data Validation for Object that requires one (or no) value for "Cylinder". (But that loses theDrop-Down.)
 - Conditional Formatting to highlight the problem.

Regards,
Brian.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:Geekamo
ID: 38859346
@ Brian,

I didn't even think of that happening...

Hmm, what if I put in a macro that when Object changes it's value, it automatically clears any dimension that was entered.

I already have a macro that I could repurpose for this.

That would solve my problem, right?

I'm thinking it would make sense, cause if you're changing the Object - then you're clearly going to be entering in different dimensions.  And even if they typed the dimenions in first, and later realized they had the wrong object selected, I have no problem with forcing them to retype the dimensions back in.  It's only 3 cells after all.

What are your thoughts?

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38859368
Geekamo,

While it would work, a Change Event macro seems a bit of overkill to me. Personally, I'd be happy with the Conditional Formatting, but if you don't want that - how many shapes do you have? Unless it's a silly amount, why not give the users radio buttons instead of picking from a Drop-Down? It's a single click for the user and the macro called can do "anything".

Regards,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38859404
@ Brian,

Ya, I know I could do conditional formatting - but with all the hands that will be using this workbook, I want to account for all the things the user could screw it up, so the more I force them into a corner the better.

I just know that even if I had a note, even in big bold red text saying "If the cell changes to RED then you need to do...blah blah", and I know... I would still be asked...

As for the shapes, there is only two.

Object = "Square / Rectangle" & "Cylinder".  

And there's two other cells that have drop downs too.

I've actually never used a radio button in Excel.  I do know what it is, but I wouldn't have a clue how to make it work, let alone writing the macro that does the work.

~ Geekamo

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38859480
Geekamo,

Please see attached. Don't mind the (appalling) design - it's pure proof of concept.

The good news is that Excel does almost all the work, so the (worksheet) code is trivial...
Sub Clear_Dimensions()

Me.Range("C7:D7").ClearContents


End Sub

Open in new window


Regards,
Brian.Measuring-Up---Stripped-Down-Ver.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38866825
@ All,

Sorry for the delay in getting back to my post.  I will be back over the weekend.  Thank you in advance for your patience!

~ Geekamo
0
 
LVL 1

Author Closing Comment

by:Geekamo
ID: 38878459
@ Brian,

In looking at your latest post with the radio buttons - I don't have the slightest clue what is going on.  lol  I was looking into the code, and I don't see anything there.  Then I noticed the "Sheet1" which the filters.  Ya, I'm lost.

I think for simplicity, I will stick with a change event everytime that field is changed.  Aside of that, that points are yours.  It works great!

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38878514
Geekamo,

Apologies, Sheet1 is irrelevant. I was just checking some setting and forgot to delete it.

The radio buttons are really straightforward...
 - Cell A7 is tied to the buttons - it's "1" when "Square" is selected, "2" when it's "Cylinder"
 - B7's formula uses A7 to decide whether to display "Cylinder" or "Square".
 - Finally, when one of the buttons is selected, it calls the "Clear_Dimensions" macro to reset the values in C7:D7.

The nearest thing to complexity about the buttons (and the frame around them) is that they've been grouped together (a matter of selecting them, right-clicking, selecting "Grouping" and then "Group"). So, to work on the individual buttons, they'd first need to be ungrouped (right-click, "Grouping", "Ungroup").

Please feel free to post here if you want to investigate buttons further. (Although there's not much more than I've mentioned above - "Regroup" comes to mind!)

Regards,
Brian.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now