Efficient Formula for Sheet References?

JessBushby

Hi,

Complete novice here so please excuse my crude description of what I'm trying to do! Also if this question is already in the forums somewhere apologies, I'm not entirely sure what the correct terminology is so I do not know what to look for.

I'm looking for a way to pull multiple sheets (training records) into one master sheet to see the overall picture of the data and further manipulate it in this master sheet. For example, % of complete courses per individual, as well number of CPD hours in a given year based on training date. I have a small data set (50 or so training records) and I'm currently using individual formula/references (COUNTIF and SUMSIF) in each row meaning I've already used up the number of references allowed.

With excel, I'm used to using a VLOOKUP and asking it to return data in a particular column, then doing whatever I need with that data once its pulled through but I need to reference multiple columns (CPD hours and the year the CPD was completed in). But feeling totally lost with Smartsheet!

Can someone please guide me?

Thanks,

Jess

Andrea_Thompson

Hi Jess! Have you tried exploring Index Match equations? It works similar to VLookup. And if you are pulling data with more than one criteria, then you would use Index Collect. Try searching for this topic in the community.

Andrée Starå

Hi @JessBushby

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JessBushby

https://community.smartsheet.com/discussion/comment/429510#Comment_429510

Thank you for replying to my question, Andrea. May I ask for some further clarification?

With Index Collect it seems that I would still be creating two different references to an individuals training record within the formula to return the right data i.e. the CPD Hours and then Training Date (to achieve the result of X number of hours of CPD completed in 2023). With 50+ individuals, and two years of CPD to report on I would quickly reach my same limit of references as I have already, see below for a snip of my sheet. I am referencing back to the individual training record for each column at least once within the formula. I'm certain its user error on my part...

Many thanks for any continued guidance you can offer!

Jess

JessBushby

@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å

@JessBushby

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

JessBushby

@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

Andrea_Thompson

@JessBushby,

The screenshots are helpful! So are you reaching your reference max because each person has a separate Smartsheet grid that you have to reference?

JessBushby

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

Andrea_Thompson

Hi @JessBushby,

There are so many ways to use Smartsheet for your purposes, but here's my 2 cents: Whenever possible, I like to set up all the data on one sheet because it will allow for easier data collection and the creation of metrics sheets, dashboards, etc. If there are multiple rows for one identifier (ex: a trainee), then I set up a filter for each so I can view/edit only their information. However, this can be tricky when you want other people to view and edit the sheet as well. That's where the WorkApps come in handy and you can create dynamic views for different people and roles.

If you need to keep the multiple individual sheets, then maybe you could use cell linking to somehow merge the data into one sheet. From there, you could create another metrics sheet with the references. Cell linking can just be a more manual process in the beginning because you will have to link every individual cell.

😊

JessBushby

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!

Andrea_Thompson

Hi @JessBushby,

I found this on cell links: You can create links of up to 500 cells from the same source sheet at a time and can have a total of 500,000 inbound links in a destination sheet.

For your follow up question, I'm not sure I quite understand how your sheets are set up with the need for the column formula. Feel free to send screenshots maybe if that helps. To create a column formula you would right click the cell and choose that option. But you would need to be able to use [column name]@row when referring to the columns in your formula. Sorry, I'm not sure if that makes sense!

JessBushby

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:

Andrea_Thompson

Hi @JessBushby,

It looks like you may have missed the parentheses at the end of the formula when you copied/pasted it. Usually that #unparseable error message is something simple like a parentheses is missing.

In addition, you won't be able to click on columns headers when using formulas. But you can click on a cell under the header and that should work so you don't have to keep retyping it.

JessBushby

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!

Andrea_Thompson

@JessBushby,

Good luck to you! If I answered your question, you can mark the comment as "yes" for "Did this answer the question?"