Best way to preserve indexed historical data through versioning?

Zach_

Hi there.

I'll start by saying that I know how to accomplish my goal (through a long =IF formula and a helper column or two), but I'm curious whether anyone here can propose a simpler solution.

Context:

• I'm on a learning and development team. For each person that we train, we track the number of training hours, as well as how many hours of self-serve learning content (eLearning, video, etc.) they were assigned.
• To lesson the burden of manual data entry, we're beginning to use learner journeys (let's say 12 different journeys…) that provide baseline data (see screenshot #1). We'll use an =INDEX(COLLECT) formula to pull baseline data into our sheet for tracking hours.
• Since we're just getting started, these journeys are all V1, but they'll evolve over time and at different rates. This is what I'm trying to solve for. When a particular journey evolves to V2, I want my tracking sheet to be able to pull in the V2 baselines without altering the items that were subject to the V1 journey.

Here's how I'm currently thinking about solving this.

• Adding a "Version" column to my helper sheet (see screenshot #2)
• Adding a "Version" column to my tracking sheet, which will use a complex =IF formula that assigns a version based on the date
• Adding an additional criterion to my =INDEX(COLLECT) formula so that it matches the current version

Can anyone think of a better, more elegant solution? I'm sure that what I've outlined will get the job done, but as our many learner journeys evolve, the =IF formula used to determine the version will become more and more complex. I'd love a simpler solution.

Screenshot 1.png

Screenshot 2.png

Zach_

I just improved my initial idea. Here's what I'm thinking now.

• Adding a "Version" column to my helper sheet (see screenshot #2 above)
• Adding a "Version" column to my tracking sheet, which will use a complex =IF formula that assigns a version based on the date
• Using an automation to record the appropriate journey version for each new item (screenshot below)*
• Adding an additional criterion to my =INDEX(COLLECT) formula so that it matches the current version

*The automation will have to be manually updated whenever a journey evolves, but this doesn't introduce any manual updating where it didn't already exist; an =IF formula would also have to be updated manually, and it would be significantly more complex.

Kerry St. Thomas

I'd read your first post and then closed the tab on accident when I was looking at the screenshots. I was going to suggest that if you can set up an automation instead of a complex IF, you'd accomplish the same output. And… well, there you are. :)

The only other thing I'm thinking is that if rows are added BY FORM, you could conceivably include a hidden field for the version number, so that things are recorded right off the bat. This could be on THIS sheet, or on a separate sheet with a roster of people (to record what version they participated in) - if on a separate roster sheet, you could do a cross-sheet lookup of name.

I really think you're on the path toward as elegant as it's gonna get. Good luck!

Zach_

Hi @Kerry St. Thomas, thank you for your response.

New rows are added by form. I might be misinterpreting your suggestion, but I don't think that would work for us. Managers respond to our form when they want to request training for their new hire; they would be perplexed by any reference to a learner journey version.

Kerry St. Thomas

I am suggesting not that managers complete the version number when requesting training, but you use a HIDDEN field on your form. This way, you can populate a default value into the form, so it always says "V2" or whatever, until you change versions - at which point in time, you can update the default value. This would all be invisible to people completing the form, and would just be a data point included for back-end analysis.

Whatever you choose to do, good luck!