Applying Data Validation to Check Box activity -- or Alternative Solution

[nitro22888]

I have a fun one, and it is racking my brain to find a good solution.

I created a calculator for work, which works beautifully. But there is one little draw back that is now coming into play. It is a calculator to calculate run lengths for tape light, and it can calculate multiples at once and do them accurately.

You will see in the bottom left a box that says "INCLUDE CUT(S) AT SPECIFIC LENGTHS". What this means is, you can type in an overall requested length toward the top, but you can choose some of that requested length be cut at very specific cuts (like 50 inches or something). The only drawback is, if the "specific cuts" total length exceeds the requested length, you can end up with some negative results in some fields, and we don't want that. So I used data validation to prevent that.

However, I recently decided that it would be helpful if there was a checkbox where, when checked, the "requested length" would just be what the "specific lengths" are, and that way you could just type in specific lengths (if that is all you have, and there is no long length requested, just a few small ones). It would be a nice feature. However, the problem is, when you check it and type in specific lengths, everything works fine, but then when you UNCHECK it, the specific lengths now exceed the requested length (which is zero). And you have the same problem I was trying to avoid.

I tried applying Data Validation to the cell that the checkbox is connected to, but it won't prevent you from unchecking the check box if the result is that the specific lengths exceed the requested length. I basically need a checkbox (or some good alternative) where I can select one thing or another, but be prevented from doing one of those things if it causes an issue -- such as "the sum of C19:C23 is greater than D10" (the specific thing I want to avoid).

You will see I already have data validation applied to the cell (B17) in question. And it works if I try to type in the cell. But if I use the checkbox to change it, it allows it to go through, and mess things up.

Can someone help me with this? I am trying hard to explain it, but I know this calculator may be too confusing to summarize and explain in such a short text box. I am simply trying to prevent unchecking a checkbox through Data Validation. And if that is impossible, is there any alternative to a checkbox that will function similarly, and also be something that I can place perameters around, where it cannot be flipped back to the other option if it causes "X" to happen?

Thank you so much for your help.

[Keebellah]

Hi, sounds interesting but it's abracadabra for me to see where I enter what value and what checkbox etc.
Just one point, and that's my own thing is that I hate DIV0 errors
Correct these formulas in the MAtrix Array sheet

Please Login or Register  to view this content.

That looks much better then.
If you can show what you fill in where and what it is that happens and what you need ... maybe one of us can help, but in my case I'm

-

[JohnTopley]

Using VBA

Please Login or Register  to view this content.

A17 is set to TRUE/FALSE based on B17

If FALSE i.e Checkbox unticked (having been ticked previously) then C19:c23 are cleared

A17 font is WHITE (so "hidden")

to see VBA: Right click on Tab>>View Code

On testing, I was able to put in data in C19:C23 which far exceeded the length I entered in C7: have I misunderstood the logic ???

[nitro22888]

Sorry, I knew this was going to be quite confusing. Please see my screenshots below. They should help (at least a little bit, I hope).

IMAGE 1:

CalcA.png

IMAGE 2:

CalcB.png

IMAGE 3:

CalcC.png

IMAGE 4:

CalcD.png

IMAGE 5:

CalcE.png


