Data validation with respect to data in other column

[nagesh.tvsr]

Hi,

Need data validation logic / formula as per the excel sheet attached.

Pl. help.

Regards,
Nagesh.

[Richard Buttrey]

Without a UDF or VBA then

With the following formula in a helper column - say Q2 copied down

Please Login or Register  to view this content.

The Data validation formula in P2 is

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Formula for DV in P2:P8 (no helper column)

Please Login or Register  to view this content.

[nagesh.tvsr]

Thanks a lot @Srinivasa Murthy it's working well.
Thanks Richard Buttrey.

[nagesh.tvsr]

Noticed an issue.

If the data entered (Bill Qty.) in two different rows, it is accepting more than the required qty.

ex: If the Req. Qty. is 6 and if the data entered 3 in one row and 4 in one row, it is accepting.

Pl. help to fix this.

Thanks,
Nagesh.

[Richard Buttrey]

Please clarify and upload a workbook pointing out where the problem occurs so that we can understand the point you're making

[nagesh.tvsr]

Hi Richard Buttrey,

Thanks for the response.

An excel attached with necessary details.

Pl. help.

Regards,
Nagesh.

[Glenn Kennedy]

This formula will check ONLY the pat number (not job no, or build or Ph). do any of those need to be included? If so, which ones?

=SUMIFS($P:$P,$N:$N,N2)<=SUMIFS($F:$F,$A:$A,O2,$E:$E,N2)

[nagesh.tvsr]

Hi Glenn,

Seems this is working well. Will in different ways and will let you know if i stuck anywhere.

Thank you very much.

Nagesh.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.