Populate calendar holidays given an agent list and the company holiday calendar sheet

Ciro Moreno

Hello Dear Community,

I have a situation with a leave calendar in Smartsheet, I need to add all holidays for the agents given the office/location, I have the list of agents with the 3 digit country code and a list of holidays with the same 3 digit country code, description and date, I want to populate all dates for each agent in the final Calendar Sheet for every match, any Ideas on how to approach this?

I've been using combinations of Index/Match, Index/Collect with no luck

Thanks in advance for any support on this!

Paul Newcome

Where are you wanting this formula to live, and what would be the expected output?

Ciro Moreno

Hi Paul, Thank you for your reply,

This formula should live in the project leave calendar sheet, there are 3 sheets here, 1st is an Agents List, 2nd is a Holiday Calendar List and 3rd is the actual Leave Calendar(image below) where all the request are going to live, I was able to find a solution using Index, Match, Collect and Join. here are the details:

Here is where the final formulas live:

I was able to do this following these steps:

First in list of holidays I founded all the matches for every country on the agents list:

=VLOOKUP(Country@row, {Agents Calendar Country Range}, 2, false)

In the leave calendar sheet I had to create a Row ID column and then I was able to index all the holidays from the list when there was a match in the country code by using this formula:

=INDEX(COLLECT({Holiday Calendar 2024 Date}, {Holiday Calendar 2024 Match}, "True"), [Row ID]@row)

Then next step was to bring the names from the agents list and join them in one cell per event:

=INDEX(JOIN(COLLECT({Agents Calendar List Name}, {Agents Calendar List Country Code}, [Country Code]2), ", "), 1)

The Idea is to be able to see in the calendar view all the public/company holidays the agents will have around the different global offices.

Paul, let me know if you have another take on this. Thank you.

Regards.

Ciro Moreno.

Ciro Moreno

Hi @Paul Newcome, I hope you're doing well,

I'm facing a new indexing challenge, I've been requested to add one line per each agent, instead of joining them together into a single cell, would you happen to know how can I approach this?

Thanks in advance,

Best Regards,

Ciro M.

Georgie

Hi @Ciro Moreno,

How about using a copy rows workflow to copy rows to the destination sheet? You could set this up on the Leave Calendar sheet, triggering when rows are added or changed, and copying to the Holiday Calendar 2024 sheet. This would mean that on the Holiday Calendar 2024 sheet, you’d have multiple rows for different dates in the destination sheet - one row per agent. For more on automatically copying rows, take a look at this help article: Work with the copy rows action.

Alternatively, if you have access to it, you could use DataMesh to pull in the required data to your Holiday Calendar 2024 sheet.

Hope that helps!

Georgie

Ciro Moreno

Hi @Georgie,

Thanks a lot for sharing these options, going to give it a try very soon!

Best,

Ciro.