Best Of

It sounds like you want to be able to change the date in Sheet 2's column formula, and specifically to do it inside a report. It sounds like you could create on your sheet 2 a "Manual Override" column, and then call that "A" (so it matches the Sheet 1 column name). And then somewhere on your Sheet 2, you can add your Index/Match to pull the date over, but add a clause to consider the manual override data:

=IF(ISBLANK([Manual Override]@row, INDEX/MATCH FORMULA TO PULL YOUR DATE, [Manual Override]@row)

That will make it possible for your date columns on both sheets to be manual entry. What other reports, automations, etc. you may have produced on that existing column "A" on Sheet 2 might need some retooling though.

Good luck!

Hi @BTO84

You're right that in Smartsheet, Calendar View coloring is set at the sheet level, not at the report level. Because of that, if you need two different color schemes (Division for managers vs Approval Status for payroll), here's a clean method:

First, it's important to know:

• Calendar View colors are based on the Primary Column.
• A sheet can only have one Primary Column, which means you can't change the Calendar display title between two different reports if they are both built from the same sheet.
• Also, Copy Row automation only moves the row once — it doesn't keep the two sheets synced if the original changes later.

Because of this, the best solution is to create a second sheet and link the data between them using cross-sheet formulas, not just copying rows.

Here’s the step-by-step:

In your Original Sheet:

• Add a System Column (Auto-Number type) called "Row ID".
• This automatically gives every row a unique ID automatically.

https://app.smartsheet.com/b/publish?EQBCT=49930dc8ef2e43aca9b73744c5116fbd

Create a second sheet for Payroll:

• Copy the sheet structure (Save as New) to keep the same columns.
• In the second sheet, use cross-sheet formulas to pull live data from the original sheet into the necessary columns, matching by the Row ID.
• Set the Primary Column differently if needed (for example, use "Name - PTO Used" as the title in the second sheet).

https://app.smartsheet.com/b/publish?EQBCT=97a46ea3912f48bb90fa2e3b64247bd9

Now you can apply separate Calendar View setups:

• In the Original Sheet, apply conditional formatting based on the Division for the Manager Calendar.
• In the Payroll Sheet, apply conditional formatting based on the Approval Status for the Payroll Calendar.

Finally, build two reports:

Manager PTO Calendar Report (based on the Original Sheet): Title is "Name - Division", colored by Division.

https://app.smartsheet.com/b/publish?EQBCT=ff0748ef86fa496b80d55b44b8db1dc3

Payroll PTO Calendar Report (based on the Payroll Sheet): Title is "Name - PTO Used", colored by Approval Status.

https://app.smartsheet.com/b/publish?EQBCT=b603b8e7149e4771815eff1234c64b9c

This way, each Calendar View is independent, the formatting fits the audience, and any updates to the original data automatically flow through to the second sheet using cross-sheet formulas — without having to manually recopy rows or set up complicated automations.

Formulas

[Name - Divistion] =IFERROR(INDEX({sample_time_off_requests_original Name-Divison}, [Row ID]@row), "")

[Start Date] =IFERROR(INDEX({sample_time_off_requests_original : Start Date}, [Row ID]@row), "")

[End Date] =IFERROR(INDEX({sample_time_off_requests_original : End Date}, [Row ID]@row), "")

[PTO Used (# of hours)] =IFERROR(INDEX({sample_time_off_requests_original : PTO Used}, [Row ID]@row), "")

[Approved?] =IFERROR(INDEX({sample_time_off_requests_original : Approved?}, [Row ID]@row), "")

You can use a separate sheet with formulas containing cross sheet references.

Put the desired labels in one column and in the next column a COUNTIFS to count how many are for that color.

You would need to use a Change Cell automation if new rows are being added directly in the sheet. If new rows are being added via form, you can set the default values there and then hide the fields within the form.