0

I have a table where I want users to fill values off a list using data validation. The content of the validation list should be based on values entered in two other tables which are defined as dynamic named ranges (say list1 and list2). I need my validation list (say listAll) to update automatically whenever values are changed or added in list1 or list2.
For example: enter image description here I could easily achieve this with a macro triggered by changes in list1 or list2, but in this particular case using VBA is not allowed. I can define a named range which simply REFERS TO: =list1, list2. This does indeed create a virtual combination of the lists but only usable in functions like count or sum. Using the combined list in data validation results in an error message:

The list source must be a delimited list, or a reference to single row or column.

From similar questions here and here and here and here It seems that previously there was no solution for this (without VBA), but I thought that it might be solvable with newer excel functions.
I have found a solution which I will post below, but it is cumbersome and I'm hoping someone can come up with something simpler and more elegant.

eli-k
  • 10,898
  • 11
  • 40
  • 44
  • Use a third range (helping range) that will merge that two list using formula and then use that 3rd range as validation list. – Harun24hr Jul 18 '22 at 04:59
  • @Harun24hr that is sort of what I did in my solution, the question is if you have a simpler way to merge the two lists in a third range while still accounting for the two original lists being dynamic and with varying lengths, plus of course not getting a bunch of blank cells in the merged list. – eli-k Jul 18 '22 at 06:20
  • I think `TEXTJOIN()` and `FILTERXML()` can merger two different list into one easily. – Harun24hr Jul 18 '22 at 06:26
  • @Harun24hr, `TEXTJOIN` may be unavailable. For example, I don't have such a function on my PC. – ENIAC Jul 18 '22 at 06:37
  • @ENIAC are you on MAC. `FILTERXML()` is not available on MAC excel. – Harun24hr Jul 18 '22 at 06:40
  • @Harun24hr, no, I'm on Windows. – ENIAC Jul 18 '22 at 06:42
  • I actually have TEXTJOIN on windows (excel 365) but can't seem to make it do the trick (result is not accepted for data validation) – eli-k Jul 18 '22 at 06:48
  • @eli-k You can't use these dynamic formula directly to data validation list. You have to use in a cell first then refer that cell with hash `#` operator. Suppose you put formula to `B1` cell then use `=B1#` in data validation list. – Harun24hr Jul 18 '22 at 06:57
  • @ENIAC `FILTERXML()` is available to Excel-2013 and later for windows. – Harun24hr Jul 18 '22 at 06:57
  • @Harun24hr, yes, I have `FILTERXML` function but I don't use it (even don't know how to use it). Thus, I've provided my own solution below. – ENIAC Jul 18 '22 at 06:59
  • @ENIAC `FILTERXML()` is a excellent formula. Read this post by JvdV to learn about FILTERXML. https://stackoverflow.com/q/61837696/5514747 – Harun24hr Jul 18 '22 at 07:05
  • @Harun24hr, I've skimmed through it and it says that the strings should be in valid `XML` format, which I don't plan to learn for now. Anyway, thank you for sharing that link! – ENIAC Jul 18 '22 at 07:39
  • Do you have VSTACK? https://exceljet.net/excel-functions/excel-vstack-function – Ike Jul 18 '22 at 07:46
  • VSTACK looks like it's made exactly for my needs, but won't be available for me or my users for a while... – eli-k Jul 18 '22 at 10:22
  • @Harun24hr, I haven't found a way to get data validation to work with the two lists using TEXTJOIN(), I would really appreciate it if you could post a solution with a description of what you have in mind. – eli-k Jul 18 '22 at 10:34
  • @eli-k can you show some of dummy data with location of cells in sheet? – Harun24hr Jul 18 '22 at 10:52
  • @Harun24hr see my edit with examle – eli-k Jul 18 '22 at 11:32
  • @eli-k Please see my answer and let me know your feedback. – Harun24hr Jul 19 '22 at 03:26

3 Answers3

2

Usually, I use an auxiliary range which combines user-defined lists. As, I don't have FILTER function in my Excel, I use a number of formulas to get the combined list:

  1. Combine both maximum possible length lists into one (column F).
  2. Determine a sequence number of user input values (column E).
  3. Number all rows in possible maximal list (column H).
  4. Check which sequence numbers exist (column I).
  5. Create a combined list (column J).
  6. Finally, create a named range.

Named Range Formulas

demonstration

ENIAC
  • 813
  • 1
  • 8
  • 19
2

Use below formula to merger two or many list into one using FILTERXML() and TEXTJOIN() formula.

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A2:A20,B2:B20)&"</s></t>","//s")

Then use # operator to refer that dynamic range into data validation list like =D2#. See the screenshot below.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • `#` doesn't work for me. `Excel` throws me an error. How do you make it work? – ENIAC Jul 19 '22 at 05:25
  • `#` operator will work only on Microsoft 365. Not earlier versions of excel. – Harun24hr Jul 19 '22 at 05:29
  • It works perfectly! I see that I can also use range names just as effectively. Pretty funny to go as far as translating it all to XML and retranslate back, and still a perfect one step solution... Thanks! – eli-k Jul 19 '22 at 06:55
  • 1
    @eli-k If you are interested to know more about `FILTERXML()` then can read https://stackoverflow.com/a/61837697/5514747 – Harun24hr Jul 19 '22 at 07:01
1

This is the solution I found – in three steps (did I say cumbersome?):

Step 1:
I call each of the lists, both in one column (D in example), one well below the other – to make sure the top one has enough place to "evolve".

Step 2:
I use the filter function on the original column to have the two lists appear in a column stacked on top of each-other:
=FILTER(D:D,D:D<>"") I tried entering this as the definition of a new name, but it is unusable for a data validation list ("evaluates to an error"). Therefore –

Step 3:
EDIT - following excellent improvement suggested by @Harun24hr:
I can now refer to the cell from step 2 directly in the data validation window, like this: =$F&1#.

enter image description here

Alternatively I can create a new name with the same reference, which I can use in data validation or for other uses. The new name is dynamically based on list1 and list2.

eli-k
  • 10,898
  • 11
  • 40
  • 44
  • 1
    You no need `=OFFSET(Sheet2!$B$1,0,0,COUNTA(Sheet2!$B:$B),1)` Just use `Sheet2!B1#` in data validation list which will refer filtered data dynamically. – Harun24hr Jul 18 '22 at 05:02
  • Thank for the suggestion - that's already a great improvement – eli-k Jul 18 '22 at 10:17