2

I have two tables Stores and Employees, Where each employee belongs to a store.

I also use another table where i can input data, what I'm trying to achieve is when you select a store in the first column then only the employees from that given store will populate in the second dropdown. I have googled searched and all i seem to come across is the INDIRECT function which means i have to format my employees/store table differently.

Is there another function (or VB) i could use that will look at the table and cross compare the store and return only the employee names. Thank you for any help, it is appreciated.

LiamJ
  • 37
  • 4
  • It's very complicated, though feasible with formulas only (my solution would be to use a hidden worksheet to generate intermediate results). However It's much simpler using VBA. Is VBA really not an option for you? – A.S.H Jul 05 '17 at 00:56
  • Can't you formulate a named range in the name manager (Ctrl+F3) then indirect with those? – Glitch_Doctor Jul 05 '17 at 08:13
  • Yes i can use VB ... to an extent – LiamJ Jul 05 '17 at 12:06
  • The named range would force me to create a range for every single store?and to add a new store i would have to create a new named range, rather than having a single function which searches the employees/store table – LiamJ Jul 05 '17 at 12:07
  • try [this](http://www.contextures.com/xlDataVal15.html) approach : – Phil Jul 05 '17 at 12:21

1 Answers1

1


It is a bit longwinded. Here is a sample file with the solution.

You have to create a matrix of named ranges in a separate sheet then create the named range for the first dropdown from your column A. Then horizontally you need to add in the list for the second dropdown for each "option" in column A.
You need to name those ranges as well.
(You can do that by selecting the whole matrix and use Formulas-defined ranges-create from selection tool. Just make sure you create it from left column. That way your you first list will become the name for the subsequent horizontal ranges)
Then all you need to do is to substitute the spaces out of the names of your first list
When excel will create the ranges of your first column the spaces will be replaced by underscores: named range will become named_range)
Then wrap it in an indirect.
enter image description here

enter image description here

Hope this helps

Thomas
  • 48
  • 5