New reference range in a metrics sheet is not working.

emilyc9

This one has me baffled.

I have added a row to a large sheet. I notice it does not show up in any of my metrics calculations after the add.

In my metrics sheet, I refer two ranges in another sheet: {All Action Items Status1} and {All Action Items Resp Org Map}, which exist as a reference ranges.

My original formula for accessing this data is:

=COUNTIFS({All Action Items Status1}, "New", {All Action Items Resp Org}, CONTAINS("DA", @cell)) and it worked without error

This worked before I had to change the second reference from another old reference {All Action Items Resp Org} to data in a new column in my sheet to {All Action Items Resp Org Map}.

I deleted all references to the old range in my metrics sheet. I have verified there are no references to the orignal range remaining.

I have duplicated the old column in my source sheet to make sure I don't have any weird old references. I reference the new Column range in my new formula:

=COUNTIFS({All Action Items Status1}, "New", {All Action Items Resp Org Map}, CONTAINS("DA", @cell))

Now, since replacing the reference, I get:

#INCORRECT ARGUMENT SET

..for every formula. I don't see what I am missing. Argh.

Thank you so much if you can help.

emilyc9

Although I could not find the bad reference, I deleted my entire status sheet (that calculates counts and totals) and rebuilt it from scratch. Problem solved. Never did find the bad reference….

Thanks again,

Emily

Isaac A.

Hi @emilyc9!

I've tested your formula, and it appears to be working fine on my end. The main thing to check is whether the cross-sheet references were inserted correctly when building the formula. Make sure your references were inserted properly, this is how they look when added the right way (see screenshot).

I recommend manually inserting the references instead of copy-pasting them, as pasting can sometimes cause issues when editing references later. You can find more details on how to create cross-sheet references here: Create a Cross-Sheet Reference.

If you’re still experiencing problems, it would be helpful to see your data. If possible, please provide a screenshot of the sheets used in the formula (with any sensitive info blurred) or share a mock data example so we can continue troubleshooting.

I hope this helps!

Cheers,
Isaac.

emilyc9

Thank you so much for your feedback, Isaac,

I know my formula was correct and my reference was properly defined. (I have been doing this for years.) I deleted every reference to the sheet reference I was having trouble with, and recreated the refererence, which Smartsheets allowed me to do. It still did not solve the problem. Since I could not solve the problem and I had a deadline to meet , I made a copy of my base sheet and reassigned references in the copy, then it worked. There must be an instance of an oddball reference in the old sheet I cannot delete (I sure wish there were a drop-down in the "Select Sheet Reference" box when you are refering to another sheet that lists the defined references.)

I'm not thrilled with the workaround but at least it worked.

Kind thanks again for your input.

Regards,

Emily

emilyc9

Although I could not find the bad reference, I deleted my entire status sheet (that calculates counts and totals) and rebuilt it from scratch. Problem solved. Never did find the bad reference….

Thanks again,

Emily