Dynamic Range reference

Question

Dynamic ranges. Changed all dynamic range names to use ‘index’ instead of ‘offset’. Offset was reportedly causing #REF errors if rows deleted. That is not dynamic. Now using ‘index’ but excel still corrupting range references. Any help would be welcome.  very frustrating

Answer 1

If you can make a dummy workbook and upload, I can check.

Answer 2

Hello again and thanks. Sorry to say my hands are not too reliable and managed to insert an extra ‘:’ in the formula. The worksheet is using the correct formula.

i am therefore driven to suppose that there must be some other corruption buried in the workbook/worksheet, given that no one else is reporting an issue. The problem is not solved but not your fault. I could try copying everything into a new blank workbook but not convinced this would work - may copy corruption. Thank you for your time and input

Martelcs

Answer 3

Your formula has been working perfectly. I just changed :: to :

=Demographics!$J$2:INDEX(Demographics!$J:$J,COUNTA(Demographics!$H:$H))

Answer 4

Lost original reply Abridged version

Sample formula:

=Demographics!$J$2:INDEX(Demographics!$J:$J,COUNTA(Demographics!$H::$h))

Column J is gender. Column H is Surname (no blanks in H so used to get full row count).

sorry  cannot Send screenshot due to confidentiality issues and GDPR regulations.

Thanks for reply and interest

regards

martelcs

Answer 5

Hi martelcs

Greetings! I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Let me have a look at your formula and if possible a screenshot of your data as well.