Macro needed to show popup box if data entered matches data in another sheet

[dixon1983]

Hi,
I have a spreadsheet which I add data to each day.
A seperate worksheet in this spreadsheet contains archived data.

I would like an error message to appear if I try to enter any data into column A in the master data sheet which matches data in Column A of the Archive sheet.

Many many thanks for anyone who can help me with this.

Regards,
Dixon

 

[Jon von der Heyden]

Hello Dixon

You could use Data Validation but you need to name the range on the archive sheet.

On the archive sheet highlight column A. In the 'Name Box' (left of formula bar) enter Archive (this will be the name for the range).

Now return to your entry sheet. Let us assume you are entering data in colum A starting from row 2.

Highlight A2 thru to A100 (lets assume that your data will not exceed row 100).
On the menu bar go Data > Validation.
Choose custom from the 'Allow:' drop down
Input this as the formula:
=ISNA(MATCH(A2,Archive,0))

Now go to the 'Error Alerrt' tab and enter your custom pop up message. You can also play with the style so that the message appears and does not allow a user to enter a value that already exists in the archive, or simply have it pop up as a warning.

Click Ok and it's done!

Amend ranges to suit.

Hope this helps.