0

Sample Data (real data can vary in # of rows):

Department  Team        First Name  Last Name   Hire Date
Sales       Team SA     Sara        M           1/1/2020
Sales       Team SA     John        A           1/1/2020
HR          Team HA     Mike        S           2/1/2020
Marketing   Team MA     Elizabeth   L           2/1/2020
Sales       Team SB     Rob         S           1/2/2020
Sales       Team SC     Janet       G           1/3/2020
Marketing   Team MA     Rob         M           2/1/2020
Marketing   Team MB     Kenneth     P           3/1/2020

Goal:

3 drop downs.

1st drop down (level 1) = Unique list of "Department" members

2nd drop down (level 2) = "All Teams" + unique list of "Team" members based on "Department" filter selection

3rd drop down (level 3) = "All Names" + unique list of "First Name"&"Last Name" (concatenated) members based on "Department" & "Team" (all or specific) filter selection

I have never used google sheets.

What I have done is created another sheet (called "Validation") In there, I have created three fields "All Departments" (unique(Department field)), "All Teams" (unique(Team field)), and "All Names" (unique(First Name field)) - haven't figured out how concatenation will work when querying the above table using SELECT statement.

On the third sheet, I've added 3 drop downs on the first row based on values from "Validation" sheet. However, they're independent of each others. I need them to dynamically show options (All + Choices) for the 2nd and 3rd filter).

My end goal is to show a dynamically filtered table of data from the main data sheet below the drop down row on the third sheet with some additional calculated fields. Also, if I add records to my main data table, it should automatically be included in the filter and output.

Community
  • 1
  • 1
007
  • 2,136
  • 4
  • 26
  • 46
  • This is NOT a trivial exercise for someone who has never used Google sheets and the question [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/a/261593/1330560) might well apply to you. There are many precedents on the net and a few on Stackoverflow that it appears you have missed during your research. Personally, I like the videos by ChicagoComputerClasses. However, does [How do you do dynamic / dependent drop downs in Google Sheets?](https://stackoverflow.com/a/35456798/1330560) suit your needs? – Tedinoz Mar 23 '20 at 21:58
  • Thank you @Tedinoz for your response. I am playing around with GS for a pet project of mine. I've watched quite a few videos and browsed SO as much as I could, but could not find anything that does what I'm trying to do. Thank you for the reference link, however I'm trying to have only one cell per filter. That example (as well as their example on YT) are for multi-line per filter (https://sheetswithmaxmakhrov.files.wordpress.com/2018/01/ddl11.png). I've been approaching it differently now (FILTER fx), but running into issues clearing the selection on the parent filter's edit. Thanks again. – 007 Mar 24 '20 at 00:38
  • _"All Departments" (unique(Department field))_ This sounds like it looks up the target sheets, and creates a unique list that must be used by the Target sheet - circular reference?? Anyway, I would suggest that make this validation list should be a hard-entered list of values. – Tedinoz Mar 24 '20 at 00:43
  • I gave up on that approach in favor of https://www.youtube.com/watch?v=iYcZU1UzTmE. Now, I'm trying to figure out how to clear out child/grandchild filter(s) selection as soon a parent/grandparent filter's selection has been modified. I would also like to "default" to "All" for each filter if, possible, not sure if GS has that capability. – 007 Mar 24 '20 at 00:48
  • 1
    FWIW, As I mentioned earlier I really like ChicagoComputerClasses - he has a video entitled [Google Sheets - Dependent Drop Down List for Entire Column, Multiple Levels](https://www.youtube.com/watch?v=s-I8Z4nTDak&t=941s). I highly recommend it; best I have seen. – Tedinoz Mar 24 '20 at 01:16
  • I watched that video earlier but decided to navigate to a diff video since it was working with drop-downs for multiple (fixed) number of rows. Decided to give it another shot... and the light just blinked... I can do the same thing for just one row dropdown.. wow! thanks again. I'll delete this soon. – 007 Mar 24 '20 at 03:07
  • okay - I think that I misread your question. You just want a dropdown in a given cell in a given row. Anyway, looks like you're on the right track. – Tedinoz Mar 24 '20 at 03:47
  • Hi @007! After reading your conversation in the comments I see that you are about to solve the issue. Nevertheless, if you still need some extra hands, please share the code that you already have so we all can take a look. – Jacques-Guzel Heron Mar 24 '20 at 11:55
  • Thank you guys. I followed that video and got it to work as I wanted. Appreciate your help. – 007 Mar 24 '20 at 20:58

0 Answers0