Need help with referencing a cell to change within a formula

jeff.katz

Greetings,

First time poster, short time user😊 I have a question/issue I am hoping someone way smarter than I can help me resolve. I am in the build process for a bunch of input sheets and some of them have hundreds of unique items (not abnormal), but I am trying to figure out how to reference them, so I do not have to add them as part of each calculation in each column and then for each row as the ID changes.

Each ID in the Primary Column is unique for each from and is being referenced as part of the calculation but changes with each row

Hopefully, once one of these is built, it can be used as a master for others to more easily copy and later on. I hope my question makes sense.

Paul McGuinness

Hi @jeff.katz

The best way to do this would be to utilise sheet summary cells https://help.smartsheet.com/learning-track/level-2-intermediate-users/sheet-summary

I use these when I may be referencing the same detail over and over across many columns or formulas, or where the reference may be prone to change.

You can have up to 200 sheet summary cells, and each cell can be up to 50 characters in length.

If utilising a lot of these summary cells I would suggest making a separate sheet that holds an index of these references with a shorter ID

i.e. sheet looks like this

Formula in summary cell looks like this

=INDEX(Contents:Contents, MATCH("Sumcell1", [Summary cell reference]:[Summary cell reference], 0))

This would allow you to share the unique references while maintaining a true list of them and not have formulas fall over due to incorrect entries.

May be a bit of a more involved set up but once you have created it initially it should save you a lot of time down the line.

Hope that helps

Paul McGuinness