Solved

Complex Validation

Posted on 2013-02-06
14
179 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

830 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