self-building range of unique values


I need a named range to build itself with unique values, as those values are entered into a separate place.  So if I have an 'definitions' tab, where I've set some named ranges, all of which are defined with offset functions driven by COUNT in the column, I need that space to become populated as the main page gets expanded.  In the attached, assumed the column in 'data being entered' will be part of many other things being entered, and the unique subset of values in those areas will need to be used as defined ranges for other things. 


Any idea on how to get the definitions tab to build itself out, with unique values?  I know filtering for unique values is one options, but that doesn't happen real-time, as the 'data being entered' is populated (meaning I need to do the activation of the filter each time in order to get the new unique values.  I was hoping there was some sort of array function, which knows to return the unique values.


In Google Sheets, there's a 'pastedata' sort of function, which real-time reacts to the sources of where that data is entered.  That's something like what I'm after...just don't know how to do in Excel.


3 Replies

One way is by using a pivot table. See attached.

Oh, and if you're on the latest version you can use =SORT(UNIQUE(TheRange)) to get a sorted list of unique items.

If you are on Office 365 subscription and wait for a few more months then new array functions will be available like UNIQUE(),


Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies