I have a named range of
=OFFSET('Student Information'!$B$8,0,0,Lists!$B$3,1).

When the row is shifted up in a later routine this range will change to
=OFFSET('Student Information'!#REF!,0,0,Lists!$B$3,1).

I need it to stay as the original. I have no idea why this is working like this.

  • I was under the impression that when you lock the cells with the $ it wouldnt change. – Tom Smith Jan 11 at 11:36
  • Sorry to say, if your job really depends on fixing one Excel formula, then you seriously need to reconsider your situation and look for alternatives. It might just happen any day you get a task you can't solve regardless whoever you involve. – Máté Juhász Jan 11 at 11:52
  • You are probably right. – Tom Smith Jan 11 at 12:04
  • I found a solution. Thanks anyway Mate. – Tom Smith Jan 11 at 12:44

I've tested your issue and for me it happens only if I delete 'Student Information'!$B$8, and that's the expected behaviour.

If I delete column A or rows 1:7, then reference changes accordingly (e.g. after deleting column A it becomes 'Student Information'!$A$8).

If you delete the referenced cell Excel doesn't know where do you want the new reference to be (if you want it at all), so it gives you error.

As a workaround you can use INDIRECT:
=OFFSET(INDIRECT('Student Information'!$B$8,TRUE),0,0,Lists!$B$3,1)
This will always refer to B8 regardless of the changes.

  • Yeah that is the issue, if I shift the first row of my database, which is B8 it will do this. Ive tried this now and it doesnt seem to work still. What am I doing wrong? – Tom Smith Jan 11 at 11:57
  • I just replaced it directly – Tom Smith Jan 11 at 11:58
  • Did you mean to use that as the named range in the name manager? Or something else? – Tom Smith Jan 11 at 12:16
  • INDIRECT isnt a function i have used before. – Tom Smith Jan 11 at 12:17

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.