Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    WorksheetVariable/NamedRanges (Excel 2003)

    Was curious as to whether there was a limit to the number of Named Ranges that a workbook can accommodate? My research revealed a long list of new limits for Excel 2007 from Excel 2003, but nothing that addressed Named Ranges.

    Also, is it possible to create somekind of worksheet variable within a Named Range. That is, I am essentially referencing the same range in each worksheet, but I would rather reference a worksheet variable as opposed to creating a new named range for each worksheet.

    Example: I have a few named ranges referenced by charts on each worksheet--the following list is an example of one of those named ranges duplicated in each worksheet and referenced by a unique chart within each worksheet. For the following arrays, I would have to create a named range specific to each worksheet.
    =SUM(Sheet1!$A$2:$C$24)
    =SUM(Sheet2!$A$2:$C$24)
    =SUM(Sheet3!$A$2:$C$24)
    =SUM(Sheet4!$A$2:$C$24)
    =SUM(Sheet5!$A$2:$C$24)

    Is there a way to reference the worksheet where the chart is residing within the Named Range so I don't have to create a continuing list of Named Ranges that are all essentially the same?

    thanks
    Amy

  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: WorksheetVariable/NamedRanges (Excel 2003)

    AFAIK, in older versions of XL the number of names is only limited to available memory and I presume that it is the same for XL2007

    If you create the name as:
    =SUM(INDIRECT("$A$2:$C$24"))

    The sum will be based on that range in whatever sheet the formula is in.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorksheetVariable/NamedRanges (Excel 2003)

    Steve,
    Thanks.

    So for the following more complex array =OFFSET(Sheet1!$B$2,COUNTA(Sheet1!$B$2:$B$13)-MIN(4,COUNTA(Sheet1!$B$2:$B$13)),0,MIN(4,COUNTA(Sh eet1!$B$2:$B$13)),1)

    I would replace "Sheet1?" with INDIRECT() like:

    =OFFSET(INDIRECT($B$2),COUNTA(INDIRECT($B$2:$B$13) )-MIN(4,COUNTA(INDIRECT($B$2:$B$13))),0,MIN(4,COUNTA (INDIRECT($B$2:$B$13))),1)

    Please double check my parethesis if I got the logic of the formula correct.

    Amy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: WorksheetVariable/NamedRanges (Excel 2003)

    The argument to INDIRECT must be a text string, so you should enclose it in quotes:

    =OFFSET(INDIRECT("$B$2"),COUNTA(INDIRECT("$B$2:$B$ 13"))-MIN(4,COUNTA(INDIRECT("$B$2:$B$13"))),0,MIN(4,COUN TA(INDIRECT("$B$2:$B$13"))),1)

  5. #5
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorksheetVariable/NamedRanges (Excel 2003)

    FWIW, where I work we've encountered files with 30,000+ names; so long as they weren't corrupt names (#REF or #N/A errors within the definitions, or linked to other workbooks), they didn't seem to cause problems. And the bulk added wasn't too bad (again with corrupt ones it can be a different story). Not that there was any good reason for the files to have that many - they had been assembled from many other books, and the users have add-ins that add a huge amount of names to each file.

    So if you're going to delete a worksheet that contains a bunch of names, you might want to use Jan Karel Pieterse's Name Manager to get rid of the #REF error names after you do it. But if you stick to the method of Sheet Level names via INDIRECT that Steve suggested, that shouldn't be a problem either.

    Best,

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorksheetVariable/NamedRanges (Excel 2003)

    The named range reference with the INDIRECT text string is not providing the desired behavior with the data from each sheet--invalid reference of some kind. The attached workbook has 2 named ranges that I want to reference data of each sheet when it is used in the Chart Source Data.

    What am I doing wrong? Can this be done or am I just referencing incorrectly.

    Amy
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: WorksheetVariable/NamedRanges (Excel 2003)

    A chart doesn't necessarily look at the active worksheet to obtain its source data, so I don't think you can use sheet-independent names for the x-labels and y-values of a chart; they have to refer to a specific sheet. In other words, to use the names as source data for a chart, you'll have to define separates names for each sheet.

Posting Permissions

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