Custom data validation

[leopardhawk]

Is it possible to have the data validation in a cell NOT permit any entry at all unless there is a valid date in another cell? I tried this...

=if(H9,0,0)

...but it still allows data to be entered in the cell I am trying to validate.

Cheers!

 

[Peter_SSs]

Re: Need help with custom data validation

Just a word of caution. IsDate() has some quirks.
I'm not sure how the 'date cell' is formatted, but if you enter, say, 0.1 into that 'date cell' you will probably see either 0.1 or you might see 0/01/1900 or similar. In any case I doubt you would consider either of those as a 'valid date' yet I think you will find that the Data Validation suggested will allow an entry into the DV cell.

 

[leopardhawk]

Re: Need help with custom data validation

I think I spoke too soon. Two separate issues have cropped up and I am hoping that you can help.

1. I need a way to copy the cell that contains the validation and drag it down the column (which contains 500 rows) but without the $$$, I can't figure out how to keep the =DATEVER(personal_info!H9) from changing to H10, H11, H12 etc... If I had to do 500 rows individually, it would be extremely painful.

2. Am I right to suggest that the ISDATE in Name Manager can only be used once on a worksheet? I have another column (P) on the same worksheet that needs to reference 'personal_info!H11' and when I selected the cell in column P and changed the 'Refers to' to =DATEVER(personal_info!H11), it changed the 'Refers to' in my first cell to =DATEVER(personal_info!XFD9)...! It looks like it won't allow me to have two separate cells references using the one ISDATE. Is this correct and if it is, is there a workaround?

Thanks!

 

[leopardhawk]

Thanks Peter, the cell with the actual date will only accept a valid date so I hope that that solves that problem. My other issues as defined in my previous post are much more of a concern I think.

Cheers!

 

[leopardhawk]

As I continue to try and find a solution to this issue, it occurs to me that my original post "Is it possible to have the data validation in a cell NOT permit any entry at all unless there is a valid date in another cell?" mentions 'valid date'. What if I change that to 'anything at all'...? In other words, is it possible to have a cell NOT permit anything to be entered unless there is something besides a blank in another cell? Would this be an easier issue to resolve?

Thanks!

 

[Peter_SSs]

Thanks Peter, the cell with the actual date will only accept a valid date so I hope that that solves that problem.

It certainly does and you are on the right track with ...

As I continue to try and find a solution to this issue, it occurs to me that my original post "Is it possible to have the data validation in a cell NOT permit any entry at all unless there is a valid date in another cell?" mentions 'valid date'. What if I change that to 'anything at all'...? In other words, is it possible to have a cell NOT permit anything to be entered unless there is something besides a blank in another cell? Would this be an easier issue to resolve?

If the Date cell is H9, then your Custom DV formula in the other cell could be simply =H9<>"" and remove the tick from 'Ignore blank'
If you want to copy that DV down and keep referring to H9 (that is only allow values in any cell below if H9 has a date) then the DV formula would be =H$9<>""

 

[leopardhawk]

Peter, fantastic! So much easier, I feel bad for having folks suggesting the ISDATE etc. It works perfectly. Anyway, thanks much and all the best!

Cheers!

 

[Peter_SSs]

Peter, fantastic! So much easier, I feel bad for having folks suggesting the ISDATE etc. It works perfectly. Anyway, thanks much and all the best!

Cheers!

You're welcome. Certainly took the long way around but the main things is success at the end.