PDA

View Full Version : [SOLVED:] Properties of Named Rectangles


Cyberdude
04-23-2005, 09:27 AM
In thread 2906 TheAntiGates made the comment
Define a named range over that rectangle, and refer to that in the code, if there's a chance the dimensions will change. I need an expansion of that comment. I thought when you named a range, you would be specifying limits on the range. How can the rectangle be expanded? What am I missing? :bug:
Jacob Hilderbrand
04-23-2005, 09:55 AM
Well, you can actually define a Named Range that is Dynamic and will expand with the data.

Or suppose you refer to a certain range several times in your code. If that range needed to be changed, you would have to change it in several spots, but if it was a Named Range, you could just update it once.
Cyberdude
04-23-2005, 10:38 AM
Hi, DRJ!
I do understand the merits of naming the range. My problem comes in understanding the dynamics. Suppose I have a named range defined as A1:A3. From what you have said, then I would expect that inserting a new row following row 1 would create a new definition for the name, which would now be A1:A4. The previous A2 and A3 would now be A3 and A4.
Now lets go back to the original definition being A1:A3. What are the effects of inserting a new row A4 following the original A3? Does the definition become A1:A4?
Instead of inserting a new row, suppose we just add data to the existing A4, which would be outside of the original definition A1:A3? Does that force a change in the definition to include A4?
You can see why I'm confused by changing the dimensions. Or were you guys saying I can go in and manually change the definition of the name to extend the defined array, and it won't have an impact of any formulas referring to that name? Ahhhh, that's what you meant, I'll wager. Duh.
Jacob Hilderbrand
04-23-2005, 11:21 AM
Inserting a Row will change the Named Range.

Or you can use a Dynamic Range that will automatically update as new data is added.


=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

This will refer to the range A1 to A and the last row used in Column A (usuming that no cells are left empty).
Cyberdude
04-23-2005, 04:16 PM
Yes, I'm familiar with the dynamic range. I use that on most of my chart's Series formulas. I'm still learning how to think intuitively about arrays in general. I figure, maybe in about two to three years, I'll start getting the hang of it. (Sigh)
Thanx for the reply.
Jacob Hilderbrand
04-23-2005, 04:19 PM
Glad to help. :beerchug:

Take Care