custom validation formula

Topic

New Reply

Hi All,

I’m trying to create a custom validation formula (Excel 2003). It seems like it should be easy but “easy” isn’t working.

I have a cell S4 where, via dropdown box, I can set a reason for closing; the list for the dropdown is stored in a range and has the values nothing (an empty cell), “snow”, etc. All reason codes, other than nothing, are at least 3 characters in length.

In cell C4, I want to allow entries as follows:
– if S4 is not nothing, don’t allow any entries
– if S4 is nothing, allow either “out” or a positive number

For my custom validation rule, I have:
=AND(LEN(S4)0)))

With this, anything seems to be allowed when LEN(S4)=3 (first dash above), nothing is allowed (this is OK).

If I delete the test on S4 (not really the end of the world), it works fine in allowing only “out” or a positive number.

What am I missing?

TIA

Fred

Reply | Quote

Replies

Not sure I fully understand, but suppose you changed LEN(S4) to ISBLANK(S4) ??

Reply | Quote

Fred,

This formula works according to your specs.
[noparse]=AND(ISBLANK(S4),OR(C5=”out”,C5>0))[/noparse]

Note: If you change S4 after entering C4 it will NOT re-validate!
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks guys but I’m not sure I appreciate the difference. I checked the length of S4 when there was nothing in there (just LEN(S4) in a separate cell) and got 0.

Would ISBLANK even work when the length really is 0?

But why would ISBLANK(S4) work and not LEN(S4)0 w/o the ISNUMBER test but that wasn’t working either.

RG: Understand the part about C5 not re-validating if S4 changed after it. The logic of the spreadsheet is that you’d enter S4 first. If you’ve made an entry, you would not bother entering anything for C5 anyway. S4 is a reason for the entire school being closed on a given day, so it affects all teachers. C5 is an individual teacher, who may have been “out” or worked some # of hours that day but the school was open. There’s also a D5, E5, etc for other teachers.

Fred

Reply | Quote

Fred,

Actually, this does work
[noparse]=AND((LEN($S$4)0)))[/noparse]

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..if you put “banana” in cell $C$5 and then, in another cell, put the formula =$C$5>0 then this formula will give a result of True. So the (OR($C$5=”out”,$C$5>0)) will also give True

zeddy

Reply | Quote

Hi fred

I don’t think using a custom validation formula is the right approach for what you are doing.
But, nevertheless, to give you what you ask for, see attached file.

This allows you to select ‘Closing Reason’ from a dropdown list in cell S4.
In cell C4, you can then select from another dropdown:
> if S4 is not nothing, you can only ‘select’ a blank entry.
> if S4 is nothing, you can select either “out” or a positive number (I made a list of 1 to 12 as an example)

Note, as per RG’s comment, if you change S4 after entering C4, if the value in C4 is now ‘invalid’, it will then be ‘circled’.
(This uses the worksheet change event to CircleInvalid)

My example file uses adjacent formulas in columns T, U to determine which dropdown is allowed for the entry in C4. The data validation for C4 then uses an ‘indirect’ formula to give the allowed entries from the allowed list.

If, as I suspect, you might require further entries below C5 and S4, you might prefer to just use event-trapping to control the allowed entries in column C

zeddy

Reply | Quote

I don’t think using a custom validation formula is the right approach

What do you suggest instead?

cheers, Paul

Reply | Quote

you might prefer to just use event-trapping to control the allowed entries in column C

zeddy

Reply | Quote

Sorry, not looking properly.

cheers, Paul

Reply | Quote

Hey Y’all,

This is crazy! If a cell contains text it should have a value of zero but obviously this isn’t always what Excel calculates it as!

For instance: [noparse]=(c5+1)>0[/noparse] returns #VALUE! when c4 contains “banana”.
However: [noparse]=Sum(c5)>0[/noparse] returns FALSE

It gets crazier!

This formula: [noparse]=AND(ISBLANK(S4),OR(C5=”out”,(SUM(C5)>0)))[/noparse] returns the correct value if placed in a cell and the cells (S4 – C5 – C4) are manipulated.

However, if the formula is placed in the Data Valadation it does not work! But, if you just reference in the Data Validation, e.g. the formula is in F4 and the Data Validation formula is set to [noparse]=F4[/noparse] the data validation works correctly, go figure? :confused:

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

SUM ignores text, whereas operators (+ – / *) don’t, hence you get 0 for SUM(“some text”) and an error if you try and use a mathematical operator.

When using a formula in DV you really need to uncheck the Ignore Blank option.

Reply | Quote

Rory,

Who knew!

I rarely use DV as I usually use VBA for checking. That sure fixed the problem and now the formula works directly in DV.

Can you elaborate more on why this is so? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Who knew

Me?

Can you elaborate more on why this is so?

Not really – I think it’s a bug but I confess I’ve not bothered to report it as the workaround is so trivial, and there may conceivably be a reason for it.

Reply | Quote

Hi RG

..you could use
N(C5)>0
instead of..
SUM(C5)>0

So N(C5)>0 could replace AND(ISNUMBER(C5),C5>0)

zeddy

Reply | Quote

Sorry guys that I haven’t been able to look at the posts until now since I’ve been busy grading papers.

And there’s the thing I forgot to mention – this is for my die-hard MAC person. Really sorry. So no VBA.

I’ll have to take a look at the ignore blank checkbox. But won’t be able to get to it soon bcs more papers to grade; now that it’s done, I have 2 weeks until the next cycle when this is needed.

But I’ll try to get to this before then.

Fred

Reply | Quote

Oops – so busy: didn’t even notice that I pretty much said this in my last post!

Finally a chance to read thru the comments.

Thanks to all who commented.

The work around being?
– unchecking the Ignore Blank option? not sure why this will work but I’ll give it a shot

– do a custom VBA routine?

Did I mention this was for my friend on a MAC with no VBA?

Fred

Reply | Quote