Data validation forula for date value to be greater than other cell OR specific text

**ehirst** · 07-25-2014, 06:32 AM

Hi there,

I'm trying to create a DV rule that will allow a cell to take a value that is either a date greater than the preceding cell OR a specific text string. (the word 'Trainer')

I'm currently toying with this formula =OR(K112>J112,K112="Trainer") but it doesn't prevent me from entering words other than 'Trainer'. Any help on how to resolve this would be greatly appreciated.

Thanks!

**Special-K** · 07-25-2014, 07:07 AM

I think the formula is resulting in TRUE if K112 contains ANY text, in which case it could well be greater than J112.
e.g.
J112 = blank
K112 = "ABC"

K112 IS greater than J112 which is not what you want.

Am still playing with this...

**Special-K** · 07-25-2014, 07:10 AM

Try this

=OR(AND(ISNUMBER(J112),ISNUMBER(K112),K112>J112),K112="Trainer")

The greater than condition now only gets executed if J112 and K112 are both numeric.

**gak67** · 07-25-2014, 07:10 AM

Try this:

Formula:

Please Login or Register  to view this content.

**ehirst** · 07-25-2014, 07:35 AM

Brilliant, both solutions worked for this (although I can see the advantage of requiring both J and K cells to be values) so thank you both very much for the help!

**ehirst** · 07-25-2014, 07:53 AM

Apologies, I didn't test properly - still not working properly as now can't enter anything in K cells without an error. I have attached a redacted version of the sheet in question in case this is of any help - if you have any more suggestions this would be fantastic.

Cell references tat I previously mentioned are accurate and all forumlae visible. Hopefully you will see what I'm trying to achieve!

thanks again

Training Matrix Sandbox Version.xlsm

**gak67** · 07-25-2014, 03:21 PM

I can see a couple of issues. Firstly, the data validation formula in cell K17 references row 127, not 17. Secondly, you initially asked to validate that column K was greater than column J, but your data shows them as equal in every instance. If that is valid data you would need to change the data validation formula in K2 to this:

Formula:

Please Login or Register  to view this content.

I used Special-K's formula as you said you preferred it.

**dwyeadon** · 10-02-2017, 12:09 PM

Thank you for the help.

Data validation forula for date value to be greater than other cell OR specific text

LinkBack

Thread Tools

Rate This Thread

Display

Data validation forula for date value to be greater than other cell OR specific text

Re: Data validation forula for date value to be greater than other cell OR specific text

Re: Data validation forula for date value to be greater than other cell OR specific text

Re: Data validation forula for date value to be greater than other cell OR specific text

Re: Data validation forula for date value to be greater than other cell OR specific text

Re: Data validation forula for date value to be greater than other cell OR specific text

Re: Data validation forula for date value to be greater than other cell OR specific text

Re: Data validation forula for date value to be greater than other cell OR specific text

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to highlight cell/data greater than a specific date

[SOLVED] Data validation based on another cell containing specific text

Count cell A1:A20 (Specific words) only if cell C1:C20 is greater than 1/1/2013 (Date)

Excel 2007 : Forula for If then/ date calculation

[SOLVED] Calculate # of Occurrences In Cell Range Greater Than Specific Date

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions