ActiveCell References, COLUMN and ROW Functions

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.

Sign in to vote!

Find more posts tagged with

Formulas

functionality

Idea Submitted

Comments

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.

Quick Links

Trending ideas

Resource Management Plan Settings in Control Center - add Client and Project Code fields
In the Resource Management Plan Settings in Control Center, there is no way to link the Client and Project Code fields even though they are not Custom fields and part of the first 3 fields you enter when creating a new project. I can't select them in the Custom Resource Management Information Fields because they are not…
WorkApp Analytics
I’d like to see user analytics for WorkApps. I’ve checked the user report available in the Collaborator Pack, but it only shows how many times someone has clicked into the WorkApp each month. I’d like to know: Which resources (dashboards, sheets, forms, etc.) are getting the most traffic? Where are people spending their…
Sheet Icon Color Consistency
The 'Sheet' icon appears purple in the left navigation but blue within the sheet, creating inconsistency. Aligning icon colors across the platform would improve clarity and simplify user training. Please standardize the icon color to enhance user experience and reduce confusion during onboarding.