# select by data validation list to highlight the cells

• Hi,

i am creating a leave planner sheet i have a confuse to enter formula can u pls solve me....below 3 points.

1) i have a data validation list ( choice of 3) in column "E" if select "AL" to highlight has green , "IL" to highlight has blue , "Sl" to highlight has brown.

2) in Holidays sheet i have yearly holiday list once i date enter to highlight the color has gray in leave calender in "yearly planner" sheet.

3) in column "A" data validation by selecting grade if above 10 grade i select the holidays list to add 1 to in-leu days in column "AU8".

i posted in other forums but still i didnt get any response.... as any one can solve this .....

## Files

• Re: select by data validation list to highlight the cells

Hi,

Your question is really vague. You need to explain a little better what you expect. As example:

Quote

"IL" to highlight has blue

what cells do you need to turn up blue? Is it just column E or ......

• Re: select by data validation list to highlight the cells

hi joris,

thanks for the reply... i updated my sheet has little more to understand the problems u try to enter the dates in column "c & d" and choose the leave type in column "E" LOOK only in January month the leave type is not chaining to what i choose in column "E" with multiple entries of employee in same month.

ok actually if i enter the INPUT selection in column "A ,B,C,D,E" once i enter the date IN COLUMN "C & D" then the calender in January month it will update the leave type has a color what i choose in column "E" ( the color i explained in column "AR16:AS18") eg:- if i select 1) it represents has annual leave 4) represents has in-lue 5) represents has sick leave.

i enter the formula in month of january calender month has =SUMPRODUCT((\$G7=SNames)*(SFrom<=H\$5)*(STo>=H\$5))*\$F7

but problem has if i select the employee 01 he taking has two type leave in January has a (from 2-jan-17 to 13-jan-17 has in-lue) and (from 15-jan -2017 to 22-jan-17 has annual leave) but showing color has blue instead of green color.

i need to correct the formula in "= sumproduct " if i enter the choose the leave type in column "E" i need has same color in the January month.

## Files

• Re: select by data validation list to highlight the cells

The problem is caused by the multiplication with \$F7. You need to change it with an INDEX/MATCH combination which picks the correct leave type from the list.
See attached.

Elaborate on question 2 and 3 in the same way as you did now so i can work on them.

## Files

• Re: select by data validation list to highlight the cells

hi joris,

excellent job you done its working really you made my day.

• Re: select by data validation list to highlight the cells

No problem, glad I could help.
please remember to mark the issues as solved on BOTH forums.
and if you were pleased with my help, click on the star at the left bottom to give me some rep-points.

• Re: select by data validation list to highlight the cells

hi joris,
pls can u solve me the 2 and 3 question also it would appreciate grateful to you....

• Re: select by data validation list to highlight the cells

Hi,

Fixed question 2. see attached.

Can you explain what you want with question 3, i don't understand what you're trying to do.

## Files

• Re: select by data validation list to highlight the cells

hi joris,

thank you so much for ur effort the 3.question is

in column "A" if we select the grade of employees if above 10 grade eg:- 11,12,13,14,15 those are eligible for in-lue days so how many holidays are declared in holiday sheet they can get those days additional leave it will add on column "Au" from 8:11 rows in in-lue balance column.
less than 10 they are not eligible for in-lue days.

i think it clear to you joris ... once this one is solved i am will be very happy.

• Re: select by data validation list to highlight the cells

Almost, you already ahve a count of the number of In_leu days in column AP. Is it just this number checked against the eligability based on the selection in column A?

• Re: select by data validation list to highlight the cells

The column in "AP" that counts the number of in-lue days taken by employee but its right the eligibility above >10 only can apply the in-lue days based on column A.

• Re: select by data validation list to highlight the cells

If column AP holds the correct number of days but isn't taking the eligibility into account, the easiest solution is to restrict somebody with a 10 grade or lower (who aren't eligable) to select the leave type.
So i restricted the input of the leave types.

See if this helps.

## Files

• Re: select by data validation list to highlight the cells

hi joris,
you are right if restricted the input of leave types they can't select the in-lue types but in sheet where u restricted this input because i tried in 10 grade i select then still i can select the input of leave types.. restriction is not working. may be attachment file is before one i think pls check.

• Re: select by data validation list to highlight the cells

The data validation rules weren't completely copied down. Corrected it in this version.

## Files

• Re: select by data validation list to highlight the cells

hi joris,
excellent its working 100% perfect thank a lot i will make post on both site i became a fan of you...:idea:

• Re: select by data validation list to highlight the cells