Forum Discussion
Data Validation for previous rows
Hi All,
I'm trying to avoid using VBA as this will cause issues when sharing with clients so looking for a solution (if possible) using data validation.
I have users entering data as follows:
Cell A = Name
Cell C,F & H = required
What I'm looking to do is validate that a previous row has had ALL that data entered based on Cell A being the trigger point. This I've managed to get to work.
The extra bit is that there may be blank rows (single or multiple) so the validation required is checking All previous rows are correctly populated IF Cell A is valid.
Can that be done? TIA
Re. We're currenthly validating LEN>0 if that helps
If the SUMPRODUCT formula previously shared does what you want with regard to columns C,F,H no update is requiredCan that code also include a check that columns B-H cannot be populated unless A is also populated?
A different Data Validation rule must apply to columns B-H then:
With the above example
- Select B2:H16
- Data Validation rule Custom: =$A2 <> "" AND uncheck option Ignore blank:
Sample updated and attached
6 Replies
Hi a_bonsey
Not sure this is exactly what you want as we have no idea how you currently validate entries in column A...
Data Validation applied to A2:A16:
=SUMPRODUCT(--(ROW(A$1:A1)<ROW()),--(A$1:A1<>""), --(C$1:C1<>""), --(F$1:F1<>""), --(H$1:H1<>"")) = COUNTA(A$1:A1)In the above example nothing can be enterred in A10, A11,...A16 as row 9 is incomplete (H9 not filled). Corresponding sample attached
