Solved

Complex Validation

Posted on 2013-02-06
14
172 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

803 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