Excel Forum
Similar Content
Enter a Constant Value in a Defined Name in Excel - Text, Numbers, Formulas, Etc.
Tutorial: In Excel you can store values in Defined Names.  Often people use a Defined Name to refer...
Change Formulas to Absolute or Relative References
Macro: This macro will change the formulas in cells to absolute or relative. It allows you to cha...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...



Excel Magic Trick 709: Defined Name Dynamic Range Formula w Relative References & Data Extract


See how to create an Excel solution that allows user to type in an area code and retrieve phone numbers of all suppliers in the area code. See these Excel tricks:
1. Create A Defined Name for dynamic Range using Relative References, not Absolute References.
2. Create Counting formula that will count phone numbers in a given area code
3. Create Array Formule for eaxtracting phone numbers and supplier data.
4. See the functions: OFFSET, COUNTA, SUMPRODUCT, SEARCH, IF, ROWS, INDES, SMALL, ROW

Download file: http://people.highline.edu/mgirvin/ExcelIsFun.htm





Got a Question? Ask it Here in the Forum.