Share via

Excel dynamic named range corruption

Anonymous
2019-09-19T21:48:16+00:00

i have defined dynamic named ranges using offset function. Have also tried index and indirect in the named range definition but result is always the same - at some point (when i blink?) excel corrupts the range reference and inserts. #REF where the range row/column should be. Very frustrating. Has anyone else experienced this problem and, importantly, can anyone suggest a cause and solution?

example : =offset(‘sheet name’!$a$2,0,0,counta(‘sheet name’!$b:$b),-1)

becomes:

=offset(‘sheet name’!#REF,0,0 etc....

This course renders all reference in VBA to this defined range corrupt. Any ideas?

Martel

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-09-20T04:49:18+00:00

    Hi Martel

    Please try this formula 

    =offset(‘sheet name’!$a$2,0,0,counta(‘sheet name’!$b:$b)-1,1)

    I guess you forgot the last argument in your formula 

    Hope this helps you

    Was this answer helpful?

    0 comments