Why does my Metrics constantly stop calculating correctly?

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

All comments

@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.)

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.

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

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!

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

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!

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

Sure thing. I'm happy to help whenever.

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,

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

"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!