Workaround or formula to use a different primary column in timeline view or calendar view?

KTM

Background: We start with a quote list and have an automation run to copy the data to a job record sheet when a quote #'s status changes to won. Since the primary columns must match in order for this automation to run, we have chosen the quote number to be the primary column for both of these sheets.

Goal: We would like to use the Timeline view or the Calendar view to see all of our jobs by job #, not quote #.

Issue: Both of these views run off of the primary column, which is the quote #, not the job #. Changing the primary column on our quote list or job record sheet is not an option for us, neither is doing a copy row automation from the job record sheet to a new sheet (because of the primary column needing to match).

What we have tried/issue continued: I have tried creating a new project sheet called "scheduling" where the primary column is "job #" and tried to cell link data in from our job record sheet. This doesn't work in the long run as it does not automatically add new entries from the job record sheet to the new project sheet and would require someone to add the cell linking in after each entry.

I am not sure that vlookup or match/index would work because I think we would need some sort of criteria to match it to and we don't want to manually enter anything into the scheduling sheet as it would be an extra step.

Question: Does anyone know of any workarounds or formulas to accomplish our goal?

jmyzk_cloudsmart_jp

I set the Quote sheet's Primary Column blank and used auto number to create the quote#. A workflow automation records the Won Date when the Status changes to Won, and when the date is recorded, another automation copies rows to the Job sheet.

https://app.smartsheet.com/b/publish?EQBCT=68c5133c4c05497dad73cf6e8fe737d2 (Link to the published sheet)

The Job sheet, the copy rows automation's destination sheet, has the following formulas;

[job #] (Primary Column) ="JOB-" + RIGHT("00" + MATCH@row, 3)

[MATCH] =MATCH([quote #]@row, [quote #]:[quote #], 0)

Please note that since the Quote sheet's Primary Column is blank, we can add job numbers independently using the formula.

The MATCH creates an auto number as the automation adds a new row.

https://app.smartsheet.com/b/publish?EQBCT=a5f2daf76d174f15975b8d471c8a4760 (Link to the published sheet)

Timeline View

jmyzk_cloudsmart_jp

I set the Quote sheet's Primary Column blank and used auto number to create the quote#. A workflow automation records the Won Date when the Status changes to Won, and when the date is recorded, another automation copies rows to the Job sheet.

https://app.smartsheet.com/b/publish?EQBCT=68c5133c4c05497dad73cf6e8fe737d2 (Link to the published sheet)

The Job sheet, the copy rows automation's destination sheet, has the following formulas;

[job #] (Primary Column) ="JOB-" + RIGHT("00" + MATCH@row, 3)

[MATCH] =MATCH([quote #]@row, [quote #]:[quote #], 0)

Please note that since the Quote sheet's Primary Column is blank, we can add job numbers independently using the formula.

The MATCH creates an auto number as the automation adds a new row.

https://app.smartsheet.com/b/publish?EQBCT=a5f2daf76d174f15975b8d471c8a4760 (Link to the published sheet)

Timeline View