Hi Jeesh,
Congratulations on finding a solution to your issue.
Cell references in formulas may be 'relative' or 'absolute.'
The Absolute reference operator ( $ ), placed before the column reference keeps the reference on the same column when the formula is filled right or left into new columns or copied, then pasted into a different column.
Placed before the row reference, the operator makes that part of the reference absolute, and keeps the reference on the same row when the formula is filled or copied and asted t a different row.
Without the Absolute reference operator, Numbers will automatically adjust the column and row reference values to maintain the referenced cell in the same position relative to the cell containing the new copy of the formula as it was in the original cell. A reference, for example to the cell three columns to the right and on the same row as the cell containing the original formula will point to the cell three columns to the right and on the came row of its new cell when filled to or copied and pasted to a new cell. If that new cell happens to be in one of the last three columns of the table, the formula will throw a #Ref error because the reference is to a cell beyond the last column of the table, and there is no cell there.
Regards,
Barry
PS: further congratulations on completing your trial and error tests, finding an answer, and posting a note on the solution, all within 24 minutes of asking the question! Very speedy.
B.
PPS: Thanks for posting the solution. Having done so, you should now mark your reply (NOT you initial post) as having Solved your issue. You won't get points for it, as you were the one asking the question, but in will help anyone searching for an answer to a similar question by making this one easier to find.
B.
