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

How can I combine grid data and Sheet Summary fields into one editable grid sheet?
I have a Sheet Summary Report that pulls data from 48 individual sheets — this includes both the row-level grid data and each sheet’s Sheet Summary fields. I want to have all of this information (both grid and summary data) together in a regular grid sheet, so I can format it, add formulas, and use it in dashboards more…
Forms - Ability to Lock fields
First of all, I use Smartsheet Gov, which I know differs (substantially) from Smartsheet Commercial, as I previously worked with that for about 3 years at my previous company. First Question, for forms, is there a way to LOCK a field? We have a field that comes up auto populated with the TDL #, based on the site where the…
Forms - Ability to Lock fields
First of all, I use Smartsheet Gov, which I know differs (substantially) from Smartsheet Commercial, as I previously worked with that for about 3 years at my previous company. First Question, for forms, is there a way to LOCK a field? We have a field that comes up auto populated with the TDL #, based on the site where the…