Help setting up a formula

Alex Germano

Hello there,

I'm trying to set up a formula that shows when a vendor is on holiday. We have a sheet shared with our vendors where they add their time-off, and another one where we send them work. What I want is to create a formula that pulls that data from the holiday tracker and shows if the vendor will be available to work on a project based on its timeline. I don't know if that's something possible.

I was thinking of using the VLOOKUP + IF formula, but I'm stuck on the logical part.

=IF(VLOOKUP(VENDOR@row), HOLIDAY TRACKER range 3, 1, false)...

This is the structure of the Holiday tracker

I appreciate your help!

Thanks!

Jeff Reisman

Just quickly considering this, but I would go with three helper columns in your project sheet:

Based on the vendor name in the project sheet, pull the start date and end date for that name from the Holiday Tracker into Date-type columns called "HolidayStart" and "HolidayEnd". (When creating your INDEX/MATCH, use the link Smartsheet provides to "Reference Another Sheet", and select the column in the Holiday Tracker sheet. Name the range, and it will be entered into the formula in curly brackets like {Remote sheet column name range})

=IFERROR(INDEX({Holiday Tracker Start Date}, MATCH(Name@row, {Holiday Tracker Name}, 0))

=IFERROR(INDEX({Holiday Tracker End Date}, MATCH(Name@row, {Holiday Tracker Name}, 0))

(In English, find the start date in holiday tracker where the vendor name on this row equals the name in the holiday tracker sheet.)

In a third column, determine if your project start date falls with the date range from the holiday tracker:

=IF(AND([Project Start Date]@row >= HolidayStart@row, [Project Start Date]@row <= HolidayEnd@row), "Vendor Not Available", "Vendor Available")

In English, if the Project Start Date falls between the holiday Start Date and Holiday end date on this row, set the column to "Vendor Not Available", otherwise set it to "Vendor Available".

(You could incorporate the INDEX/MATCH formulas above into the IF/AND, but you might do it in three columns first to keep the complexity down until you know all the formulas work!)

Note: these formulas assume that there is only one row in the holiday tracker sheet per vendor.

If your vendor name fields in both sheets are single-select drop downs, that would ensure that your values will match. It also lets you scroll through vendors on your project sheet to quickly find one that is available.