I have to use VLOOKUP to make a list of 5 possible components for a product. Users can choose from 1 to 4 of them. My question is how to show an alert message when they choose the same product twice?
2006-11-25 23:04:50 · 2 answers · asked by hado_hl3130 2 in Computers & Internet ➔ Software
Let's suppose that the fields in which the user will imput the components codes are B8:B12 and we don't want to have the same component twice in those cells. For this constraint we will use data validation feature, as follows: select the cell B8 an go to menu'data>validation...'. Select 'custom' into allow drop-down and type the following formula: =NOT(COUNTIF($B$8:$B$12,B8)=2). Go to error alert tab and type whatever error you want to appear in the messagebox then hit ok. To apply the same behaviour to the cells b9 through b12 just copy the cell b8 and paste it over them.Note that if the cells are updated by a macro(not by directly typing into those cells), then the restriction will not apply. Also a copy/paste over those cells will delete the validation rule
2006-11-25 23:57:23 · answer #1 · answered by Mihai L 2 · 1⤊ 0⤋
Mihai L has the correct answer
Enjoy my profile, I am the VBAXLMan
2006-11-27 02:25:52 · answer #2 · answered by Anonymous · 0⤊ 0⤋