• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Custom Validation not working on calculated column

A

ali92

New Member
Hi All,

I have codes in column A and product in column B (to be typed in by users for each row) with monthly utilization in following columns. Idea is to have one row per unique combination of codes and products e.g. code 1234 against product ABC should have only 1 row associated with it and when people fill in data and put in code & product, if this already exist, it should give prompt that this combination already exist.

To get it working I combined code + product in column C as =A2&B2 and put in custom data validation with formula as =Countif(C:C,C2)=1

Validation is not working on this calculated column but if I manually type in same code+product combination then it works.

Helllppphhhh Helllpphhh
 
Ali

Thats is how Data Validation works
It doesn't apply to calculated or pasted values

you need to apply it to the Data Entry columns
using =Countif(A$2:A$10,A2)=1
 
Hi Hui,

Thank you for your kind reply. Issue is, same code could be associated with multiple products and vice versa. This has to be on combination. So is there any other way it could work? e.g with highlighted red cells ... I've managed to put conditional formatting which would highlight if repeated combination .... for empty cells it gives 0 and have additional condition to ignore cells containing 0 (this needs to be in table with a lot of blank rows as many people would be accessing and filling in data on the same file) so is there any other way if we could put a pop up/warning to review entries they've made, if repeated?

Thanks.
 
You must log in or register to reply here.
Back
Top