• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation Using the Offset Command

L

leimst

Member
Hello,


I am using an Offset command in the "Refers To" line of the Name Manager in order to create a dynamic Named Range, e.g. =OFFSET('Dropdown info'!W$2,0,0,COUNTA('Dropdown info'!W:W)-1,1). However, I originally input the formula as =OFFSET('Dropdown info'!M$2,0,0,COUNTA('Dropdown info'!M:M)-1,1). I can't seem to figure out why the column keeps changing on me because even when I change it back to column "M" it somehow mysteriously changes itself back again when I'm not looking!!! :)


Any help would be greatly appreciated!


Leimst
 
What are you doing in between the time when it changes? Are you selecting any part of the range and dragging it?


Upload a file if possible
 
Unfortunately, I'm at work and they do not allow us access to any of the upload sites. The only thing that I can tell is that I have 4 or 5 such Named Ranges and by the time I get done setting up one, the others have changed. Do I need $ signs in my formula somewhere?
 
@leimst


Take away the zeros!!!! in the formula. Just use

=OFFSET('Dropdown info'!W$2,,,COUNTA('Dropdown info'!W:W)-1,1)
 
Let me know if it works Leimst!

You can use Zero's because it tells excel the reference your looking for is not real. Then excel freaks out.
 
leimst,


Yes, w/o the $ signs, the formula is being calculated relative to whichever cell is active when you enter the name manager. Needs to be in front of all the column references, like so:

=OFFSET('Dropdown info'!$M$2,0,0,COUNTA('Dropdown info'!$M:$M)-1,1)


@Montrey

Whoa, 5 posts in under 10 minutes (on this thread)! Someone's eager to get home to their new game. =)

Also, the 0's were fine, as they are used in the offset arguements. As we don't need to offset, we can put 0, or leave it blank (which gets counted as 0 or FALSE, depending on context).
 
@leimst


Take away the zeros!!!! in the formula. Just use

=OFFSET('Dropdown info'!W$2,,,COUNTA('Dropdown info'!W:W)-1,1)
 
Mystery solved and thank you so much for the help!! I was actually having that same problem on another spreadsheet as well so that fixed them both!


Thanks again,


Leimst
 
You must log in or register to reply here.
Back
Top