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

Is there a way to prevent a shared user from seeing the data in the "View Detail" option?
Is there a way to prevent a shared user from seeing the data in the "View Detail" option? I'm trying to make sure that when a user is given access to view a report, they can only view the columns present in that report. The view might only show certain columns but the user can then click on the record as shown below and…
Gantt Chart Timescale not aligning with projects fiscal year and start/end dates
I have a project sheet with a Gantt chart where the timescale headings are not aligning with my project’s fiscal year and start/end dates. Configuration: - Primary heading: Quarters (displaying as Q4 2026) - Secondary heading: Months (MMM format) - Fiscal year start: February 2 - Week start: Sunday - Project start date:…
Changing how items are displayed on the calendar
Is there an overall calendar setting whereby I can choose a specific column to display in the calendar view? I have created an additional column that gives the task name (located in a child row) as well as the main task (in it's parent row). I want that to display in the calendar. Overall creating a content calendar and…