Data Validation for previous rows

[a_bonsey]

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

[#2]

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

[#3]

Thanks for that. I'll take a look.
We're currenthly validating LEN>0 if that helps

[#4]

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?

[#5 · Accepted solution]

@a_bonsey 

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

[#6]

@L z.  that's brilliant. You're a star 

[#7]

@a_bonsey Glad I could help & thanks for providing feedback