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!