Apr 20 2022 03:15 AM
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
Apr 20 2022 05:47 AM
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
Apr 20 2022 05:49 AM
Apr 20 2022 06:53 AM - edited Apr 20 2022 06:55 AM
Sorry, all a bit new to this.
Can that code also include a check that columns B-H cannot be populated unless A is also populated?
Apr 20 2022 07:18 AM
Solution
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 required
Can 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
Apr 20 2022 09:20 AM
Apr 20 2022 07:18 AM
Solution
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 required
Can 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