Excel data validation question.

[willhub]

Hello,

I have a column on a spreadsheet which uses a list from a second sheet and data validation to only allow those values to be used.

What I want to do, is in the list, if a certain list value is used more than twice, I was the spreadsheet to throw an error and decline the use, so only one value can be used twice.

Is this possible?


Cheers.

 

[morbid42]

Many many methods, heres a basic one.

1) add a "helper" column, using excel formula to count the number of occurences of adjacent cell "to the current row"
2) if formula value >1 (basically a form of FLAG), do not process
Extra) Use a macro to populate the formula, convert to values, delete row where value>1, delete helper row.

Try to have a copy of the speedsheet called RAW, uneditted for audit trail, and try to fix the duplicates at the root cause.