self-building range of unique values

Brass Contributor

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.


Thanks.

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(),