Absolute Reference Changing

L_123

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:

=join(collect([Test1]$4:[Test3]$4,[Test1]@row:[Test3]@row,"Red"),char(10))

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.

L_123

Figured it out, didn't realize the effect of move workflow on an absolute reference.

L_123

Figured it out, didn't realize the effect of move workflow on an absolute reference.