LinkBack URL
About LinkBacks
NBVC,
Thanks for the quick response.
I tried your Index/Match formula but it's doing the same thing as the Vlookup, only finds the 1st occurrence in the Helper Column ($A$62:$A$92).
Jim
NBVC,
Thanks for the quick response.
I tried your Index/Match formula but it's doing the same thing as the Vlookup, only finds the 1st occurrence in the Helper Column ($A$62:$A$92).
Jim
Oh, I see now what you're looking for...
try this formula in I6:
this formula must be confirmed with CTRL+SHIFT+ENTER, not just ENTER... you will see curly { } brackets appear, if entered correctly.
=IF(SUMPRODUCT(($G$62:$G$92=$C6)*($M$62:$M$92<=I$4)*($T$62:$T$92>=I$4)),INDEX($A$62:$A$92,MATCH(1,($G$62:$G$92=$C6)*($M$62:$M$92<=I$4)*($T$62:$T$92>=I$4),0)),0)
Then copy the formula over and down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
NBVC,
Your Brilliant ... Works Perfectly!
Now I'm going to try to figure out a Conditional Format to Change cells to Yellow if Cell >= 1 and Today > Start Date but < End Date.
Can't Thank You Enough.
Jim
How about for I6 yellow Conditional Formatting:Now I'm going to try to figure out a Conditional Format to Change cells to Yellow if Cell >= 1 and Today > Start Date but < End Date.
=AND(I$6>=1,VLOOKUP(I$6,$A$62:$Y$92,13,0)<=TODAY(),VLOOKUP(I$6,$A$62:$Y$92,20,0)>=TODAY())
NBVC,
I think I may have mislead you. When I plug your formula into the conditional format, all cells from the 5th to the 18th (m6 to z15) turn Yellow.
My first 3 Conditions (Today, Weekend, and Holidays) work fine.
Then I have
Condition 4: =AND(I6>=1,TODAY()>I$4) - Turns cells RED if Today > cell
Condition 5: =AND(I6>=1,TODAY()<I$4) - Turns cells Green if Today < cell
Trying to figure out Condition 6, So far I have
Condition 6: =SUMPRODUCT(($A$62:$A$92=I6)*($T$62:$T$92>TODAY()))
This formula is on the right track as it does turn the cells Yellow if the project started before today and hasn't finished yet (End Date > Today). But, it also turns my cells that were previously Green to Yellow also. Maybe just an ordering thing.
Jim
Yeah, it probably is an ordering thing...
Conditional Formatting works like this...
If the first condition is satisfied, it will apply that format and stop...
If the first condition is not satisfied, it will go to the next condition and so on...
so you must be careful of the ordering... if more than one condition applies only the first one that it comes across will be used.
NBVC,
Thank you for all your help!!!
I changed my Conditional formula to =SUMPRODUCT(($A$44:$A$74=I6)*($M$44:$M$74<=TODAY())*($T$44:$T$74>TODAY())) and had to put that as Condition 4, before my previous Conditions 4 & 5 ... Works Perfectly.
Again, I can't thank you enough. You guys are Great.
Regards,
Jim
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks