Excel Forum



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.