Custom Data Validation with multiple column

Faysalalam2008 · Oct 27th 2013

[TABLE="width: 896"]

[tr]

[TD="colspan: 4"]I m trying to validate data in Column H.
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 14"]when I put code in column G and amount In column H, it should not exceed amount 100000 because A2 and B2 validate code and amount[/TD]

[/tr]

[tr]

[TD="colspan: 3"]reference code 111[/TD]

[td][/td]

[/tr]

[/TABLE]

[TABLE="width: 64"]

[tr]

[/tr]

[tr]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]

Smallman · Oct 27th 2013

Re: Custom Data Validation with multiple column

Faysalalam

Don't use whole columns when you are using Sumproduct. Is is not the most efficeint of formulas at the best of times without using the whole Column.

For example Condition 1 should look like this;

=SUMPRODUCT((G2:G17=G2)*(H2:H17<B2))

Once you start addressing this sort of issue the rest should come more easily. It is late here so I won't be able to follow this threat through till the morning. I am sure others can fly the flag in the mean time though.

Take care

Smallman

Faysalalam2008 · Oct 30th 2013

Re: Custom Data Validation with multiple column

Thanks,
but it did not work, all the condition in excel is ok but it does not work in data validation,
condition 4 does OK but i want a cell reference instead of "<150000"

Custom Data Validation with multiple column

Participate now!

Share