Results 1 to 15 of 15
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Managing named ranges (2000)

    I often work with projects in which there are several categories of items, one category per sheet in a workbook. This makes things fairly easy to manage. Now there is a requirement to break some data into sub-categories, within each sheet. I considered using named ranges as a first thought, but while these seem like a good idea for static data, they seem to be problematic for dynamic data, where records might be added and deleted from ranges in an unpredictable manner.

    The problem appears to be keeping track of the beginning and end of each named range. I've seen a few formulas using =OFFSET to describe the range, but they don't seem conducive to keeping track of changes the way I'd like. Another thing I might need to do is to add a new named range somewhere in the middle of a sheet, or delete an existing one altogether. This made me think of maintaining a hidden sheet containing an inventory of all the current range settings, but the whole idea is starting to sound a bit messy.

    Any general ideas on how to approach this please?

    Alan

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Managing named ranges (2000)

    Could you be a little more specific about what you are after?

    I have used offset and additionally indirect and count to make dynamic named ranges that can move to different sheets and adjust themselves to the length and width of the data.

    You don't have to name everything, you can get by naming only what you need at the time, the range can be very flexible with combos of these functions.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    OK, a hypothetical example might be 3 category ranges on a sheet, with each item record being a row:
    Cat1 rows 1-8
    Cat2 rows 10-16
    Cat3 rows 19-29
    (note some blank separator rows)

    A user might delete 2 rows from Cat1, add 6 rows to Cat2 and delete a row in Cat3. This would then change the first and last rows defining the start & end of each named range. They might add a new Cat2A named range between Cat2 and Cat3.

    Is there a way for each named range to always (automatically) reflect the intended contents of the range, or must each range be manually redefined/ readjusted each time changes like the above are made? In the case I'm thinking of, named ranges are the way in which searches are scoped, so it would be desirable to retain them as the method of caterorising records.

    Alan

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    Have you tried it? The ranges should adjust dynamically to account for deleted/inserted rows. The one thing that you can't do is add a row to the front or end of a range and have it included in the range. For example, in your definition, if you add a new row 9, Cat2 will be adjusted to rows 11-17, and Cat 3 will be adjusted to rows 20-30, but Cat1 will still be rows 1-8.
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    What you could do is have three blank rows between each named range (and one above the top one). Include the first and last empty row in the named range. Then hide those rows. Now when the user inserts a row, the row will always be within the named range.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    I think you only need 2 blank rows between the ranges. And then like Jan said, make your named ranges include a blank row at the top and at the bottom. I typically apply some sort of color or shading to those blank rows to try and warn another user not to type anything into those rows. If you color them black, it's unlikely that someone would go to the trouble of changing the color to type their new entry in there.

    One problem I have with the "hidden rows" suggestion that Jan made is that I don't think it will let someone easily insert a row at the bottom of a range.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    <hr>One problem I have with the "hidden rows" suggestion that Jan made is that I don't think it will let someone easily insert a row at the bottom of a range.
    <hr>
    Well spotted! Best to hide ALL rows below the last range then.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    Thanks for the memory jolt there Leagare. Remembering back to this project, which has resurfaced, it was exactly what you describe to be the problem (the start and end of the named range). And that is what remains the problem, with users expecting insertions to magically slot into the range they envisage. This was why, from memory, blank rows were inserted between ranges - to at least give an indication of which range the new row was intended for.

    Alan

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    Thanks to both Jan and chipshot for your replies. The hidden row idea is certainly something I hadn't thought about, but with the limitation of adding something below the last row, this could be a problem. One of the requirements is to be able to create new named ranges, either in between existing ones, or more likely, appended to the bottom of the sheet.

    I had gotten to the stage of considering protecting the whole sheet(s) and running the whole show from a custom toolbar, with menu commands like:
    <big>Insert</big>
    New Row
    Above
    Below
    New Range
    Above
    Below

    Still, I'll have a play with the information I've gleaned from this thread and see what I can do. I'd much rather keep it as simple and "familiar" as possible in this instance.

    Alan

  10. #10
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    <hr>Well spotted! Best to hide ALL rows below the last range then.<hr>
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    I'm not sure what you mean by this, Jan. This is what I meant. Let's say you have a blank row 1, and then data in rows 2-10, hidden rows 11-12, and data in rows 13-20. Since only rows 10 and 13 are visible, you can't easily add a line of data between row 10 and row 11. If you keep the rows visible and just shade them, you can select row 11 and just insert a row.

    [edit]Jan, after scratching my head a bit, I understand your comment now. I think my method solves this problem also because the line below the last data line on the sheet would also be shaded and hopefully users would insert data above this shaded line.[/edit]

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    <hr>If you keep the rows visible and just shade them, you can select row 11 and just insert a row<hr>
    That would work quite nicely. yes.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    Hi sdckapr:

    re: "I have used offset and additionally indirect and count to make dynamic named ranges that can move to different sheets and adjust themselves to the length and width of the data."

    This sounds like what I'd like to do in vba code. Can you please tell me how? I'm trying to programmatically create a user-defined custom chart for each sheet in a rather large workbook. The chart has 5 series, so requires 5 ranges. Each sheet is set up the same way (it contains a matrix exported from Matlab), so I know the columns and row numbers for the beginning of each range. I can use OFFSET and COUNTA to get the bottom of the range, but don't know how to "reuse" a named range on different sheets (if this is possible).

    Is there a way to pass a variable (say for the active sheet, or a string with the sheet name) to the RefersTo part of a named range so it will adjust itself? And, if so, is there a way to save the charts once generated so they don't update to the wrong data when the sheet name is changed in the dynamic range? Or, do I have to create named ranges for each sheet? Five named ranges for each of possibly a hundred or more sheets is a lot of names.

    Thank you, in advance, for your help.

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    If you use something like this:

    =OFFSET(INDIRECT("A1"),0,0,COUNTA(INDIRECT("A:A")) ,COUNTA(INDIRECT("1:1")))

    And define a global name with that formula, then it should refer to a dynamic table on each sheet where the name is used.

    This works for me. After defining this name, when I'm on any sheet and hit F5, type the name and click OK, I get taken to the appropriate area on that sheet.
    What I don't know however, is whether a chart based on such a name will have an inkling that it has to use the data from the sheet the chart lives on. Up to you to try I guess.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Managing named ranges (2000)

    It can be done but it is a little tedious to set up.

    You create the named ranges on a Sheet and create a chart on the sheet with them

    If you copy the sheet, the local names will be created (automatically) on the new sheet, but the chart will be linked to the global name not the local name. The source data in the chart can be edited to replace the global name with the local name, by replacing the workbook name with the sheet name (must be done for each copy of the sheet)

    This seems to be the easiest way [TIP: To make it less tedious, create short sheet names before editing. They can be renamed later and the info in the charts will be updated]

    If you try to "fool" XL and copy the sheet after linking it to the local names, new local names for the copied sheet will be created, but the source data will no longer be linked to anything, the data becomes a matrix of the actual values, since it can no longer link to the source it does a Paste values. [which would require creating the links to the local names rather than the editing them as in the above.]

    Steve

  15. #15
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Managing named ranges (2000)

    Hi Jan and Steve:

    Thank you both for your helpful responses. Here's how I did it in code:

    1. Established dynamic named ranges for XValues, YValues and sName, using OFFSET, COUNTA and INDIRECT
    i.e., for column 5, starting on row 11, with 5 cells above that row not empty:
    =OFFSET(INDIRECT("$E$11"),0,0,COUNTA(INDIRECT("$E: $E") )-5,)
    2. With the data page selected, passed the ActiveSheet name to my vba code to create the chart
    3. Dimmed 3 local ranges, for XValues, YValues and series name
    4. Dimmed 3 local names, one for x, one for y, one for sname
    5. Set the local names to the dynamic, named ranges
    6. Stripped off the initial = from the names and put them into strings
    7.Using the ActiveSheet.Name argument, set each range to its active sheet and range:
    Set nmx = application.names("PF_X")
    strX = Right(nmx,len(nmx)-1)
    Set rngX = sheets(strSheet).Range(strx)
    (same for y and series collection name)
    8. with cht.seriescollection(2).
    .XValues = rngx
    .Values = rngy
    .name = rngn.value
    end with

    Same for the other 4 series.

    This way, each chart is linked to its own data sheet, so if I need to update them, I can.
    Works like a charm!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •