• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Complex Validation

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
Geekamo
Asked:
Geekamo
  • 7
  • 7
1 Solution
 
redmondbCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now