WorksheetVariable/NamedRanges (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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(Sheet1!$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

Reply | Quote

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,COUNTA(INDIRECT(“$B$2:$B$13”))),1)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote