Index/Match - Primary Sheet to Individual Sheet

KTM

Hi Community,

I am looking to track training at the individual employee level as well as for my company overall to provide statistics.

Currently I have two sheets. One is the primary tracker, and the second is an individual employee sheet (template).

The primary tracker has approximately 107 columns, each column represents technical training. We will use this one to provide overall statistics and for the trainer to update status.

The individual employee sheet allows for the trainer to put Notes/Comments for each training and works well for when the trainer is meeting 1:1.

In the Status column for the individual sheet, I have this formula:

=IFERROR(INDEX({Training Tracker Range 3}, MATCH([Employee Name]1, {Training Tracker Range 55}, 0)), "DO NOT EDIT")

It works, but here is what I've run into:

1. I received this error after trying to do the formulas on my template version.

2. It seems like if we were to add new columns to the primary tracker, the links in the Status column will break, which won't work as I'm sure our training will evolve.

Ideally, it would be nice if we could edit the status from either the primary tracker or the individual sheet, but I haven't figured that out either.

Suggestions for either a different formula (that doesn't limit us, or break the whole sheet), or a different way to build this out so that we can accomplish having a primary tracker and something user friendly at an individual level without needing to manually update twice.

I know we can build a Smartsheet report, but with how many columns we have… it's not ideal.

Thanks in advance for your ideas/thoughts!

jmyzk_cloudsmart_jp

Hi @KTM

A sheet can include no more than 100 distinct cross-sheet references.

If your formula references the employee name column and more than 100 training columns, you've hit the limit.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Before you create cross-sheet references

You must have the required permissions.

A sheet can include no more than 100 distinct cross-sheet references.

A reference range can include a maximum of 100,000 inbound cells.

To avoid the limit, instead of referencing each column, you can reference a range of cells and use the VLOOKUP function.

In the example below, the sheet has 120 columns for training status and 100 employee names.

In the formula below, to use a stable column formula, I used the Sheet summary field to look up the [Employee Name]1 value.

[Status]
=IF(ISNUMBER([#]@row), 
  VLOOKUP([Employee Name]#, {Primary tracker - Employee Name: Training 120}, [#]@row + 1, 0))

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

Range: {Primary tracker - Employee Name: Training 120}

Another sheet created from the template works fine when I changed the employee name.

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