Sheet Summary cell reference changing?

1_UTOPIAlk

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 

 

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