"Overwrite" cell if not found in INDEX MATCH

[surkdidat]

Hi all

Hope someone can help!

In Column C (starting in Row 2) I enter a code (This will always start with "POR_" followed by 5 numbers"

In the Mechanics tab I have the codes and the product names (In columns AA:AB)

I have an INDEX MATCH set up, however, new Products/IDs are set up every day, and the form will be sent out weekly, with the updated data in the "Mechanics" tab

However, if someone tries to enter a code not found on the tab, is there a way I can have it set up so it can either be overwritten (when not found only), or a pop up box asking for a value if not found)

I appreciate I could just leave the cell unprotected, and can be overwritten if required, just would like a bit of protection that a correct value cannot be overwritten

Hope that makes sense!

 

[Z51]

You can block invalid entries using Data Validation.

On the Formulas tab, click Define Name
...next to "Name", type something that describes your list in Mechanics column AA, for example: products
...next to "Refers to", type: =OFFSET(Mechanics!$AA$1,0,0,COUNTA(Mechanics!$AA:$AA))
...click OK

On the sheet where you're entering codes:
...select C2:C?? (e.g C2:C100 or C2:C20000, depending on how many rows you think you'll ever need)
...on the Data tab, click Data Validation
......on the Settings tab, under "Allow", select List
......un-check the box next to "In-cell dropdown"
......under "Source", type: =products
......on the Error Alert tab, "Show error alert after invalid data is entered" should be checked. "Style" should be "Stop". In the "Title" field, type something like "Invalid Product ID". In the "Error Message" field, type a useful description, e.g. "You have entered an invalid Product ID. Please retry." or "The Product ID you entered does not exist in column AA on the Mechanics worksheet. Please enter a valid Product ID."
......click OK

Then try entering values into C2 (or C3, C4, etc.). For valid entries, nothing should happen. For entries that aren't found in the "products" list, you should get the pop-up error message.