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

To clarify, are you asking to put the subcommittee list in the rows below the DV (or wherever) in addition to populating the DV itself?

If so, then use the same part of the formula that tells us which column represents the selected committee =MATCH(H1,1:1,FALSE)

[with whatever page references you need to add to the ranges, since your DV is now on a different page it will probably be something more like:

=MATCH(H1,Sheet1!1:1,FALSE)

Then in the cells where you want the list to show up, the easiest way is something like:

H2= offset(Sheet1!$A$1,row(H2)-1,MATCH(H1,Sheet1!1:1,FALSE))

where row(H2)-1 means that we know the subcommittes start on row 2, so use the row number of where you want the subcommittees to show up to determine the row number you are pulling from. The number you subtract should be one less than the row number of H2 (if you are instead starting the list in row 5, then you would use -4)

When you drag that formula down, you should get each of those values from the subcommittee. Since some of the source cells are blank, everything past the last entry may show up as zero (IIRC), you don't want to add blanks to the source cells because that will throw off your CountA. My preferred solution is to just wrap this formula in an IF statement once you have it working;

=IF([the whole formula above]=0,"",[the whole formula above]). That isn't efficient from a processing perspective, but it does work. Just fill that formula down to at least the max number of rows you might need (the max number of subcommittees under a single committee)

Answer 2

Works like a charm now that I moved the DV lists to a separate sheet.  I am now moving on using the index and DGET functions to get more information for my project.  Another question would be, do you know of a way to now populate the Subcommittee list into the cells below the Committee name obtain selecting the Committee from a DV?

Thanks