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
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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?
-
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).
-
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.
-
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Thank you, I just made this correction to my formulas on the summary, and I will see if this works. I appreciate the advice.
-
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives