Best Way to Find a Row Matching Specific Criteria

Bethany Garcia

I have a sheet that has reached the cell reference limit and won't allow other sheets to copy rows into it. After doing some research it seems that my column references (column:column) within my formulas are the problem.

My sheet contains a unique identifier in the column "ID", and there is a column "Approval Status" which can be Needs Approval or Approved. We are adding new rows to the sheet weekly, and if the ID for the row we add already has a row on the sheet with an Approval Status of Approved, we want that to be identified as such. If the ID for the row we add either already has a row on the sheet but does not have an Approved status, or that ID doesn't exist on the sheet, we want that to be identified as such.

My current formula is below. Right now I'm using a count, so it's 0 if there isn't a row on the sheet already with the same ID and status of Approved, or if it finds a row with the same ID and the status of Approved it returns the count.

Is there a more efficient way to calculate this that doesn't use a many cell references?

=COUNTIFS([Unique ID]:[Unique ID], [Unique ID]@row, [Approval Status]:[Approval Status], "Approved")

Austin Smith

@Bethany Garcia

1) how many rows of data are we talking about?

2) could you split the data into sheets based on months or years, since the data's imported from elsewhere anyway?

3) I don't quite see how the specific formula that you listed causes a problem unless you have a monstrous data set. The only way to simplify would be to build a helper column with just as many references plus the countif formula with reference. Typically the references that lock down a sheet are cross-sheet formulas that pull from other places or formulas that search large arrays of data.

4) See what @Paul Newcome and @Andrée Starå said here 100,000 cell reference limit - do I understand it right and can I monitor it? — Smartsheet Community -- if you have other formulae doing something similar to this, that might be your issue. And their solutions may help.

Bethany Garcia

@Austin Smith

1. There are currently 900 rows on the sheet and I'm trying to move 2600 rows to the sheet.
2. If I split the data into sheets then I would need to manually update the formula to refer to those sheets, right?
3. I do have the below formula in the sheet as well to identify if a given row is the most recent row or not for that ID. Could this, in combination with the other formula, potentially cause the problem? I only have one cross sheet reference in the sheet but it's using INDEX so I don't think that would be causing it, unless all of these formulas combined are causing the problem.

=IF(MAX(COLLECT([Reported Date]:[Reported Date], [Unique ID]:[Unique ID], [Unique ID]@row)) = [Reported Date]@row, "Yes", "No")

Paul Newcome

The limit referenced in the thread above is for cross sheet references. I know there are more references allowed for formulas that are looking at cells within the same sheet, but I can't remember what that is off the top of my head.

Your one formula above though (once you have the full 3,500 rows) would be referencing 24,507,000 cells. And that is just that one single formula. Basically you are referencing 3,500 cells in the first range plus 3,500 cells in the second range plus the two individual cell references. So 7,002 cells being referenced 3,500 times.

I would suggest trying to split up the data across multiple sheets. What is the end goal for these various counts you are trying to get? Are you trying to track how many weeks ago something was approved? Do you need a weekly count of how many were updated to approved? Something entirely different?

Austin Smith

@Bethany Garcia

1) At 900 rows, no, those formulae aren't expansive enough to jam up the works on a data set that small, unless you index is doing something crazy. I have many sheets far larger that do far more without issue.

I'd wager the transport is what it doesn't like. Have you tried to disable the column formulas to do the primary merger and see if the data transports between sheets if references are not involved? You could turn the formulae back on after the transposition if able. Then the weekly rows could move via automation.

2) If you had a helper column(s) to denote the month/year, you could just set up an automation that would send things with the correct year and month to their appropriate sheet. Would take an amount of set up that might not be worth it, but would give you some freedom, potentially.