Hi again,
I was able to come up with a formula that works great for listing single matches that fall within dates. Any more than one match, and CONFLICT is shown, which was my initial intention. I have this formula pasted in each cell in a row on a calendar-type sheet, where each column is another workday - this one is for BILL, using named ranges:
=IF(SUMPRODUCT(--(WORKDAY("Jan 1, 2019",COLUMN()-2)>=BILLSTART),--(WORKDAY("Jan 1, 2019",COLUMN()-2)<=BILLEND),--(BILLEND<>""))=1,INDEX(BILLJOB,SUMPRODUCT(--(WORKDAY("Jan 1, 2019",COLUMN()-2)>=BILLSTART),--(WORKDAY("Jan 1, 2019",COLUMN()-2)<=BILLEND),--(BILLEND<>""),ROW(Info!$A$1:$A$47))),IF(SUMPRODUCT(--(WORKDAY("Jan 1, 2019",COLUMN()-2)>=BILLSTART),--(WORKDAY("Jan 1, 2019",COLUMN()-2)<=BILLEND),--(BILLEND<>""))>1,"CONFLICT",""))
Now, I need to show up to the first 3 matches, and display CONFLICT if there are more matches than this, but I can't quite figure out how to do this. Any suggestions? See this example Google Sheet for more details.
Edit: Marked as SOLVED due to attempting a simpler approach to this issue.
Bookmarks