Forum Discussion

Brass Contributor

Apr 20, 2022

Solved

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: C...

excel

Formulas and Functions

Lorenzo
Apr 20, 2022
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
DataValidation_a_bonsey.xlsx16 KB

a_bonsey

Brass Contributor

Apr 20, 2022

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

a_bonsey

Brass Contributor

Apr 20, 2022

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?

Lorenzo
Silver Contributor
Apr 20, 2022
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
DataValidation_a_bonsey.xlsx16 KB
- a_bonsey
  Brass Contributor
  Apr 20, 2022
  Lorenzo that's brilliant. You're a star
  - Lorenzo
    Silver Contributor
    Apr 20, 2022
    a_bonsey Glad I could help & thanks for providing feedback