Use Data Validation to ensure the product code of "Sold Items" is not entered again

I have a excel file by which I make my invoices.

Here are the steps which I follow:

* In sheet "Product" I enter the product code and the item description.
*I save the excel file and close it.
*When customer want to buy a product, then I open sheet "Invoice", enter the product code in range "B11:B31" , then I enter the rate at which I want to sell the product in range "Q11:Q31"
*Then I press "Update Button" - It copies data from my "invoice" sheet to " Invoice data" sheet and saves the excel file.
*If the customer pays the amount then I click on "Paid" button and if its outstanding then I press "Due" button. These 2 button "save as" the excel file to a specified location.

So even if the product is sold , I can still enter the same product code in sheet "invoice" and make invoices. Practically the product is sold.

So through "Data Validation" I was looking for something in which If the product is sold then sheet "Invoice" Range "b11:b31" does not allow the user to enter the "product code" which is sold.

Sheet "Invoice data" is the sheet where sales is recorded. In sheet "Invoice data" range "F" contains all the codes of products which are sold.


I have attached the sample file for simplification.

Please help me if possible.

Thank You.

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

Thank you - that is fine now.

This proposed solution employs a new column, 'Available', on the 'Products' sheet. The 'Available' column is populated by the array entered formula*:

Formula:

Please Login or Register  to view this content.

A named range, again called 'Available', is then added and given a 'Refers To' of:

Formula:

Please Login or Register  to view this content.

Data validation is then applied to the range B11:B31, on the Invoice sheet, with the source being =Available
Note: Ranges for the formulas are for demonstration purposes and may be adjusted as needed.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

Could you provide details of an occasion where you get that popup.
I only get that popup if I type in an item code that has already been used or is not on the 'Product' sheet, which is what I expect. As long as I pick items from the drop down list I don't get the popup.
Let us know if you have any questions.

To apply data validation without the drop downs use the following formula for custom data validation applied to the range B11:B31 on the 'Invoice' sheet:

Formula:

Please Login or Register  to view this content.

This will allow the item number to be typed into one of the cells in the range B11:B31 on the 'Invoice' Sheet, as modeled in the attached file.
After looking at the layout a bit further it may be that you were actually wanting to type the item numbers into the 'Invoice Data' sheet.
If the latter is the case then the following formula would need to used as custom data validation and applied to column F on the 'Invoice Data' sheet:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

When I checked using the file attached to post #10 I was able to type "test1" in cell B12, however as soon as I tried to complete the action by either pressing the Enter key, down arrow key or using the mouse to select a different cell the pop up warning appeared. That is the way that Data Validation is expected to work.
If you are getting different results then it might help if you would reattach a copy of the file showing the problem.
Let us know if you have any questions.

I am not able to replicate your problem. I typed "test1" in both cells B13 and B31 and got the pop up warning both times. I also typed "awe" in both cells and got the same resulting pop up.
Perhaps this is more of a VBA issue than a formula issue. I'll ask some of the VBA side contributors to take a look.

Like JeteMc, it works find for me, so I can't replicate the issue, but I don't think the issue is VBA. I suspect that the problem is Excel 2007. I've never used Excel 2007, but the internet tells me that Excel 2007 does not respond well to referencing other worksheets in your Data Validation list formula. This problem was addressed in Excel 2010. If my guess is accurate, I would suggest returning to JeteMc's suggestion of creating a named range called "Available" and using that as the range reference in your Data Validation formula. In your data validation rule for INVOICE column B, replace the reference to PRODUCT with a reference to Available, so your rule ends up being:

=OR(B11="",AND(COUNTIFS(Available,B11),COUNTIFS(B$11:B11,B11)=1))

Give that a try on your Excel 2007 and see if it works as desired.



NOTE: In the attachment, I recreated the "Available" list in column T of PRODUCT using the following array-entered formula in T4, filled down:

=INDEX(A$3:A$484,SMALL(IF(COUNTIF(INVOICE!$B$11:$B$31,A$3:A$484)=0,ROW($3:$484)-2),ROW(1:1)))

I used this formula instead of JeteMc's formula simply because it allowed me to check changes without needing to use your buttons to update column F of "Invoice Data" and subsequently column O on PRODUCT. The "Available" list in my attachment should include everything in PRODUCT column A that's not listed on INVOICE column B. If JeteMc's version works better for what you're doing, stick with that.