Efficient Formula for Sheet References?

@Andrée Starå & @Andrea_Thompson

I thought it may be useful to include the current formula's I'm using for the above snip of columns. There seems to be 7 references to an individual training record for each row of data, multiplying this by 50 people is where the volume is coming from and my maximum references being reached.

Can I be more efficient with these formula or am I trying to use Smartsheet in a way that isn't intended?

% Trained:

=COUNTIF({Jessica Bushby - Training Types}, OR(@cell = "Internal training", @cell = "External training - certified", @cell = "CPD", @cell = "External training - informal")) / COUNTIF({Jessica Bushby - Training Types}, OR(@cell = "Internal training", @cell = "External training - certified", @cell = "CPD", @cell = "External training - informal", @cell = "Planned training", @cell = "Renewal required", @cell = "Expired"))

Total Courses:

=COUNTIF({Jessica Bushby - Training Types}, OR(@cell = "Internal training", @cell = "External training - certified", @cell = "CPD", @cell = "External training - informal", @cell = "Internal training", @cell = "External training - certified", @cell = "CPD", @cell = "External training - informal", @cell = "Planned training", @cell = "Renewal required", @cell = "Expired"))

Expired/Due Courses:

=COUNTIF({Jessica Bushby - Training Types}, OR(@cell = "Planned training", @cell = "Renewal required", @cell = "Expired"))

CPD Hours 2023

=SUMIFS({Jessica Bushby - Training Record Range 1}, {Jessica Bushby - Training Record Range 2}, IFERROR(YEAR(@cell), 0) = 2023)

CPD Hours 2024

=SUMIFS({Jessica Bushby - Training Record Range 1}, {Jessica Bushby - Training Record Range 2}, IFERROR(YEAR(@cell), 0) = 2024)

@Andrée Starå - Thank you for sharing your expertise and being willing to look at this again with me.

I copied the formula's into another comment, above, but here's the screen shots (this is my name only so happy to not redact):

% Trained

Total Courses

Expired / Due Courses

CPD Hours

Hi @Andrea_Thompson

Yes, each individual has a sheet with their training record on it and I'm trying to bring a snapshot of everyone's training record into one matrix sheet so I can see groups/the overall company training position. This is where the need for multiple references is coming in.

Should this set up be changed all together? I don't feel like I'm doing something extraordinary, but I'm starting to wonder whether I'm not using the tools smartsheet have for presenting and storing information effectively!

Thank you for continuing to engage with my post!

Jess

Hi @Andrea_Thompson

Thank you for this advice, Andrea. I do need to keep the individual sheets, but you've given me an idea and I think combining cell linking to the matrix sheet but first doing the 'maths' of the training summary information in the individual sheets might give me what I need, albeit with some manual work up front. Is there a maximum number of linked cells I can have in a sheet?

Also, a follow on question if I may... I'm trying to make a formula that references an entire column i.e. Training Type for the CountIf/SUM formulas much like I would in Excel (i.e. count anything in column 'E' which meets the following criteria), but it doesn't seem to be compatible as when I try to click the column to use this as the range it doesn't 'allow' me. Is this another user error? If I manually select the cell range, i.e. E1 to E52 this works but I then have to repeat this selection in 50+ training records, instead of being able to copy the formula over to each sheet and it automatically reading the right column. Each training record has a different number of rows hence wanting the copied formula to work regardless of which record I copy it into.

Hoping I continue to make sense with my limited knowledge of the appropriate terminology!

Hi @Andrea_Thompson

Thank you for the info on cell links, this is reassuring.

Here's another set of snips of the sheet/formula. I manually wrote '[Training type]:[Training type]' into the formula I couldn't select the column heading when writing the formula. This worked in the first sheet I wrote it in - , see Snip 1, section in yellow.

I copied the formula in Snip 2 into this training record instead of having writing the same formula again but whilst it worked in the other record, it doesn't here. See Snip 3 for error in Total Assigned Training Elements cell.

Whilst I can manually write the formula 50+ times into each training record, I'd rather not if there's a way around it.

Snip 1:

Snip 2:

Snip 3:

Thanks ever so much for your guidance @Andrea_Thompson. I'll have another look at the formula and hopefully I'll be able to get it working!