Hi all,
It's been some years I was here the last time. Maybe that's over now, now I have more time for Excel issues.
I searched for the answer to my question, but that's not easy here on the forum, so I made an example where I explain what the situation is and what I want to achieve: using the table from a data validation list in the reference.
I hope somebody here knows the answer or can provide other help.
thanks in advance!
Frans
Hello Frans,
This is described in the blog article Excel Tables as Source for Data Validation Lists.
Br,
Anders
Hi Frans,
What's the Excel version you're using?
You could add a dynamic named range to include the new data you enter in the table using the UNIQUE and SORT functions (Excel 2021 or 365)
Take a look here.
Add a new item to your table and check if the dropdownlist is updated
Thanks Anders and Hans (also in the Netherlands I see :-))
The information Anders gave with this link helps a lot. It gives also the explanation why in the original file it didn't work: the Excel table was on a different sheet.
And Hans provided the working example (although I have to test in in the original file with the refering table on a different sheet, but otherwise another option of Mynda's wil work I think).
Thanks again both of you, it is always very nice to receive good help and so quickly!
Frans
Two more things:
1) The issue is also adressed by Mynda in her blog (and that's from 2013!) and still not really solved I see
2) Hans asked my version: Microsoft365
And I see a little sneeky part of your solution Hans: the use of 'offset' for making a range. In most of the cases this will work, but for me it's nicer when the solution is 'complete usable in every case'. But this is a minor thing.
thanks again both!
Frans
The offset part is what makes the validation list dynamic, you add a new item to the table it automatically updates it.
I've been using this
method for dynamic lists for more than 15 years and it hasn't let me down (yet)
To avoid OFFSET, in modern Excel you use the # sign to refer to an entire spilled array. So, in your example you can refer the named range Items_List to:
=Blad1!$M$2#
Or just point the data validation list to =Blad1!$M$2# or =Blad2!$M$2# if that happens to be the sheet where the list sits. So, using a Named Range isn't really needed either, although it could make it easier to maintain in case you have many different DV lists, all over the place.
