Sheet Summary cell reference changing?

Hello,

I have built a sheet summary with formulas that look something like this, =COUNTIF(Provider1:Provider154, "Syringa"). The problem that keeps happening is that the cells I referenced to count, are changing randomly. For example, I will reopen the sheet and the formula looks like this, =COUNTIF(Provider24:Provider75, "Syringa"), hence making the results wrong.

I have been saving the formula, directly after entering it into the cell. I have also locked the sheet summary cells so others cannot change them. Can anyone advise how to correct this, or is this a bug?

Thank you!

Laura

Find more posts tagged with

Construction

Sheets

Support

IT & Operations

Comments

Andrée Starå

Hi Laura,

Strange!

If you haven’t already, I would recommend that you reach out to the Smartsheet Support Team. Smartsheet Support Team

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

Is the sheet being sorted or the rows being rearranged? That could cause this to happen. To avoid this, you could try either referencing an entire column or leveraging hierarchy to run you formulas on CHILDREN rows.

Mike Wilday

Are you collecting data on a form? Is it adding new rows to the top of your sheet? This is the only way I could see that the referenced data keeps moving. Are people sortying your sheet or adding information in those summary rows?

Paul Newcome

I had thought about forms, but then the range SIZE would be the same.

[Column Name]1:[Column Name]10 would change to [Column Name]2:[Column Name]11. Not [Column Name]2:[Column Name]5 (which is basically what was shown in the example).

1_UTOPIAlk

Thank you all! it looks as if I could have problems due to sorting, and or adding rows to the sheet via a form. I think what I need to understand is how to make the row reference, rather than selecting cells in a row, and see if this helps.

I have reached out to SS support as well for additional ideas on the subject.

Thanks.

Andrée Starå

Happy to help!

Paul Newcome

To reference an entire column, you would use the same basic principal for the range except without row numbers.

[Column Name]:[Column Name] will reference the entire column of [Column Name]. Then your range will not be affected by sorting, adding, or deleting the rows.

If you cannot for whatever reason reference the entire column, I may have a workaround, but it will require some testing if you need it.

1_UTOPIAlk

Thank you, I just made this correction to my formulas on the summary, and I will see if this works. I appreciate the advice.

Paul Newcome

Happy to help!

Mike Wilday

That works as long as you aren't going to be gathering extra data rather than your summary rows. You might also consider summarizing the data in the new sheet summary feature. Which allows you to summarize data from the sheet into a nifty sidebar which will keep your data clean and make it easier to pull data from.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

Sign in to join the conversation!

Quick Links

Trending discussions

Set Up Links for Board View
In my grid view, I added a column that takes a hyperlink but when I mosey on over to Board View, only some of the columns appear as attributes displayed on the card. Can I make the hyperlink appear here?
Sorting not working?
Hi. I have a sheet where I am sorting dates by ascending order. But the sorting functionality doesn't seem to be working. Instead of bringing all items with date values to the top of the sheeting and sorting them in ascending order, it leaves a gap. Is it a result of the "Next Review Due" column containing a column formula…
Automatic Unalignment of Dashboard Widgets
Is anyone experiencing their dashboard widgets automatically being unaligned or they have moved a bit from your last save? I am the type where I like things clean and aligned. Some may say it's OCD. In Dashboard, this can mean the edges of my widget line up, each section is centered or same purposed widgets are the same…