Share via

Excel - Dependent dropdown lists

Anonymous
2021-08-04T00:48:46+00:00

Hi guys,

I am trying to create a sort of flow chart using dependent dropdown lists.

I have managed to get 3 dropdowns working, but I am struggling with the 4th dropdown.

In this example A2=ProductList

B2=INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"List")

C2=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

I am trying to now add a fourth column now to determine the Region of the where the fruit came from e.g. Mac Apple from Portugal, however I am receiving an error when trying the below formula.

D2=INDIRECT(VLOOKUP(C2,INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup),2,0),2,0)&"Lookup")

Portugal and Italy have been set up as a lookup table with Maclist - called MacLookup

I am unsure as to why I am receiving this error, and would appreciate any help I could get with it.

Thank you

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Andreas Killer 144.1K Reputation points Volunteer Moderator
2021-08-04T10:16:14+00:00

Hi Peter,

please download this file

https://www.dropbox.com/s/hwi91qpjgh2nb8j/Cascade%20ComboBox.xls?dl=1

I copied your example into my example file for cascading combo boxes.

BTW, it also contains a VBA solution for different scenarios and all data can be loaded dynamically from a Setup sheet.

In row 1 you can see the used names inside the name manager, in row 2 are the formulas to build the strings, inside the name manager they are surrounded with an INDIRECT.

IMHO this setup is much easier to follow.

Andreas.

Was this answer helpful?

1 person found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-08-30T02:28:27+00:00

    Thank you so much for your help Andreas.

    I went with the VBA option.

    I have no experience in writing VBAs, so your example was such a big help.

    Really appreciate it!!!

    Thanks,

    Peter

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-08-06T09:03:11+00:00

    Hi Peter,

    May I know whether there are any updates to your question? If you have any questions, please feel free to contact us.

    Tin

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-08-04T04:09:20+00:00

    Hi Andreas,

    Thank you for your reply.

    I have pasted the link below.

    https://1drv.ms/x/s!Au9KJXdfT8o4mW3pAONYEJVXcKjp?e=JAPTj6

    Thanks,

    Peter

    Was this answer helpful?

    0 comments
  4. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2021-08-04T03:53:06+00:00

    We need to see your file.

    Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
    Click Upload in the top and choose your file.
    After uploading, right click the file and choose Share.
    Click Copy Link in the lower left edge (no need to enter an email).
    Copy the link and paste it here.

    Andreas.

    Was this answer helpful?

    0 comments