1

I have an Excel spreadsheet that contains two sheets.Suppose the names are sheet1 and sheet2.

There are several data in column A in sheet1 and several data in column A in sheet2.

How can I restrict users when they enter data to avoid to enter duplicates of data in both sheets that is already available in column A sheet1 and column A sheet2?

That means new entries must be unique and they cannot match with anything on column A of sheet1 and column A of sheet2.

1 Answer 1

3

You need to use "Data Validation" for that:

  • go to Data - Data Validation
  • In "Allow" field select "custom"
  • Enter formula:
    =(COUNTIF(Sheet1!A:A,<cell>)+COUNTIF(Sheet2!A:A,<cell>))=1
    where <Cell> is the adress of the cell you're entering the validation (e.g. if whole column A is selected in Sheet1, then just enter A1)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .