Cascading Lists

[ExcelN00b1]

I'm trying to create cascading lists in a Workbook I created yesterday. I followed the steps here: https://www.ablebits.com/office-addins-blog/2014/09/30/dependent-cascading-dropdown-lists-excel/

This works for the first row, but I need to copy this down the column to n rows. My sheet has 2 free text fields, list (parent list), list (child list), list, list, free text field and will have n rows.

So the first row has cascading lists, but the subsequent rows do not.
I tried making the function dynamic so I could drag it down as many rows as I need, but Excel won't allow me to save my changes, it claims there's an error in the function.

I tried two different approaches, based on what I learned in the tutorial I cited.

=OFFSET(INDEX(Departments,1,MATCH('Source Code'!$C$2,Entities,0)),0,0,COUNTA(INDEX(Departments,,MATCH('Source Code'!$C$2,Entities,0))))

=INDEX(Departments,1, MATCH('Source Code'!$C$2,Entities,0)) : INDEX(Departments, COUNTA(INDEX(Departments,,MATCH('Source Code'!$C$2,Entities,0))), MATCH('Source Code'!$C$2,Entities,0))

My thought was that if I reference the cell, instead of the named reference, that I could copy it down the column and the cell numbers would increment. But I can't even save this in the data validation dialog.

How can I accomplish this?

 

[Rijnsent]

Hi ExcelN00b1,
could you post a workbook (link, e.g. dropbox or google drive)? Just from your formulas it's hard do deduce what's going wrong.
Cheers,
Koen

 

[ExcelN00b1]

Hi ExcelN00b1,
could you post a workbook (link, e.g. dropbox or google drive)? Just from your formulas it's hard do deduce what's going wrong.
Cheers,
Koen

Ok, so I saved a copy of my Workbook, stripped out all of my data and replaced it with generic data. Now the cascading lists are working EXACTLY like I want them to in that copy of the Workbook - the one with the meaningless data.
Here's a link to that file: https://www.dropbox.com/s/u8be8971a3fynuv/Example - Copy.xlsx?dl=0

Here's the original Workbook, the one with meaningful data, and broken cascading lists. I didn't really want to share this copy, but I'm starting to think the issue is in the data, not the formula.
https://www.dropbox.com/s/27zeg85ejngefl7/SourceCode_20150828 - Copy.xlsx?dl=0

 

[Rijnsent]

Hi ExcelN00b1,
I fiddled a bit and found 2 issues with your validation formula:

=OFFSET(INDEX(Departments,1,MATCH('Source Code'!$C$2,Entities,0)),0,0,COUNTA(INDEX(Departments,,MATCH('Source Code'!$C$2,Entities,0))))

-Firstly: $C$2 implies that as you drag/copy your formula down, it will keep referring to cell C2. Changing that to $C2 (for cell D2) helps
-Secondly: I tried a simple formula to start with =OFFSET(Departments,0,0,1,1), but that lead to an error. With a bit of googling I found that named ranges and validation don't always go together, when I changed that to =OFFSET(INDIRECT("Departments"),0,0,1,1) it worked again. Merging these two solutions into your formula leads to:

=OFFSET(INDEX(INDIRECT("Departments"),1,MATCH('Source Code'!$C2,Entities,0)),0,0,COUNTA(INDEX(INDIRECT("Departments"),,MATCH('Source Code'!$C2,Entities,0))))

Which works like a charm in both your workbooks, also when dragged down.
Cheers,
Koen