Issue Converting Formula to Column Formula

vzarzycki · 12/13/23

Hi --

I've hit a wall with this formula and have no idea how to tweak it so that I can convert it to a column formula. I have 2 sheets - one is the master/current sheet and the other is the archive/previous sheet. I have an automation that will copy selected rows to the archive sheet when triggered and then clear the current quarterly fields. I also have columns on the master/current sheet that provides the prior quarter values on the master/current sheet for reference when a user is completing the current planning columns.

For the life of me, I can't seem to figure out how to make this so I can convert it to a column formula. The formula works in the individual cell and returns the expected value, but I just can't convert it to a column formula. I get that error that says the Syntax isn't quite right.

=IFERROR(INDEX(COLLECT({Copy of Archive Sheet (V2) Range 1}, {Copy of Archive Sheet (V2) Range 2}, $ID@row, {Copy of Archive Sheet (V2) Range 3}, $[Prior Quarter]@row), 1), "N/A")

Any help would be greatly appreciated! Thank you!