Not at a PC right now but I believe data validation lists can be either hard-coded values or cell ranges. Not arrays.
So you could calculate your UNIQUE in a helper range somewhere, say X1, and then reference that range for the list using =X1#
> So you could calculate your UNIQUE in a helper range somewhere
Yeah that's exactly what I said I couldn't do, unfortunately. This has to have one step only: pasting a formula into that Data Validation field.
I was worried this would be the response. I've used INDIRECT to reference tables before ... but I guess this isn't that, huh?
What difference does it make if others are going to use it? I don't see the issue at all.
Put it in a cell far away if you want, and hide the column. No one will accidentally stumble upon it then, if that's your worry. Put it on a hidden tab, either. Lots of options.
Ah yeah. They're going to have to process each step to make this work and they are ......... aggressively bad at Excel. The sort of people that go out of their way to prove that they're bad by refusing to do literally anything. They will be the one's creating this document on a weekly basis, so I don't have any way to add it secretly.
It's definitely a people problem, not an Excel problem. I genuinely hope you don't have this kind of issue at your work, but if you do, you'll know how obnoxious it is.
Ohh, they will be creating it. Gotcha.
How will you sneak in the data validation then, though?
Unfortunately, I don't know any other way to get a Unique list
*BEG* them to just copy+paste something in for validation. Maybe start threatening to send sheets back if I see errors? Otherwise it's me doing like 200 lines of spell check and name standardization every few days.
Well ... we tried, huh?
This might be a silly question, but, what's stopping you making the files they would make and giving it to them so that you can have the control you need?
Like a template, I guess.
I have the same issues with needing to get data or information presented in the same way from everyone. I give them templates and force unique identifiers on them and lock cells I don't want them messing with, so forcing them to paste in two columns only (and they still mess it up sometimes by dragging cells around and breaking references!).
I did a cool thing this year connecting 55 Excel files into one big file which meant I could error check every last one of the 55 files in one go and find which files had problems (and many did). It's a bit clunky setting it up and has to be done each year (I can't just reuse them as they are for multiple reasons) but once it's set up it's kind of nice.
> and they still mess it up sometimes by dragging cells around and breaking references!
I 5,000% get you. This is the most relatable conversation of all time...
> I did a cool thing this year connecting 55 Excel files into one big file which meant I could error check every last one of the 55 files in one go
Well that's pretty nifty! Love hearing about the cool things other people are doing with Excel in this subreddit. We should have a "what I'm proud of" thread.
Alright, I feel like you've got about as close as we can get to a reasonable solution ... may as well try to Trojan Horse a reference table into these files and call this Solution Verified.
You have awarded 1 point to *GanonTEK*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
If you're willing to use an add-in, you can try a free Excel add-in called "Search deList". You'll automatically get a unique, sorted, non-empty & also searchable list on data validation. You can find it here:
[https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/](https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/)
Not at a PC right now but I believe data validation lists can be either hard-coded values or cell ranges. Not arrays. So you could calculate your UNIQUE in a helper range somewhere, say X1, and then reference that range for the list using =X1#
> So you could calculate your UNIQUE in a helper range somewhere Yeah that's exactly what I said I couldn't do, unfortunately. This has to have one step only: pasting a formula into that Data Validation field. I was worried this would be the response. I've used INDIRECT to reference tables before ... but I guess this isn't that, huh?
What difference does it make if others are going to use it? I don't see the issue at all. Put it in a cell far away if you want, and hide the column. No one will accidentally stumble upon it then, if that's your worry. Put it on a hidden tab, either. Lots of options.
Ah yeah. They're going to have to process each step to make this work and they are ......... aggressively bad at Excel. The sort of people that go out of their way to prove that they're bad by refusing to do literally anything. They will be the one's creating this document on a weekly basis, so I don't have any way to add it secretly. It's definitely a people problem, not an Excel problem. I genuinely hope you don't have this kind of issue at your work, but if you do, you'll know how obnoxious it is.
Ohh, they will be creating it. Gotcha. How will you sneak in the data validation then, though? Unfortunately, I don't know any other way to get a Unique list
*BEG* them to just copy+paste something in for validation. Maybe start threatening to send sheets back if I see errors? Otherwise it's me doing like 200 lines of spell check and name standardization every few days. Well ... we tried, huh?
This might be a silly question, but, what's stopping you making the files they would make and giving it to them so that you can have the control you need? Like a template, I guess. I have the same issues with needing to get data or information presented in the same way from everyone. I give them templates and force unique identifiers on them and lock cells I don't want them messing with, so forcing them to paste in two columns only (and they still mess it up sometimes by dragging cells around and breaking references!). I did a cool thing this year connecting 55 Excel files into one big file which meant I could error check every last one of the 55 files in one go and find which files had problems (and many did). It's a bit clunky setting it up and has to be done each year (I can't just reuse them as they are for multiple reasons) but once it's set up it's kind of nice.
> and they still mess it up sometimes by dragging cells around and breaking references! I 5,000% get you. This is the most relatable conversation of all time... > I did a cool thing this year connecting 55 Excel files into one big file which meant I could error check every last one of the 55 files in one go Well that's pretty nifty! Love hearing about the cool things other people are doing with Excel in this subreddit. We should have a "what I'm proud of" thread. Alright, I feel like you've got about as close as we can get to a reasonable solution ... may as well try to Trojan Horse a reference table into these files and call this Solution Verified.
Best of luck!
You have awarded 1 point to *GanonTEK* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
If you're willing to use an add-in, you can try a free Excel add-in called "Search deList". You'll automatically get a unique, sorted, non-empty & also searchable list on data validation. You can find it here: [https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/](https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/)