Absolute Reference Changing

L_123 ✭✭✭✭✭✭
edited 11/21/22 in Formulas and Functions

Hi Guys,

I'm having an issue with an absolute reference changing, I submitted a support ticket, but I wanted to ask here as well

So I have a sheet that is submitted to via form adding rows to the top. The operator puts values in the form, and formulas return RGB if the values are good. I want to pull all the "Red" values from a single row into a results column as seen below:

To do this I'm using a collect formula similar to below:


The issue is that the $4 doesn't hold. After about 15 submissions, the $4, which should be $19, is instead $15 or some other number. I could put the values in another sheet, or use some more complicated formulas to parse it out, but I'd rather not have to do that. I have about 80 unique sheets with 50 or so columns on each of them, so keeping it easy is optimal.

I've been dealing with this for a few months, thinking it was a temporary issue, but it hasn't resolved itself. Maybe i'm doing something wrong, or have a bad assumption that the absolute reference should hold.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!