Share via

EXCEL automatically changing reference point in my named range

Anonymous
2017-03-07T16:40:58+00:00

I am using this function to create a named range for a dynamic chart, =OFFSET(Brown!$B202,0,COUNT(Brown!$202:$202)-1,1,-12)

It works great until EXCEL changes what I enter to this =OFFSET(Brown!$B492,0,COUNT(Brown!$202:$202)-1,1,-12) or some other random number that replaces my reference point. My formula will work for a minute or two, then all of a sudden my charts disappear and I discover that the reference point has just changed, in all of my named ranges? Anyone have any idea why?

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

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-03-08T07:59:08+00:00

    Sorry for incomplete instructions...

    try

    =OFFSET(INDIRECT("Brown!$B202"),0,COUNT(Brown!$202:$202)-1,1,-12)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-03-07T17:06:42+00:00

    Where would I add additional dollar signs?

    =OFFSET(Brown!$B202,0,COUNT(Brown!$202:$202)-1,1,-12)

    like this?=OFFSET(Brown!$B$202,0,COUNT(Brown!$202:$202)-1,1,-12)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-03-07T16:54:01+00:00

    I guess, rows are getting inserted hence the reference is changing... adding a dollar will stop it from changing

    Was this answer helpful?

    0 comments