Excel Cascading Drop down list with tables across multiple pages

0
votes

I am unable to create dependent drop downs/ Cascading drop downs in excel.

I have data in tables in multiple pages in an Excel work book. Every table has its name. for the current scenario the data is like this...

There are 3 tables in 3 pages with below TableName. First row of every page is a blank row with out data.

  1. Account
  2. Project
  3. ProjectAllocation

Account Table 

AccountID        AccountName        ShortName 
----------------------------------------------
101              Account - 1        Acnt - 1
102              Account - 2        Acnt - 2
103              Account - 3        Acnt - 3
104              Account - 4        Acnt - 4

Project Table

Project-ID      Account         Project-Name
--------------------------------------------
105            Acnt - 1         Project - 1
106            Acnt - 1         Project - 2
107            Acnt - 2         Project - 3
108            Acnt - 3         Project - 4
109            Acnt - 4         Project - 5

Allocation Table

ID       Account       Project          EmpID
----------------------------------------------
1        Acnt - 1      Project - 1       101
2        Acnt - 1      Project - 1       102
3        Acnt - 1      Project - 1       103
4        Acnt - 1      Project - 2       104
5        Acnt - 2      Project - 3       105
6        Acnt - 3      Project - 4       106
7        Acnt - 4      Project - 5       107

Now in the Project Allocation table...

I can successfully created drop down for the Account to select among the available options from "Account" Table.

I want to populate the drop down in Project Allocation table in such a way that all the projects should drop down corresponds to the Account which I select.

I tried to create a data validation but the drop down has become read only or I ended up with an error (REF, Value etc.).

I watched so many videos in YouTube where they tried to achieve this task with in a single page but not using multiple tables from multiple pages.

Any help in this regards is much appreciated.

1
excelexcel-formula

1 Answers

0
votes

This solution assumes your tables are sorted in the following way. They have to stay sorted, or be re-sorted when new data comes in, for this to work.

  • Account > By Account Name
  • Project > By Account
  • Allocation > By Project

exactly as appears in your sample data.

Having a Summary sheet like below

enter image description here

Enter the following Named Ranges scoped at the Workbook Level:

  • account: =OFFSET(account!$C$1,1,0,COUNTA(account!$C:$C)-1,1)
  • project: =OFFSET(project!$C$1,MATCH(Summary!$C$2,project!$B:$B,0)-1,0,COUNTIF(project!$B:$B,Summary!$C$2),1)
  • allocation: =OFFSET(project!$C$1,MATCH(Summary!$C$2,project!$B:$B,0)-1,0,COUNTIF(project!$B:$B,Summary!$C$2),1)

Then (using the sheet above), the following data validation:

  • C2 = account
  • D2 = project
  • E2 = allocation