First of all, you should make sure you know WHY you're getting the #REF error, so that you're not just putting a bandaid on a problem that needs surgery.
Second, if you know the answer to that first question, it sounds like a job for the INDIRECT function. You can read up on that here.
