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 ?
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext