Why does my Metrics constantly stop calculating correctly?

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

Sure thing. I'm happy to help whenever.

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

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