Hello,
Does anyone know why (Extract) shows up in a formula? I used the advanced filter to copy unique records from a table. I wanted to see the largest field length so I type =Len() and used the arrow keys to navigate to where the newly extracted unique records are. Instead of the cell range inside the parentheses saying (F1) the word Extract is now there so it looks like this =Len(Extract). Does anyone know why this is happening? Or what purpose it serves? Thanks.
-Scott
Hi Scott,
The advanced filter is creating a defined name "Extract", where the results of the advanced filter will be pasted.
In excel, by default, when a selected range has a defined name, the range address will be automatically replaced with its name.
In defined tables formulas, range references (also known as "explicit references") are also replaced with structured references. Only automatic table references can be disabled from using them in formulas, from File-Options-Formulas-Use Table Names in formulas. For named ranges, the only way to avoid the automatic replacement of explicit references with defined names is to manually type the range reference, instead of selecting the range.
The advanced filter is actually using 3 defined names: Criteria for the criterias range, Database for the range to be filtered and Extract for the range where the results will be pasted. If you define these 3 names before using the advanced filter, they will automatically show up in your Advanced filter dialog window, in their corresponding fields: Criteria box, List Range and Copy To, you will not need to indicate them again. From what I remember, only the Extract name will be automatically created, not the other 2 names.
Named ranges are far more flexible than explicit references (and the formulas are more "readable"), once you get used to them you will find them very useful.
I know someone that has over 70.000 defined names in a single file 🙂
Thank you Catalin! I'm well versed in defined names and table structure but I guess I just never noticed the 'Extract' defined name before. That was very informative, thanks for the help.
-Scott
