3

I have a live DB sheet in Excel, and I want to create a sheet where I can fill new rows using dropdown lists based in the DB sheet.

In other words, I have Client | Product | Requester | Piece/Requirement columns, then I want to fill each cell based in the one on the left (dropdown lists) without using VBA or INDIRECT function. Here is a sample DB:

enter image description here

Does anyone have a clue how to make it?

Improve this question
5
  • 1
    If you want to avoid INDIRECT() then you have to use a helper sheet. A sheet which will hold filter formulas in each row, so when you will select an item in your calculation sheet, then the helper sheet's formula will return depended values. And second dropdown will refer that calculated cells using cell refence with hash (#) operator. Commented Oct 18 at 2:12
  • @Harun24hr That is a valid answer. Are you planning to post it as an answer (with formulas and process ofcourse) becuase if you are not then I will. My answer is ready. Commented Oct 18 at 5:57
  • @SiddharthRout Please post as answer. Commented Oct 18 at 6:27
  • Thank you @Harun24hr, but it doesn't solved my problem. The solution should be like if lists (in data validation) allow to use filter, unique and sort functions. However, I've already solved it, but not in the way I was looking for. If have sometime, I will post my solution. Commented Oct 18 at 18:46
  • In case you are interested, I developed an Excel add-in which allows this sort of filtering without a helper sheet. It also enables doing it in both directions. You can find it here: appsource.microsoft.com/en-us/product/office/… Commented Oct 30 at 19:49

1 Answer 1

Reset to default
5

I usually do not post an answer when there are no efforts shown by the OP, but this is one of those unique cases where the question itself is interesting, the solution is a bit complex, and it can help a lot of future visitors. Dependent dropdowns are a very common query, and there is a clean way to handle them in Excel 365 without using VBA or INDIRECT.

You can achieve this using the new dynamic array functions like UNIQUE() and FILTER().

Here is how I would set it up.

As per your image, your database is in B2:E32, and your dropdowns are in H3:K3.

enter image description here

Now, create helper formulas. For example, in columns M to P.

M3 -> =IFERROR(SORT(UNIQUE(FILTER($B$3:$B$32,$B$3:$B$32<>""))),"")
N3 -> =IFERROR(SORT(UNIQUE(FILTER($C$3:$C$32,$B$3:$B$32=$H$3))),"")
O3 -> =IFERROR(SORT(UNIQUE(FILTER($D$3:$D$32,($B$3:$B$32=$H$3)*($C$3:$C$32=$I$3)))),"")
P3 -> =IFERROR(SORT(UNIQUE(FILTER($E$3:$E$32,($B$3:$B$32=$H$3)*($C$3:$C$32=$I$3)*($D$3:$D$32=$J$3)))),"")

enter image description here

Screenshot for the first one is shown above. Similarly do it for the rest.

Next set your Data Validation lists as follows:

H3 -> =M3#
I3 -> =N3#
J3 -> =O3#
K3 -> =P3#

enter image description here

Screenshot for the first one is shown above. Similarly do it for the rest.

Explanation of # used in Data Validation List:

The # symbol in Excel is called the spill range operator. It tells Excel, Use the entire range of cells that this formula spills into.

If we take the example of A3, we have =M3#

This means Refer to all the cells that are part of the spilled array from M3, however many rows that happens to be.

So if next month your database grows and there are five more clients, your formula automatically spills further down, and the dropdown still captures the new names, because M3# expands with it.

In short:

M3 = just the top cell of the spill.

M3# = the entire spilled result of that formula.

In Action

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

You are right, this is a very common query. This will help other visitors I believe. I posted a comment on Microsoft techcommunity, that why MS Excel team doesn't update the validation list capability to support dynamic array formulas so that we do not need to use any helper column or named range. But no luck yet. Hope MS Excel team will upgrade the feature in near future.
I think (and I could be wrong) but they are busy with Copilot and other AI enhancements.
Yes, those are under testing. Significant improvement is partial word searchable dropdown in Excel-365.
Awesome @Siddharth Rout, this is very like the method I used. I'll check in depth to see if it can improve mine. Thanks for sharing the process!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.