Log In
Register
Home
VIP
April 21, 2015
Offline
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
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello Frans,
This is described in the blog article Excel Tables as Source for Data Validation Lists.
Br,
Anders
Answers Post
Trusted Members
October 17, 2018
Offline
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)
Trusted Members
October 17, 2018
Offline
Take a look here.
Add a new item to your table and check if the dropdownlist is updated
VIP
April 21, 2015
Offline
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
VIP
April 21, 2015
Offline
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
Trusted Members
October 17, 2018
Offline
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
Moderators
January 31, 2022
Offline
@Hans
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.
1 Guest(s)
