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) 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 

3) 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.

4) 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) 

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

6) 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  

7) 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]].

8) 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.

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

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

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