As I said in the last photo, when you uncheck the checkbox after typing in values under "Specific Lengths", the Requested Length total switches from adding up the Specific Lengths to adding up the Feet/Inches in the Requested Length section, and the Specific Lengths now exceed the Requested Length, resulting in some negative values on the sheet.
It seems you can't use Data Validation to prevent a check box from being checked or unchecked, even if you apply it to the cell that the check box is connected to.
I am not an excel expert, so Data Validation is the only thing I can think of to solve this without using Macros (I don't think using Macros is a good idea, given the way this calculator will be used.
If anyone has any ideas -- even if it is NOT using a checkbox at all, but using some other method to switch between these two input options (Requested Length vs Specific Lengths) -- it would be such a huge help for us. This is a really important tool for our design department, and this little hurdle is preventing us from using it fully.

Let me know if you have any questions or thoughts. I am so grateful for your help.

[nitro22888]

Using VBA

A17 is set to TRUE/FALSE based on B17

If FALSE i.e Checkbox unticked (having been ticked previously) then C19:c23 are cleared

A17 font is WHITE (so "hidden")

to see VBA: Right click on Tab>>View Code

On testing, I was able to put in data in C19:C23 which far exceeded the length I entered in C7: have I misunderstood the logic ???

Hey John,

Thanks for that solution. However, I am trying to avoid using Macros (hence why I posted this in the formulas and functions section). Based on how this will be used, it would be unwise to use macros for it. That said, I LOVE that solution. I will keep it in mind, but still keep searching for other alternatives that do not require VBA.

[Keebellah]

I don't know it all can be accomplished using just formula's
I'm a VBA guy myself as many of us here even this section of the Forum is specially dedicated to Functions and Formulas
I've taken the pictures and will see if I get to understand how everything works and if I grasp the idea
Maybe I'll try a macro and then set if back to formulas but don't hold your breath while I'm at it

[JohnTopley]

You can only use VBA to "hard-code" any formula which can always be overwritten. A "checkbox" is no different logically than having "Yes/No" as a drop down for making the choice.

Given the VBA, is the logic correct i.e. unchecking B17 should clear the C19:C23 cells ?

And please remove the pictures as they are of little value ("clutter") and not readable. (although I see fellow contributor has found them useful!!)

[Keebellah]

@JohnTople: The pictures may clutter the reading but they do explain the hole story and show what the OP's intention is.
Yes, the OP could have saved it as 5 separate pictures and attached them but when you attach tem they still show up in post

[nitro22888]

Hey John,

YES, the logic (of clearing the cells when B17 is unchecked) is correct, and that would solve the issue. Like I said, I am trying to avoid VBA, but if there is a way to accomplish that without VBA, that would be an great solution.

When I posted the pictures, I didn't know they would show up smaller than they were originally. Very sorry about that. You can open the photos in a new tab and they will be full size though.

[nitro22888]

Thank you Keebellah.

I am thinking if there isn't a solution for the "checkbox" problem, maybe I can use another method of switching between the two things? I thought about a drop-down where you can select one or another, but the problem I have there is I (as far as I know) can't apply Data Validation to the drop-down, since you have to use Data Validation to create the drop down. But maybe there's some other possibility?

[Keebellah]

As far as I know the greatest limit in Excel besides really impossible combinations is the user's imagination and ingenuity to find solutions.
Like I said, I'll take a look once in a while and see what I can come up with once I understand the whole thing, who knows, using named ranges and vlookups.
Maybe even a pre calculated list with the possible combinations and array formulas to extract the needed combinations.
Almost like macro coding but this with a number of formulas and helper columns

[JohnTopley]

Given the small mount of VBA, and that is "embedded" in the "Calculation" sheet, I do not understand why it is not an acceptable solution.

There is no way that C19:C23 can be cleared using formula so, if using these is a key part of your solution, then VBA is the only option.

[nitro22888]

Hey John.

One issue I see, after playing with your version a bit, is -- even though C19:C23 will clear when it unchecks -- it actually also prevents me from typing anything into C19:C23 when the box remains unchecked. I don't need it to do that. I just need to prevent the sum of C19:C23 from ever exceeding D10.

I tried using macros on this spreadsheet for another issue in the past (which I was able to solve another way), and macros kept crashing at various times. It just wasn't consistent, and I would prefer not going that route.

I am also thinking that there may be a good alternative that I haven't thought of to using a checkbox. Like a drop down to select between 2 options (but I don't know how to apply data validation to a drop down menu). Or maybe something else? I feel like there has to be something. Sorry if it feels like I'm being difficult with the macro thing. I just want to think through everything before going that direction.

[JohnTopley]

In D10

=IF(SUM(C19:C23),SUM(C19:C23),('Multi Run Length Calculator'!D7+('Multi Run Length Calculator'!C7*12)))

This would require C19:C23 to be cleared manually

[JohnTopley]

How can it exceed D10 when D10 is the sum of C19:C23 ????

To me your logic is inconsistent or my understanding of it is wrong.

[nitro22888]

Yeah, I think you are thinking about it incorrectly (which is probably my fault). This is why I posted those 5 photos. They really should make it all more clear.

The only time that D10 equals C19:C23 is if the box is checked. When the checkbox is unchecked, D10 equals "('Multi Run Length Calculator'!D7+('Multi Run Length Calculator'!C7*12))".

The problem is when you check the box, and then include values in C19:C23 (during this time D10 equals the sum of those cells), and while those values are still entered in those cells, you UNCHECK the box. Then D10 goes back to calculating "('Multi Run Length Calculator'!D7+('Multi Run Length Calculator'!C7*12))" -- which is zero usually. So after unchecking the box, C19:C23 exceeds D10. And when that happens, it causes incorrect values in other places. So I am trying to stop that from happening.

