I’m working on a spreadsheet project and need some help. I’ve got two tabs: one for contacts and another for updates. The contacts tab has two columns: company and employee name. Now I want to set up the updates tab so that when I pick a company from a dropdown in the first column, the second column shows a filtered list of only the employees from that company. I’ve tried looking for solutions but haven’t found anything that fits my needs perfectly. Is there a simple way to create this kind of dependent dropdown? I’m not great with formulas, so any tips would be super helpful. Thanks!
This is definitely doable in most spreadsheet applications. You’ll want to use a combination of Data Validation and the FILTER function. First, create a named range for your company list in the contacts tab. Then, in the updates tab, use Data Validation to create a dropdown from this range for the company column. For the employee column, you’ll need a more complex Data Validation setup using FILTER. The formula would look something like:
=FILTER(EmployeeRange, CompanyRange=A2)
Where A2 is the cell with the selected company. This will dynamically update the employee list based on the chosen company. It might take some tweaking depending on your specific setup, but this approach should get you on the right track. If you’re still struggling, there are plenty of tutorials online that walk through this process step-by-step.
hey, i’ve done something like this before. u could try using INDEX and MATCH functions. basically, set up ur company dropdown first. then for employees, use a formula like:
=INDEX(employee_range, MATCH(1, (company_range=selected_company)*(ROW(company_range)=ROW()), 0))
it’s a bit tricky but works great once u get it right. good luck!
I’ve tackled a similar challenge in my work. What worked for me was using a combination of named ranges and the INDIRECT function. Here’s what I did:
First, I created named ranges for each company’s employee list on the contacts tab. For example, ‘CompanyA_Employees’, ‘CompanyB_Employees’, etc.
Then on the updates tab, I set up the company dropdown using Data Validation as usual. For the employee dropdown, I used this formula in the Data Validation:
=INDIRECT(A2&“_Employees”)
Where A2 is the cell with the company name. This dynamically references the correct named range based on the selected company.
It’s a bit of initial setup, but once done, it’s easy to maintain and works smoothly. Plus, it’s less complex than some other methods I’ve seen. Hope this helps!