Find the max for each employee for each column

Pierre LF

Hi there!

I am stuck on an issue with my Skill Matrix, which is now very business critical. I have a table with 300+ columns, one for each skill. Each row correspond to a training record with a date where an employee designated by a number has been trained.

A second table summarize the current status so it should show the max of each column / each skill for the employee number corresponding to the row. There is in this table only 1 row per employee number. I have been trying an Index(Collect( combination but I couldn't figure out a formula for the whole sheet and not only for one column. As I have 300+ columns, I would lke to find a formula for the whole sheet so I do not have to change the scross sheet reference for every columns.

Here is the desired output:

Could you please help me finding the right formula for this? :)

If you are still following, I get another challenge!

I have a third table which are the requirements for each position, with the same skills columns. Each row correspond to a position .

The second challenge I have got is to compare for each skill (so each column), the difference between the Max we just calculated before and the requirement for each position.

Let's make an example!

Patrick Smith our truck driver should have for the column Skill 1 +2, Skill 2 -1 Skill 3 -1 and so on.

I will be really grateful if you have any tips or feedback for me. Please note that I would like to keep the structure as it is linked wiht a form, but all feedbacks will be appreciated! ;)

Have a nice week end!

Pierre

Julie Fortney

Wow, 300+ columns! I can see why you are looking for an alternative to creating all of those cross-sheet formulas. I don't know of a way to create the formula you are looking for to accommodate the whole sheet.

Have you considered using a report instead? The grouping and summary options are handy. This would provide you with a way to see the data.

The downside is that the report summaries don't provide you with data you can use, other than in dashboard charts.

The other idea I have is to change the format of your sheets. You mentioned you don't want to do that, but if it's an option, you could have just one column for the name of the skill, and one column for the value of that skill. You could then use a cross-sheet formula telling Smartsheet to find the max for the person, position, and skill you're looking for.