Enable Column Formulas to Be More Flexible

Hello SS Community,

I was fortunate to find a fantastic trick proposed by Paul Newcome in 2021 to index the occurrence of a value in a given column. It was exactly what I was looking for. The only drawback is that there are limitations to column formulas (see screen clipping). In my solution I am using Absolute references which, you can see below, is an unsupported reference type.

Would you please find a way to allow for some of these currently-unsupported types in column formulas? It's such a terrific solution, and yet having to drag the formula down the entire column anytime a row is added or deleted detracts from its efficacy.

Here's a link to the tip from Paul Newcome:

https://community.smartsheet.com/discussion/81127/identify-cell-instance-count

Sign in to vote!

Find more posts tagged with

Comments

Paulo Ferrer

There are many limitations to the column formula, but the one that bothers me the most is the impossibility of referring to a single cell. For example, if I want to capture a number that is in the first row and first column, as a constant, I can't get it in the column formula. Increasing referencing flexibility in column formulas would be a big step forward.

Franklin

the current smartsheet will give an error if the formula with "$", for example: if i want to convert one cell formula with "$[A]$1 + $[B]$2"" to column formula, it will give error unless you remove all the "$" in the formula. this should be changed.

WisethK

Allow a Cell formula to be changed to a Column Formula when an $ (absolute references) is specified in the formula.

Annaleah Morrow

We regularly need to repeat one value (like 'industry') to appear on each row of our sheets so that when we pull data together we can slice and dice more easily. These are often hidden columns so they don't annoy our clients. The challenge is that we would like to take the singular reference of 'industry' from our project intake or metadata sheet, and you cannot make a column reference refer to a frozen cell. Please consider lifting that restriction to allow us better reporting capabilities.

Dani_torrone

Currently when you want to reference a single cell in another row you cannot convert the cell formula to a column formula without getting a syntax error even though the formula works fine as a cell formula.

I need this functionality as I am trying to figure out a work around to do round robin auto assignments for new items based off of who was assigned to the last item.

In my screenshot below, I have added a helper column called "previous onboarder" which I want to reference the previous row's "Onboarder" value so I can setup an automation to auto-assign an Onboarder to the next new item based on the "previous onboarder" value. My automation would say something like, When Request Number is changed and previous onboarder is Matthew Hayes assign Onboarder to Danielle Torrone. While single cell references are unable to be converting to column formulas I cannot think of another way to achieve the round robin assignment.

Quick Links

Trending ideas

Automations Tracker
I have a workflow with nearly 40 automations, half of which are email-based requests for updates with various sets of triggers. While I know the automations work, due to months of testing, when our system went live to the institution, I became very worried that the automations wouldn't run and I'd have no idea whether or…
Preserve Original Filenames for Multi-Photo Uploads in Smartsheet Mobile App
Description: When users upload multiple photos in one action via the Smartsheet iOS mobile app, all files are assigned the same generic name and timestamp. This makes it difficult to identify individual photos and creates confusion in workflows that rely on accurate, unique file naming. Why This Matters: Industry Standard:…
Joyful Animations - in Smartsheet!
I am proposing bringing a bit of pizzazz to Smartsheet by having celebratory animations for certain criteria. Think Joyful Animations in Outlook! My team was hoping to have some sort of confetti pop/gif/image when we close out a project or hit a milestone. Bonus points if it is configurable like the automations are.