Data Validation or Macro to prevent Duplicate up to Vacation days

[hajiali]

In sheet "Employee List" I have employee numbers and first and last name followed by number of Vacation each employee has. Ex

EMPLOYEE NUMBER	FIRST NAME	LAST NAME	VACATION DAYS
156631	TOM	SMITH	15
54263	SARA	PARKER	12
254264	PETER	HAYES	3
21458	KIM	CLARK	6
122354	MATT	WADE	10
32455	LIZ	MORRIS	20
35663	JOHN	MILLER	18
54531	JENNY	PARKER	17
325486	JOE	PHILLIPS	20

On my Vacation sheet I have days for the whole year in column A to the right from column B-L of that I will enter the Employee number to assign them a vacation day. What I need is Data Validation or Macro that will prevent me from Entering the Employee number more than Vacation days. and if I do, Message me with "The employee exceed the number of Vacation days".

Ex. Using the table above the Sheet it should only allow me to enter Peters Employee number in Vacation sheet 3 times, Matt employee number 10 times, JOE employee number 20 times in so on, in the range B2-L636

any suggestion is greatly appreciated.

 

[lrobbo314]

First I turned the range into a table, not sure if you did that step already or not.

Then I created a dynamic range for your employee number column by going into name manager and entering the following formula. I named the range 'emp'.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

Then, select all of the employee numbers, not the header row, add data validation, select custom, then add the following formula.

=COUNTIF(Emp,A1)=1

Should do the trick.

 

[hajiali]

Thanks Irobbo314 I just tired it on a new workbook and still didnt prevent me from entering the employee number more than the Vacation Days. How does it refer to the Vacation days column? Which Table do i Convert to a Table the range with employee numbers, name, and vacation days, or the Vacation sheet or both?

 

[lrobbo314]

Sorry, I misunderstood the question. I'll post back later.

 

[lrobbo314]

So, you can do the same thing with setting up the dynamic ranges if you want. In this example I'm just putting in the formula you will need to add to the custom data validation formula. Hopefully I am understanding it correctly this time. The table on the right illustrates where you are assigning the days off, and the column 'DV formula' has the formula that you will plug into the data validation.

Also, this is all on 1 page for simplicity sake. You might need to adjust the formula to match your sheet names.

Book1
	B	C	D	E	F	G	H	I	J
1	EMPLOYEE NUMBER	FIRST NAME	LAST NAME	VACATION DAYS			Eployee	Date	DV Formula
2	156631	TOM	SMITH	15			254264	11/24/2019	True
3	54263	SARA	PARKER	12			254264	11/25/2019	True
4	254264	PETER	HAYES	3			254264	11/26/2019	True
5	21458	KIM	CLARK	6			254264	11/27/2019	False
6	122354	MATT	WADE	10
7	32455	LIZ	MORRIS	20
8	35663	JOHN	MILLER	18
9	54531	JENNY	PARKER	17
10	325486	JOE	PHILLIPS	20
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=COUNTIF($H$2:H2,H2)<=INDEX(Sheet1!$E$2:$E$10,MATCH(H2,Sheet1!$B$2:$B$10,0))

 

[hajiali]

Hey Irobbo314 Thank you for your help. Just had to alter the Formula to work with my sheets and range. Works great!!! thanks

 

[lrobbo314]

Awesome! Glad it worked out.