Notification required if sum exceeds.

Question

Dear Excel Genius

In the attached excel worksheet link, I have PO ENTRY and SALES ENTRY Worksheets.

https://1drv.ms/x/s!AiSRNak3QYCfg4xHc2wLFmRVSfvpVQ?e=flwZUe

PO Entry sheet has PO Number and it is quantity. The sales Entry sheet has the PO number and the sales quantity.

I need a pop-up error alert when the sales entry quantity exceeds the PO quantity.

For example,

PO Number ST07/1697 has 100kg of quantity, But the sales 125kg. The sales exceed the PO. So it has to notify the user that this sale exceeds the respective PO Number.

As with ST09/1670, PO Qty is 1000kg but the sales are 1100kg.

I need some notification or pop-up alert when the user enters the sales more than the PO.

I am expecting your valuable reply.

Answer 1

Hi @Anbuselvam Kovilmani ,
I edited your sample file and here the formula in Data Validation:

=SUMPRODUCT(($H$7:H7=H7)*($N$7:N7))<=VLOOKUP(H7,'PO ENTRY'!A:B,2,FALSE)  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Anbuselvam Kovilmani ,
Based on your description, I created a sample about custom Data Validation in Excel:

Select the C1:C3- go to Data- Data Validation- under Allow list select Custom and enter the formula:

=C1<=A1  

Enter the Title and Error message under the Error Alert:

Try this method and check if this will help you.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

@Emi Zhang-MSFT

Thanks a lot for your reply.

As described by you, the target cell and the sourced cell should be in one number. I meant only one cell of both.

But in my case, PO Entry has one cell value and the sales entry cell may be 2 or 3 or 5 which We don't know.

So the sales entry cells need to find by the PO numbers ST07, ST09, and ST06. It is not the fixed cells.

Answer 4

Hi @Anbuselvam Kovilmani ,
I understanding the meaning of your requirement, but how can I know the limited of quantity for each PO Number?
I created a sample, if the limited of quantity for each PO Number is 1000, it's the fixed value.
Select Range N2:N26- go to Data- Data Validation- under Allow list select Custom and enter the formula:

=SUMPRODUCT(($H$2:H2=H2)*($N$2:N2))<=1000  

Hope it's helpful.

Answer 5

Thanks @Emi Zhang-MSFT

Please see the below image which is showing the PO ENTRY Sheet and the PO NUMBER & QUANTITY.

One PO number has only one quantity. it won't change any further.

So how can I replace 1000 with another sheet PO number and quantity in the below formulation?

=SUMPRODUCT(($H$2:H2=H2)*($N$2:N2))<=1000

Your valuable reply will be appreciated.