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,


    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.

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


    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


    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:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!