Formula cells are breaking because the cells are linking to one another inside the sheet

BethWork

I built out a metric sheet and use sheet references within the cell formula. It was all linking and working correctly. I noticed last week that one of the formulas was not correctly calculating the total as it was previously. When I try to fix the formula and reference the correct column from the other sheet it breaks EVERY formula on the sheet.

Nothing had changed and the references from the other sheet hadn't changed.

How do I troubleshoot this so I don't have to write the formulas all over again? How can I tell why changing a formula on one part of my sheet is breaking another one?

Paul Newcome

What exactly are you changing, how exactly are you changing it, and what kind of error are you getting?

Lucas Rayala

Hi @BethWork, this needs a little more context, maybe a few screenshots, to help clear things up for you. However, general troubleshooting tips:

First thing, of course, it to recheck and see if you accidentally introduced a formatting issue (such as comma placement or a one-off parenthesis). Does changing the column reference back "fix" things? Then you might have a data-type issue or an error in your reference column.

What kind of error are you getting?

Are there any errors in the column you are referencing (i.e. "Invalid Value", etc)? If so, that may break your reference.

Is the new column a different data type than the old column? For instance, if the old column was a date column, but the new column is a text/number column that has dates hardcoded in, your formula will likely break.

In general, I would change the formula back, then create a column to test the formula. Generally a good idea to never update live formulas until you know they work.

Test small portions of your formula to see if it breaks.

BethWork

@Paul Newcome & @Lucas Rayala

I'm not getting an error message. It's returning the results as 0. Which, to me, indicates that one of my column references from another sheet might be referenced wrong.

Here's what I'm expecting

Total sites=1046

Sites remaining (should) =1042

Live Sites= 4

Formula 1:

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, AND(@cell <> "Store Name"), {ALL STORE Rollout Schedule-IT-1547-EG Range 4}, "Open")

Formula 2:

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, AND(@cell <> "Store Name"), {ALL STORE Rollout Schedule-IT-1547-EG Range 4}, "Open", {ALL STORE Rollout Schedule-IT-1547-EG Range 2}, "Not Started")

Formula 3:

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, AND(@cell <> "Store Name"), {ALL STORE Rollout Schedule-IT-1547-EG Range 4}, "Open", {ALL STORE Rollout Schedule-IT-1547-EG Range 2}, "Completed")

If I update the column reference in Formula 3 (bolded and italicized) to reference the correct column it will then show I have a total of 4 live sites, but then the numbers just beneath it will entirely wipe out to zero.

There is not reference to those particular cells within those formulas so I'm not sure why they change. The same thing happens on different parts of the sheet.

Paul Newcome

It sounds to me like you are Editing the cross sheet reference as opposed to creating a new cross sheet reference all together. When you edit a reference, it will apply that update to all instances of that reference throughout the entire sheet.

So if some formulas do need to reference that original column, then you are going to want to remove the reference from the formula you need to adjust and then create a new reference.

But the way I read your formulas, that reference should be looking at a column that contains a list of store names?

Are you able to provide a screenshot of the source data?

BethWork

@Paul Newcome OH! That would explain why it's updating all the other cells. That actually helps me out a lot in understanding the references in the whole sheet.

Yes, in that particular reference should be looking at a list of stores names. BUT I have a similar formula where it where it looks at the list of solution types.

It's a confusing set of data because I can't just use the store name as my cross sheet column reference in every formula. In some instances the criteria is a cross sheet reference for a column called "Solution Type" which may include many different store names.

Paul Newcome

Ah. Ok. So it sounds to me like you are going to want to delete the cross sheet reference from that single formula and create a new one instead of updating the existing cross sheet reference. Or did you try that already and still have issues?

BethWork

@Paul Newcome Yes, once I delete that cross sheet reference and create a new one it doesn't alter the other formulas. However, I do now realize that when I create a cross-sheet reference I should update the name so it's distinctive and doesn't cause issues in the future.

Now that I've gone back and fixed all my formulas can I still edit the reference name? Clicking edit reference and then renaming it there?

I realize I would have to do that for every formula, but I don't want a problem in the future.

Paul Newcome

You can do that or you can use the reference manager. Either way, updating the name of a reference will impact the sheet in the same way where it will apply to every instance of that cross sheet reference all at once.

Keep in mind though that it can get a little finicky if you have column formulas. I suggest converting them to cell formulas, updating the reference naming convention, then setting them back as column formulas.

EDIT:

It can also get a little finnicky if you are trying to update within a sheet summary field. It works a lot better if you use a reference in the sheet (even if it is just a "dummy" formula in the sheet) or the reference manager.