Data Validation Dependent Lists with errors

Question

I am trying to create a Data Validation Dependent List in B19 that populates based on the values selected from a DV list in B18. In the above picture you can see that I have a list of Committees in row 1.  I have highlighted the entire row (22 committees in total) and created a defined name for them (Committees).  I was able to make the DV Dropdown list in B18 containing a list of a Committees in row 1, with out problems.  Now the problems arise when I try to create Data Dependent lists.  First I highlight each committee name in row 1 and drag down to the end of the Subcommittee lists.  I then hold control and do the same for all 22 committees. After I have down that I then CRTL-SHIFT-F3 and select top row to create a defined name per committee name with all subcommittees selected. The first problem I come across is that excel doesn't like the spaces between the words in the Committee names and inserts an underscore in replace of the space.  That then causes problems when I use the indirect function to create the data dependent list in  B19. Once I select a committee name (with no blanks) from dropdown list in B18, the list in B19 doesn't populate.  Now I have tried to insert underscores in the Committee names prior to naming the range and that still doesn't permit populating the dependent list below. I need try and remove all underscores and spaces in the committee names and the defining the name range, that then allowed for me to have the correct listing in B19, but the Committee name in B18 looks horrible.

I hope I have explained this with enough details for someone to assist.

Thank You!

Answer 1

Let's try to walk through it on the forum, to (eventually?) provide the full solution to other people who might have to solve similar challenges in the future

There was a missing parens near the end (that's the risk of aircode in forums)

OFFSET(A1,1,MATCH(H1,1:1,FALSE)-1,COUNTA(OFFSET(A1,1,MATCH(H1,1:1,FALSE)-1,100,1)),1)

Looking back at your original screenshot, there is one other concern (although it may not be critical to you). The CountA function will count the number of cells in that column that are not blank, and that is how many cells will be included in the drop-down list. Because your "working area" is in A and B below your data range, those will get picked up by the CountA.

The impact is that when someone selects Agriculture, they will have two blank options at the bottom of the dependent list (the range starts at the top and includes blank cells, whereas the CountA ignores blank cells).

When the user selects Appropriation, they will have one extra blank.

That really only matters if you want to prevent them from selecting blanks, but if it does matter to you then I'd suggest changing the location of those selection lists so they are not within the columns of data that the formula can reference

Answer 2

I am still getting a #Value! error in B19. Can you please supply your email and I will send you the entire file for you to play with.  My ultimate goal is to use the data on Sheet 6 because it has columns for SUBCOM CODE and MAJORITY and MINORITY, as the Data Dependent List (B19) based on selection in (B18). 

Thanks Again

Brandon

Answer 3

The error was mine- I was filling in the Offset parameters from memory and accidently switched the last two. The parameters should be =offset(rows, columns, height, width)

Try this instead:

=Offset(A1,1,Match(B18,1:1,false)-1,CountA(offset(A1,1,Match(B18,1:1,false)-1,100,1),1)

Answer 4

Thanks a bunch for the help, however it didn't quite work as wanted.  the DV list in B18 works, but the Subcommittee list in B19 now populates with the cell values of row 2 all the way across.  In the above photo, the committee names are in bold on row 1 and the subcommittee names begin on Row 2 and go down. So either I did the formulas wrong or maybe I explained my goal poorly.

TIA

Brandon

Answer 5

Here is how I would approach this- showing you as multiple steps (helper cells) so you can follow the logic:

B18= DV list set to a fixed range of A1:V1

C18 =Match(B18,1:1,false)     C18 tells you the number of the column you need to use for B19

C19=offset(A1,0,C18-1)        gives you the actual header cell of the correct column

C20=CountA(offset(A1,1,C18-1,100,1) is an extension of C19 formula and tells you how many items are on that sublist

B19 DV: = Offset(A1,1,C18-1,1,C20)

however, if offset isn't accepted in the DV, you may have to make this a named range, then plug that named range in as the DV source; that means you may also have to add the sheet reference for these ranges (test it out)

So to put all of that into one named range without the helper cells, just replace each cell reference with the formula in that cell, working backwards. Each underlined item is replaced with the cell formula in the following line, shown as bold text.

=Offset(A1,1,C18-1,1,C20)

=Offset(A1,1,C18-1,1,CountA(offset(A1,1,C18-1,100,1))

=Offset(A1,1,Match(B18,1:1,false)-1,1,CountA(offset(A1,1,Match(B18,1:1,false)-1,100,1))

HTH