Does that make more sense?

[JohnTopley]

If I check B17 then enter data in C19:c23, these are added in D10: if I uncheck , then these values are cleared (VBA solution) and D10 is zero UNLESS you have ALSO entered a value in C7 and/or D7. Then the C7 & D7 values are used in D10.

So after unchecking the box, C19:C23 exceeds D10

How can zero exceed zero ???????

[nitro22888]

You are forgetting why C19:C23 are there in the first place.

They are there so we can enter a LARGE value/length into C7 & D7, and then if we need any specific lengths to be included in the lengths that C7 and D7 create, we can enter them into C19:C23.

So C7, D7, and C19:C23 are all meant to work together originally. But now I am realizing it would be an incredibly helpful option to ONLY calculate specific short lengths (if that's all we have for a specific project) by only using C19:C23. That's what the checkbox is meant to allow, by switching the source that D10 is pulling from.

That probably is confusing, but you can see it visually explained in the photos I posted this morning. I was pretty thorough to show what the purpose was. (see attached image which shows the way C19:C23 was originally meant to work).

I'm now trying to adapt it to multiple uses, but switching between the two is causing issues when you uncheck the box. I hope this helps clarify things.

[JohnTopley]

No it still does "compute" with me so I'll drop out: none of what you say aligns with the formulae you have used. D10 is either C19:C23 OR c7/D& not AND.

[nitro22888]

Cell D10 does not include C19:C23 -- but the formula does not stop there. There are about 5 other sheets that this is all running through, and trust me, C19:C23 and D7/D8 work together.

Just take the original Workbook I attached to this thread and type in a large number into D7, and then type in any quantity you want into C19:C23. You will see those quantities show up in the list of custom cut lengths on the far right, and then anything leftover will be cut up automatically by the calculator.

Of course if you just ignore what I say we will be on totally different pages (no pun intended). And it's your choice to help or not. I appreciate you giving your thoughts though.

[Keebellah]

I'm still around, but it's bed-time here now
Will pick up again sometime during the weekend

[nitro22888]

I'm still around, but it's bed-time here now
Will pick up again sometime during the weekend

Sounds good. Thank you!

[Keebellah]

No promises but it tickles my curiosity to see what can be done without VBA

[JohnTopley]

Cell D10 does not include C19:C23

Formula in D10

=IF(B17=TRUE,SUM(C19:C23),('Multi Run Length Calculator'!D7+('Multi Run Length Calculator'!C7*12)))

Complete contradiction (in my view) !

[nitro22888]

Track the antecedents to C19:C23 on the DATA worksheet, and what happens to those values. They end up being subsumed into the 'Requested Length', and the results under the 'Cut List' end up including those Specific Lengths at the top, and whatever length is leftover from the 'Requested Length' (cell D10) -- minus the total length from C19:C23 -- the calculator will automatically divide up evenly (or by the max run length).

You're just focusing on that one cell. But the calculator ends up combining the two sections further down the line.

The whole point of the checkbox is to isolate them from each other when needed, so that we can ONLY include specific lengths in the results, instead of having to have the Requested Length dictate everything.

I don't mean to drag this on. But it's just more complicated than that one formula in cell D10.

[nitro22888]

Just FYI -- I FOUND A SOLUTION!!!

So my solution is: on the third sheet in the workbook ("Calculation List"), cells H4:H8 are the first cells to pull the values from C19:C23 in order to round them to the nearest cut mark. I added a conditional "IF/AND" formula to each of those 5 cells where if both B17<>TRUE and D10<SUM(C19:C23), then 0. Basically, if you uncheck the box now while C19:C23 exceeds the requested length (D10), NOTHING will happen. The rest of the calculator will just read C19:C23 as being zero, until D10 is greater than or equal to the SUM(C19:C23). I also added a conditional formatting to C19:C23 where if that scenario happens, the text will become light gray, signifying that it is not being included at all (it is considered zero) in thee calculator.

So since without VBA I can't ACTUALLY make it zero out, I can at least make the calculator consider it to be 0 under the circumstances that were causing me problems. And surprisingly this works really well I've attached a copy of the workbook now for you to check out, if you are curious to check it out.

Thanks for all your help. I appreciate you doing what you can do to try and solve this issue.

[Keebellah]

Hi, great feat. Thanks for sharing it.
I'm still trying to get the idea of the idea so that I understand it.
Nice way to solve this without VBA