ActiveCell References, COLUMN and ROW Functions

Aaron Bean

I want to make a sheet summary field return whatever is in row 6 of the activecell's column. It has to dynamically change with the cell's position.

Similarly, based upon the activecell's row, I want to return the value of Column 2 of that row.

Excel has a feature for ActiveCell which then you can use Offset or Index to return differnt columns or rows from the cell that is active.

We need this functionality in Smartsheet.

DW1

"ROW" - used to reference the sheet row number that can be referenced in other formulas. Something like '=ROW@row'.

Many use cases for this function, but as an example:

When using dependency enabled project sheets, the SUCCESSORS function will show the tasks that can start after the dates for a given predecessor task. I need to index/match against the values returned by the SUCCESSORS function and report what dependent tasks can start if the predecessor task is marked complete, regardless of planned start/finish dates.

MichaelTCA

I am currently trying to "merge/list" multiple columns into 1 long list. One way I have done this through excel is an advanced INDEX/SMALL/IF/ROW and so on.

The ROW/COLUMN function is not in Smartsheet and I think it would be very beneficial to be able to make lists or transpose a row into a column, and vise versa.

Another way I have done this through excel is through Data Queries. I'm not sure if Smartsheet offers something similar?

I definitely have not found a way to write macros into Smartsheet (don't have access to API), but that would probably be another option if I had the capabilities.

My project is developing a report involving 2 sheets. I can add 2 columns with the same name, but my goal it to combine it into 1 so that the grouping is accurate and not separated per sheet.

If there is already a way to do this, please direct me in the right direction.

Otherwise I think it's a GREAT idea to either add the functions I suggested or a feature within reports that simplifies this task. Maybe If the columns have the same name, you give the option to merge and automatically create the list within 1 column so that the grouping is actually accurate across multiple sheets.

Adrian Mandile CHESS

Hi @MichaelTCA,

Reports will not 'merge' Columns if the Column Type is different in each underlying Sheet, even if they have exactly the same Column Name. This is worth checking. Also, if it is different, your 'Assigned To' columns would also potentially appear twice in the Columns (to show) option when defining the Report.