0

The title explains it all pretty much, but I will add more details + my test sheet to show:

Test sheet here:

https://docs.google.com/spreadsheets/d/1iTF_5WXjIjoyqb1WfqSLeWxn-DoNTVd7mOqKHCWmmOM/edit?usp=sharing

We are counting tiles for inventory. In an effort to get employees to be even more accurate, we want to allow them to choose the 'LOT & SERIAL #' based on a validation rule from he sheet next to it, which is our database.

A simple data validation rule like the one I placed on column E for 'PRODUCT CODE' would look into the entire column for 'LOT & SERIAL #', but I want to know if it's possible to have the 'LOT & SERIAL #' drop down menu look into column 'C' of our database sheet, AFTER they have chosen a 'PRODUCT CODE'(left side) and to show the possible matches ONLY to that 'PRODUCT CODE' chosen in the column to the left? A drop down menu with input rejection would be ideal. I will insert a 'NOT FOUND' value for the ones they can't find.

Any help with this would be match appreciated!

Cheers,

braX
  • 11,506
  • 5
  • 20
  • 33
Nicolas Salazar
  • 13
  • 3
  • 1
    What you are asking for is the **"Dependent drop down lists"**. Plenty of answers around. Please have a look at [this one](https://stackoverflow.com/a/35456798/1527780). – marikamitsos Jun 12 '20 at 16:50
  • I saw what you added. I really appreciate it but it's not what I needed help with. The vlookups work just fine. I just need to offer a list of 'LOT & SERIAL #' based on the selection the user made for "PRODUCT CODE' for that row. This is like a filtered result based on what they selected already, so they don't see ALL the results for 'LOT & SERIAL #'. This is some sort of a dynamic query, but I can't find a proper way to do it and it is driving me crazy! please help! – Nicolas Salazar Jun 14 '20 at 21:35
  • *"I really appreciate it but it's not what I needed help with. The vlookups work just fine."*. I know they do. I just showed you a way on how to make you sheet lighter and **replace your 17730 formulas with just 3**. As for help on answering your question, please try the answers from my previous comment as well as related ones found on the right sidebar. – marikamitsos Jun 14 '20 at 22:25

1 Answers1

1

A way to do this without script is using INDIRECT.

In another sheet, hidden column, etc:

=INDIRECT(cell where there's first Data Validation)

This will list the Named Range under the name of the cell where there's the first dropdown list.

Now make a Named Range with this new range, let's call it Dependent List for clarification, and make it into the Data Validation in the cell where is the second Data Validation.

Now in the second Data Validation it will appear a dropdown list with whatever values are in Dependant List.

This method works better when there's need of only a few dependent dropdown lists.

Vitali
  • 11
  • 2
  • Could you show me that in the test sheet I provided? I am kind of new to Google Sheets. I understand the concept, just not exactly how I would insert it into my sheet. I do have another sheet which is the database sheet, which contains both of the values I am trying to filter out 1 step at a time. – Nicolas Salazar Jun 14 '20 at 19:23
  • @NicolasSalazar Made an example in Simple Dependent Dropdown tab inside your spreadsheet with detailed explanation, hope it helps – Vitali Jun 15 '20 at 13:17