Enable Column Formulas to Be More Flexible

EV_1

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:

Identify cell instance count

Dave Schierman

Jul 12, 2021

I’m looking for a way to identify a duplicate cell value in a row so I can mark it for removal to another sheet for archiving. Rows are entered via forms at the top of sheet that contain a “Product Name” and it’s status. I am referencing this from another sheet to find the current status of the product so I just need the latest entry for each product but would want to keep a few previous as well. Ideally, a formula that would denote 1st, 2nd, 3rd etc row instance.  Hopefully that makes sense.  Thanks

---

View Post

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.