Maintaining Formulas When Sorting a Sheet

Hi,

Is there a way to sort a sheet where the formulas do not change. I have a few formulas at the top of a sheet and whenever someone sorts through the sheet, my formulas change based on where it was moved to.

My formula is a COUNTIFS and I want it to count what I am asking regardless of where in the sheet the row where the formula is jumps to. Is this possible? I have a lot of people that look at the sheet and like to filter, only it mixes the formula.

Find more posts tagged with

Sheets

Formulas

Solution Center

Project Management

Comments

Shaine Greenwood

Hi,

The best way to make sure that when others sort the structure of your sheet stays the same is to create a report, and get your colleagues to sort the report instead.

Details on reporting is available in the help center: https://help.smartsheet.com/articles/522214-creating-reports

If a report doesn't work for you, you might consider adding absolute references to your formulas. For more info, see: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet#reference

davidharley

Absolute referencing works when moving the formula, but not when rows are sorted. Surely there must be a way to get a countif formula to remain static as conditional formatting works?