Why does my Metrics constantly stop calculating correctly?

Gabriel Barrera

I've been making a "Metrics" sheet for some time now, and in the beginning everything was calculating fine. But it seems to be when I add more "data tables" if you will, or more metrics, some tables stop calculating correctly. Here is what my sheet looks like for a better understanding

So, the first few-ish sections will be fine (Overdue Project Site Visits),(PM Overdue Installation Dates), and (Superintendents' Target Project Site Visits). I put in the correct COUNT formulas, it computes the right numbers. Then, as I make more, or go down the line and enter those required COUNT formulas, (Project Managers' Estimated Installation Dates), etc. The first 3-ish will break completely. They wont have an error or anything, they just stop computing correctly altogether. Then, to fix this, I'll retype the exact same formula, but from scratch, and then It will compute fine. But, if I go back to the (Project Managers' Estimated Installation Dates) table, that is now broken.

It feels like I have reached a limit on the number of COUNT formulas I can have running simultaneously on the same sheet, but I can't confirm this because there is no error message. I hope this makes sense. I'm more than happy to clarify if need be in order to fix this annoying problem.

Another picture to give more visibility. The top table is expanded and is now broken, because I just fixed the bottom table which is also expanded. The bottom is "fixed" because I just re-entered the same formula, but from scratch. Is there a limit I am reaching? Maybe formatting issue of my metrics sheet? Any feedback is appreciated.

Jeff Reisman

@Gabriel Barrera

It doesn't look like you're using conditional formatting, so that's ruled out as a cause (conditional formatting is a resource hog.)

How big are the cross-sheet ranges your COUNTs are referencing? There's a limit of 100,000 cross-sheet links in a sheet. So if you have a column formula containing a range that references an entire column in another sheet with 10,000 rows, then that counts as 10,000 cross-sheet links. If you have formulas referencing 7 different columns in that 10,000 row sheet, you're at 70,000 links, so no problem. But say as time goes on that sheet grows to 15,000 rows, then all of a sudden you're at 105,000 links. I don't know how good Smartsheet is at warning you when this happens. I know if you tried to create new range references at that point you'd get an error, but I'm not sure if it just starts disabling formulas to get back to the 100,000 link limit.

Jeff Reisman

@Gabriel Barrera

It doesn't look like you're using conditional formatting, so that's ruled out as a cause (conditional formatting is a resource hog.)

How big are the cross-sheet ranges your COUNTs are referencing? There's a limit of 100,000 cross-sheet links in a sheet. So if you have a column formula containing a range that references an entire column in another sheet with 10,000 rows, then that counts as 10,000 cross-sheet links. If you have formulas referencing 7 different columns in that 10,000 row sheet, you're at 70,000 links, so no problem. But say as time goes on that sheet grows to 15,000 rows, then all of a sudden you're at 105,000 links. I don't know how good Smartsheet is at warning you when this happens. I know if you tried to create new range references at that point you'd get an error, but I'm not sure if it just starts disabling formulas to get back to the 100,000 link limit.

Gabriel Barrera

@Jeff Reisman

Great insight, I didn't know that. But let me ask you this, does the cross-sheet links increase only if the columns being referenced are different? There's one column that I use in every formula, and it would make sense if I reached the limit if it counted it separately. Also, 100,000 links is the limit for the source sheet, or the destination sheet (my metrics sheet)? It seems like a good workaround this is have multiple metrics sheets designated by categories, such as PM or Super in my case.

Jeff Reisman

@Gabriel Barrera

As long as the reference you're using for that one column is the same in all of your formulas, it should only count once. You wouldn't want to have multiple references for the same range. You can see all the references you have in a sheet by selecting a formula cell containing an inbound link (little blue triangle on the right side of the cell), right-click, and select Manage references.

The inbound link limit applies to the sheet the formulas are on, the one that is essentially looking up data from another sheet, so in your case it's the metrics sheet. (The number of places where a cell on source sheet can be referenced, aka the Outbound Cell Link Limit, is an insane 25 MILLION, so no worries there!)

I have a report I built where my product data admin can enter a product SKU and pull up the 50 or so fields of data he needs for that product, which is sourced in two sheets. One source sheet has almost 5000 rows. So behind the scenes, I have two lookup sheets each pulling data from 18 columns on the large 5000 row source sheet, and one pulling data from 12 columns on the smaller source sheet. The data sheet the report is based on gets a cell populated with the Product SKU by the user through the report; the lookup sheets are linked to that cell, and use it in their INDEX/MATCH formulas. Fields stacked vertically on the data sheet are linked to the various formula fields on the lookup sheets. Once they update, the report filters and sorts them to present in the correct order.

Gabriel Barrera

@Jeff Reisman

That's genius! I would love to learn how to do that so I can incorporate that in my own practice. I just started using Smartsheet and Excel around 2 months ago so I'm still very green. Do you have any recommendations on where I could learn how to do that? I have reports that I built that show relevant information based on specific PMs and their projects' phases. But I'm curious on how you set it up to where your data admin can enter a specific value, and have it spit out the corresponding information. That seems extremely powerful!

Jeff Reisman

@Gabriel Barrera This thing took a ton of brainstorming to get right. It's been in use for 3.5 years, but hopefully will be going away when we have direct product data import into our inventory/finance system. This gives the product data admin person the data they need to create new product records, in the order they need it, without having to scroll across a wide sheet with many rows of data they don't need. They get one record, presented vertically so they only have to scroll down once to see the remaining data fields, without having to worry about their eyes jumping rows and entering the wrong product data.

I have these three sheets tucked away in a background resources folder in my product data flow workspace:

DLFNM stands for Data Lookup For New Materials.

DLFNM-Main is the first sheet in the lookup. Only the first row is unlocked. This appears as the first line on the report, which I mandate by using the Sort column to call it number 1. On the report, the user enters the SKU in the first row of the Material column. My INDEX/MATCH formulas that use the SKU to match all reference the cell Material$1:

I also pull a product hierarchy value into the first row from my my main data sheet based on the SKU. This hierarchy value is used in other INDEX/MATCH formulas.

The SAP Field Name column lists the fields we're looking up values for. The Value column contains my formulas. I'll come back to the green Data Match! formula on row 1.

The second sheet, DLFNM-SCIM is very similar, also looking up values from my main source sheet.

The third sheet, DLFNM-Hierarchy, is using the product hierarchy for the SKU from my main source sheet to gather lookup data from another source.

This is the Report the product data admin person uses to lookup the product data and have it presented in the order they need it in. The instructions are there front and center:

Since they're doing a Ctrl-C/Ctrl-V of the SKU anyway, the instructions have them paste the SKU into the top three Material rows, which correspond to the top row from each of the three data lookup sheets that make up the report.

The Data Match! field from DLFNM-Main contains a formula that is looking to make sure that the SKUs entered on the left have all made their way to the other two lookup sheets, and the hierarchy values found for the SKU also match, so that I know the data I'm seeing is really for the SKU I'm interested in and not left over from the previous SKU the user looked up:

=IF(AND(Hierarchy$1 = {DLFNM-Hierarchy Hier}, Material$1 = {DLFNM-SCIM Mat}), "Data Match!", "MISMATCH! - Please Refresh!")

Because not every SKU has every field populated, the report is filtered to only show the rows from the three lookup sheets where the Value column is not blank. The report is sorted by the hidden Sort column, so that the top row from each sheet show up as rows 1, 2, and 3 on the report, with the remaining fields being presented in the order that matches what the data admin needs them to be in.

Gabriel Barrera

@Jeff Reisman

This was extremely generous and I greatly appreciate you helping me out. This is truly amazing work!!

I've already looked over it multiple times and I know it's going to take a lot more to truly understand. But I'm going to try my best to replicate what you've done and spin it in a way that it useful for my scope of work. I hope we can stay in touch because I know I'm going to have some questions!

Thank you!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/355465#Comment_355465

Sure thing. I'm happy to help whenever.

Gabriel Barrera

@Jeff Reisman

Goodmorning,

I want to do something you similar you did on how you showed relevant columns based on if a value was blank or not. More specifically, I would like a said person to enter a project ID number. That project ID number has a said project status. Depending on that status, the report will filter those respective columns based on what status that project is in. That way, you will always get the relevant "SAP Field Names" at the top, and the others at the bottom. Could you go more in depth on how you sorted it by frequency, or I guess if one was blank and one was not?

Just answer whenever you find the time, I'm in no rush!

Thanks,

Jeff Reisman

@Gabriel Barrera I use a couple different things to achieve this.

Sort: On all the source sheets, each row has a numeric value in the hidden "Sort" column. The Sort column for the top row on each of the three source sheets contains, 1, 2, and 3 respectively. That's what forces the three rows to be at the top of the report:

Because my source sheets are organized by lookup source, the fields on each aren't necessarily in the order I need to present them in to the user. By using the sort column to assign each a different numeric value, I can force it to show up in the right order on the report.

The next thing I use is the filter. I filter the report to only show me rows where there is a value in the Value column. So now the report will show me the fields I need to see only if they have data, and they're still in the right order, just skipping the empty fields.

I do cheat just a bit though! What you can't see is that in the Value columns for rows 2 and 3 on the report (which are the top rows of the other two source sheets,) there is an "X" in white text, making it invisible. Therefore, there's a value in the Value column, which keeps the row from being filtered out.

Gabriel Barrera

@Jeff Reisman

"Trickery" LOL.

I didn't use your exact method but your overall structure and ideas proved to be extremely useful and I'm now a lot closer to my end goal. This saved me a tremendous amount of time.

Thank you!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/356623#Comment_356623

You're quite welcome! Just remember to stick around here and pay it forward. 👊

Gabriel Barrera

@Jeff Reisman

Of course!🤝

Gabriel Barrera

@Jeff Reisman

Just an update.

Ended up doing a similar sorting trick you did. Also added some conditional formatting to just to differentiate between the filled and unfilled fields a little easier. The fields will fill with a light green background color if there is information in the that respective cell. And the fields will fill with a light red background color if the cell is blank.