One Data Set, Multiple Calendars

Gloria H.

I have a sheet set up for publishing to Outlook calendar. "PTO Detail" is the primary column and displays on the calendar. I need to create a second calendar with all of the same dates with the second column "PTO Detail Team". What's the best way to go about this?

All of the data is input via a form, and both calendars need to be 100% consistent with each other.

I believe I need to get the "PTO Detail Team", and date columns into another sheet, but I can't figure out a way to do this automatically, reliably, and in a way that makes it the detail for the team the primary column, which is required for the calendar. Anyone know a way to do this?

Thanks!

Andrée Starå

Hi @Gloria H.

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Gloria H.

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Gloria H.

Hi Andre, thanks for this tip! This is exactly what I was trying to figure out and it worked perfectly. I didn't know about the auto-number feature.

Thank you!!

Gloria H.

@Andrée Starå, with further testing of this solution I've run into another problem which maybe you can help with?

The set up of the Auto number in Sheet 1, and manual entry of numbers in Sheet 2 to match against worked well to set up an index match. I'm experiencing problems when new data is entered into the Sheet 1 via a form.

I am seeing that when the form adds new rows to the top of the sheet, the Index match on sheet 2, now returns #NO MATCH, for all fields except for the new row.

When I switch the form to add new rows to the bottom of the sheet, the index match on sheet is accurate for all existing data, but also adds hundreds of extra duplicate rows below the newly added row.

It seems that Sheet 2 can handle it when rows on sheet 1 are reordered, filtered, etc, but not when row 1 is moved out of first position. I kind of understand that, but now that the duplicates have been introduced, I can't get rid of them.

Here is my index match formula:

=INDEX({PTO Detail Team}, MATCH([Merge ID]@row, {Auto Number}))

Andy insight you can give would be greatly appreciated!!