Re: select by data validation list to highlight the cells
glad i could help.
Posts by Joris Moerings
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
Re: select by data validation list to highlight the cells
The data validation rules weren't completely copied down. Corrected it in this version.
-
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.
-
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
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.
-
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
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.
-
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: Count Unique values with multiple criteria
Hi,
No you should add it as a separate IF function: =SUM(If(FREQUENCY(If(ISNUMBER(SEARCH("Student",C2:C9,1)),If(Not(ISNUMBER(SEARCH("Memphis",B2:B9,1))),If(Not(ISNUMBER(SEARCH("Denver",B2:B9,1))),If($A$2:$A$9<>"",MATCH($A$2:$A$9,$A$2:$A$9,0))))),ROW($A$2:$A$9)-ROW($A$2)+1),1))
-
Re: Count Unique values with multiple criteria
Hi,
Take alook at this and see if this is what you're after:
Excel 2016 (Windows) 64 bit
[TABLE="class: head"]
[TR="bgcolor: #888888"]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]1
[td]
[/TD]
[/td]Name
[td]
[/td]Location
[td]
[/td]Group
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]2
[td]
[/TD]
[/td]Charlie
[td]
[/td]Nashville/Memphis/Atlanta
[td]
[/td]Instructors
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]3
[td]
[/TD]
[/td]Dave
[td]
[/td]Seattle/Las Vegas/San Francisco
[td]
[/td]Instructors
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]4
[td]
[/TD]
[/td]Adam
[td]
[/td]Montreal/Quebec/Barre
[td]
[/td]Student
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]5
[td]
[/TD]
[/td]Charlie
[td]
[/td]Dallas/Memphis/Houston
[td]
[/td]Student
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]6
[td]
[/TD]
[/td]Ellen
[td]
[/td]Manchester/Concord/Boston
[td]
[/td]Instructor
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]7
[td]
[/TD]
[/td]Ellen
[td]
[/td]Johnston/Memphis/Augusta
[td]
[/td]Student
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]8
[td]
[/TD]
[/td]Benjamin
[td]
[/td]Post Falls/Oregon/Seattle
[td]
[/td]Instructor
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]9
[td]
[/TD]
[/td]Dave
[td]
[/td]Seattle/Denver/St Louis
[td]
[/td]Instructor
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]10
[td][/td]
[/TD]
[td][/td]
[td][/td]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]11
[td][/td]
[/TD]
[td][/td]
[td][/td]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]12
[td]
[/TD]
[/td]Tally 1 = 2 (Charley & Ellen)
[td][/td]
[td]=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Memphis",B2:B9,1)),IF($A$2:$A$9<>"",MATCH($A$2:$A$9,$A$2:$A$9,0))),ROW($A$2:$A$9)-ROW($A$2)+1),1))
[/td]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]13
[td]
[/TD]
[/td]Tally 2 = 1 (Adam)
[td][/td]
[td]=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Student",C2:C9,1)),IF(NOT(ISNUMBER(SEARCH("Memphis",B2:B9,1))),IF($A$2:$A$9<>"",MATCH($A$2:$A$9,$A$2:$A$9,0)))),ROW($A$2:$A$9)-ROW($A$2)+1),1))
[/td]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]14
[td]
[/TD]
[/td]Tally 3 = 2 (Benjamin & Dave)
[td][/td]
[td]=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Instructor",C2:C9,1)),IF(ISNUMBER(SEARCH("Seattle",B2:B9,1)),IF($A$2:$A$9<>"",MATCH($A$2:$A$9,$A$2:$A$9,0)))),ROW($A$2:$A$9)-ROW($A$2)+1),1))
[/td]
[tr]
[/TR]
[/TABLE]
[TABLE="class: grid"]
[td]Sheet: Sheet1
[/td]
[/tr]
[/TABLE]Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself -
Re: Formula to automatically search multiple criteria and extract multiple columns in
Hi and welcome to the forum,
Take a look at the sheet and see if this is what your after.
Hope this helps you on your investigating journey into the wonderous world of excel. -
Re: Formula to meet multiple criteria and perform calculation
Hi,
Wasn't this already solved by the answer you got on: http://www.excelforum.com/exce…-perform-calculation.html