Formula help for INDEX MATCH. Or is it INDEX COLLECT?!

1845sarah

I have a holiday sheet for Team Members to log their holidays: Assigned To, Start and Finish Date. A formula has been created to flag the status of when they are ‘On Holiday?’: eg, ‘Holiday Complete, ‘On Holiday’ and ‘Holiday Booked’. Based on this status, an ‘Availability’ symbol also appears

Id like to use this information on a timing plan. When I assign ‘Joe’ a task and a start and finish date, should any of these dates ‘clash’ with the dates on the holiday sheet, Id like the relevant availability symbol to appear

So in the below example, Jane has a task assigned 11/03 to 13/03 but the holiday sheet shows she is on holiday 09/03 to 13/03, hence the ‘cross’

Is this at all possible in Smartsheet?!

Paul Newcome

I would actually suggest a COUNTIFS. Count how many rows on the holiday sheet match the name and have an overlapping date. If that count is greater than zero, throw your flag.

=IF(COUNTIFS({Holiday Sheet Name Column}, @cell = [Assigned To]@row, {Holiday Sheet Start Column}, @cell <= Finish@row, {Holiday Finish Column}, @cell >= Start@row) > 0, IF(Start@row >= TODAY(), "Holiday Booked", IF(Finish@row >= TODAY(), "Holiday Scheduled"))