Complex Validation

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.

@ 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

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.

@ 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

@ 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

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.

@ 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

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.

@ 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

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

Select allOpen in new window

Regards,
Brian.Measuring-Up---Stripped-Down-Ver.xlsm

@ 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

@ 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

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.