1

I have a Google Sheet in which users key in records for a specific month. A report on another tab looks up the month and consolidates it. I'm trying to enforce using the first day of the month to match the header in the report sheet. (I know I could replace the lookup with a sumif range, but I'm trying to keep the formulas as short as possible and the amount of calculation to a minimum, since this ends up being a large document)

I've set a Data Validation rule to enforce this rule, but I just looked and found numerous instances of violations, which make the report inaccurate. The rule is: =eomonth([cell],-1)+1

Mousing over the cell shows me the validation text, but the rule is set to reject the value completely! What is going on wrong here?

enter image description here

Improve this question
  • It's hard to say what's going wrong without the spreadsheet to look at, but perhaps enforcing a particular date (1st) as a proxy for the whole month isn't the optimal strategy, as evidenced by the fact that users apparently can't be bothered to enter data correctly. Maybe try a simple list of possible months and enforce that, then decode in a formula. Also even if the document is large, if you design your SUMIF's correctly, there shouldn't be so much impact on performance. – ttarchala May 9 '17 at 6:22
1

Custom formula type of data validation is evaluated independently of the cell. In other words, I was assuming that it was checking if the value returned by the expression was equal to the current cell. In reality, it was checking to see if =eomonth(A169,-1)+1 equaled "true" (or -1, I suppose).

There are 2 potential solutions here.

  1. Maintain "Custom formula" as the type, but change the formula to: =A169=eomonth(A169,-1)+1

  2. Change the validation type to "Date" and set it to Date equal to: =eomonth(A168,-1)+1

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.