Too many cells referenced - Need formula revision suggestion?

Cory Strischek

Hi, I hope some of you all had a great Engage. It was my first and very helpful.

As soon as I got back, I started using DataTable to solve for a big problem I've been having with a voluminous sheet, but found that my formulas are still too referential ... according to the warning below.

The TLDR is that I think my formulas would be less intense if I used some kind of INDEX(COLLECT vs. COUNTIFS, but I'm just not sure... Please take a look!

There are no cell links or cross-sheet references in the sheet. The sheet is pulling 4,300 rows from a DataTable. There are 22 columns at the moment. 6 columns have pretty robust formulas as I'm trying to roll-up status quality across different types of related rows I cannot indent, based on a type drop-down value.

I'm wondering if anyone can recommend a different approach to these formulas.

The idea is that, first, Test Subtask Status turns a text status into a RGYB symbol via nested IF.

=IF(AND(IssueType@row = "Test Subtask", OR(JiraStatus@row = "Pass", JiraStatus@row = "Cancelled")), "Blue", IF(AND(IssueType@row = "Test Subtask", JiraStatus@row = "Fail"), "Red", IF(AND(IssueType@row = "Test Subtask", JiraStatus@row = "ON HOLD"), "Yellow", IF(IssueType@row = "Test Subtask", "Green", "NA"))))

Then, User Story Status evaluates associated Test Subtask Status via COUNTIFS. If a User Story has any related Test Subtasks of Red, Yellow, Green, it inherits that quality.

=IF(AND(IssueType@row = "User Story", COUNTIFS(IssueType:IssueType, "Test Subtask", UserStoryGroup:UserStoryGroup, IssueKey@row, [Test Subtask Status]:[Test Subtask Status], "Red") > 0), "Red", IF(AND(IssueType@row = "User Story", COUNTIFS(IssueType:IssueType, "Test Subtask", UserStoryGroup:UserStoryGroup, IssueKey@row, [Test Subtask Status]:[Test Subtask Status], "Yellow") > 0), "Yellow", IF(AND(IssueType@row = "User Story", COUNTIFS(IssueType:IssueType, "Test Subtask", UserStoryGroup:UserStoryGroup, IssueKey@row, [Test Subtask Status]:[Test Subtask Status], "Green") > 0), "Green", IF(AND(IssueType@row = "User Story", COUNTIFS(IssueType:IssueType, "Test Subtask", UserStoryGroup:UserStoryGroup, IssueKey@row, [Test Subtask Status]:[Test Subtask Status], "Blue") > 0), "Blue", "NA"))))

Stories do something similar via Story Status, where they inherit User Story Status's "worst" color...

=IF(AND(IssueType@row = "Story", COUNTIFS(IssueType:IssueType, "User Story", StoryGroup:StoryGroup, IssueKey@row, [User Story Status]:[User Story Status], "Red") > 0), "Red", IF(AND(IssueType@row = "Story", COUNTIFS(IssueType:IssueType, "User Story", StoryGroup:StoryGroup, IssueKey@row, [User Story Status]:[User Story Status], "Yellow") > 0), "Yellow", IF(AND(IssueType@row = "Story", COUNTIFS(IssueType:IssueType, "User Story", StoryGroup:StoryGroup, IssueKey@row, [User Story Status]:[User Story Status], "Green") > 0), "Green", IF(AND(IssueType@row = "Story", COUNTIFS(IssueType:IssueType, "User Story", StoryGroup:StoryGroup, IssueKey@row, [User Story Status]:[User Story Status], "Blue") > 0), "Blue", "NA"))))

Then, I create one status (Test Status) I can use to collate, group/filter in reports

=IF(IssueType@row = "Test Subtask", [Test Subtask Status]@row, IF(IssueType@row = "User Story", [User Story Status]@row, IF(IssueType@row = "Story", [Story Status]@row, "")))

And Group Status based on the super-parent (Story) also helps me group in reports. Basically, everyone gets their parent or grand-parent Story's test status.

=IF(IssueType@row = "Story", [Test Status]@row, INDEX([Test Status]:[Test Status], MATCH(StoryGroup@row, IssueKey:IssueKey)))

Last I have a pretty simple ordering formula called Hierarchy:

=IF(IssueType@row = "Story", 1, IF(IssueType@row = "User Story", 2, 3))

So ... INDEX(COLLECT VS. COUNTIFS or doesn't matter, too many formulas?

Cory Strischek

I should note that I cannot get DataMesh working on this sheet either. I suppose that that's because of the limit being hit, but I should and will check with support too, as directed by the friendly popup. 😀

Amber Grossman

Does DataMesh still count as a reference? I have had this issue MANY times but thought I was getting around it with DataMesh and then blowing out the INDEX/MATCH formulas?

Cory Strischek

Hi Amber, thanks for the comment. I ended up using DataMesh to get around this issue. My earlier comment was noting that I was trying to use all of those formulas AND DataMesh on top of it when I really needed to break down the sheet more and use DataMesh to stitch multiple "processor" sheets all back together, if that makes sense.