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 .....

http://www.excelforum.com/show…28&highlight=#post4574828

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.

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

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 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

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

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

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