Does this YouTube tutorial help at all? It shows how to adapt formulae using the OFFSET function to avoid the #REF! error.

https://www.youtube.com/watch?v=L_rRC9plsTU