Formula Suggestion: [Column Name]@ to Imply "[Column Name] in This Row"

John Sauber

In Excel 2010, a simple and powerful feature was added to formulas used within tables: the @ prefix. All @Column1 means is this row in Column 1. It greatly simplifies the reading of formulas, and I would wager that accross the entire spectrum of Smartsheet users, most formulas are referring to cells within their own row. This makes the readability of formuals much better. Additionally, we have had problems when we move rows around in our most complex sheet, which has a nuber of complex formulas, some of which are over 200 characters in length. In addition to the complex formulas, the sheet is over 1300 rows and is 30 columns deep, and we're approaching the technical limits of the application. This is in part due to the massive amounts of row reference updates needed to be made in our formulas. If the @ symbol were used instead of the row number, those references would not need to be updated at all, because our formulas only refer to cell data for their own row.

 

In short, [Column1]@ in row 385 makes more sense to me than [Column1]385, and seems more scalable and user-friendly.

 

Of course, I'm making major assumptions in terms of technical complexity to implement this, but I thought it was worth sharing to the Community. I have already submitted this to techinical support as an enhancement request, but I wanted others Community members to have the opportunity to consider this request, and possibly submit it as an ehancement as well if it seemed like it would be beneficial.

 

Thanks! 

JohnHinkle

I am trying to test this out on Excel:Mac 2011 and cannot get it to work. Could you could provide an actual formula that works in Excel using this function? 

John Sauber

I don't know about the Mac version. This function works on cells which are a part of a table in Excel, and they're called "Structured References." Later on, the [#This Row] function was reduced to a simple "@" symbol to accomplish the same task (documneted here).

 

I really feel that for formulas refering to data in their own row, @Start reads a hell of a lot better than Start1253. I am actually surprised and disappointed that this discussion didn't garner more discussion. 

Daniel Stein

Hi John,

We're currently looking into a simpler way to reference data in the same row, and I saw that you had suggested exactly this. Besides the simpler notation, it would also provide performance benefits to users, because formulas wouldn't need to update as often.

I wanted to get your latest thoughts on the notation you would expect to see for this. Are you still a fan of [Column name]@ as the notation? What about @[Column name]? Or is there another notation you would prefer to see?

Thanks,

Daniel

Product Manager, Smartsheet