Active Cell and Summary

Aaron Bean

I would like to use the Sheet Summary to add a few key fields that reference different cells, depending upon what the active cell is.

For instance, when the active cell is C40R272, I want the summary field to return the value of C1R272; when active cell is C40R473, the summary field would return C1R473.

Similarly, when the active cell is C40R272, I would want another summary field to return C40R$7 and when it's C30R272, the summary field would return the value of C30R$7.

An ActiveCell function, which has been requested numerous times, along with a Column and Row function would be extremely helpful here.

This would basically allow me to view key field in the sheet for the row and column I'm adding values to, while inputting data. The alternative is constantly editing data, then scrolling up, then to the left to see the key values after the inputs are made.

Frank B.

To reference different cells in the Sheet Summary based on the active cell, you can use formulas that reference the active cell's location using relative cell references. For example, to return the value of C1R272 when the active cell is C40R272, you can use the formula =INDIRECT("C1R" & ROW()).

Similarly, to return the value of C40R$7 when the active cell is C40R272, you can use the formula =INDIRECT("C40R$7"). To make this formula dynamic based on the active cell, you can replace "40" with the row number of the active cell using the ROW() function, like this: =INDIRECT("C"&ROW()&"R$7").

To make these formulas work in the Sheet Summary, you can use the INDIRECT function and reference the active cell using the formula =CELL("address",INDIRECT("RC",0)). This will return the address of the active cell in R1C1 format, which you can use in your formulas.

I hope this helps you achieve what you are trying to do in Smartsheet!

Aaron Bean

Thanks for the reply. Those functions are all returning an #UNPARSEABLE error.

To clarify, one problem statement is that 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 column2 of that row.

Andrée Starå

Hi @Aaron Bean

I hope you're well and safe!

Great idea! That would be a great addition to Smartsheet features.

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Frank B.

I hope you're well and safe!

Your solution is for Excel and not Smartsheet so it won't work, unfortunately.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!