User FAQs

If you have a question, please send it to us.

1) What are the advantages of using named ranges ?
You can insert or delete cells, rows and columns and the cell references remain intact.

2) How many named ranges should a workbook have ?
The more you have the more memory Excel needs to process the file.
10,000 or less would be recommended
20,000 would be excessive
40,000 would seriously impact performance
60,000+ would cause your file to crash

3) What is the difference between a Workbook Level named range and a Worksheet Level named range ?
Workbook Level - These can be referenced from any worksheet in the workbook, without prefixing.

=Name_Wbk 

Worksheet Level - These are intended to only be used on one specific worksheet although you can reference them from other worksheets by prefixing the name of the worksheet.

=Sheet1!MyWorksheetLevel 

4) What type of named range is created when you use the Name Box ?
Typing into the Name Box creates a named range with workbook level scope.
You can create worksheet level scope if you proceed it with the name of the worksheet and an exclamation mark.

5) What is a dynamic named range ?
A dynamic named range expands automatically when more data is added to the range.
This type of named range uses a combination of the worksheet functions OFFSET and COUNTA.

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$1:$B$100),1) 

6) When you copy (or move) a worksheet across to a different workbook, do all the named ranges (wsh + wbk) get copied across ?

7) Write the code to populate an array with the values in a named range ?
The following lines are all equivalent.

Dim arValues As Variant 
arValues = Range(NamedRange).Value 
arValues = Application.Evaluate[NamedRange].Value 
arValues = [NamedRange]           '// since value is the default  

8) Where do the named ranges that have an "IQ_" prefix come from ?
These prefixes are automatically created as part of the Capital IQ Add-in.

9) Where do the named ranges that have a "wrn." prefix come from ?
These prefixes were automatically created as part of the Report Manager Add-in.
This add-in was added in 1997 and removed in Excel 2002.

10) Where do the named ranges that have a "_bdm." prefix come from ?

11) Where do the named ranges that have a "TRNR_" prefix come from ?

12) Where do the named ranges that have a "C_" prefix come from ?