Copy and paste-able dependent dropdown list
Our take
I have two different setup sheets. First one is a table for my category and my subcategory. Second sheet is basically the same with different input, so not same (sub-)category. On my 3rd sheet i want to have different dependent dropdown lists.
E.g. sheet3: 1st row, left cell: dropdown category (sheet1), right cell: dropdown subcategory (sheet1)
2nd row, left cell: dropdown category (sheet2), right cell: Dropdown subcategory (sheet2)
Both subcategories should change individually depending on what I selected in their respective left cell.
After that I want them to be copy and paste-able in sheet3 while still having fully functional dropdown menus.
Some help would be highly appreciated. Nothing I tried so far has worked.
edit: my current solution that only works when I don't c&p is to have the first cells on sheet3 as the headers (categories) of the tables as data validation list and the 2nd cell as a formula like this for sheet1:
=SORT(UNIQUE(TOCOL(XLOOKUP(Sheet3!Cell1a;Sheet1Categories;Sheet1Subcategories;;0;);1;)))
and sheet2:
=UNIQUE(TOCOL(XLOOKUP(Sheet3!Cell1b;Sheet2Categories;Sheet2Subcategories;;0;);1;))
I have these on another sheet because I didn't manage to do it on sheet3. Problem is that it still references Cell1a/b if a move it around instead of dynamically changing it.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Drop down list from discontinuous cells not workingI have to make a bunch of drop downs referencing a seperate sheet that I cant really edit. some of the lists need to reference cells that arent all contiguous. For instance data validation list on =Sheet1!$A$1,Sheet1$C$1, Sheet!$D$1 yields a "You may not use reference operators for data validation critera" But leaving B1 in there works. i.e. =Sheet1!$A$1:Sheet!$D$1 works. Any way around this? submitted by /u/223specialist [link] [comments]
- Drop-down list dependant on two other cellsI have a file where I want a drop-down list in cell D14 that is based on cell D10 and D12. I have looked online and saw lot of videos that are dependant on one cell with the indirect formula, but not two. https://preview.redd.it/ptykf9yzrrwg1.png?width=289&format=png&auto=webp&s=6c0fe00c73c3d45816229bcba5fe2f1733bff083 So for example, if I had this random data and I chose Animal in cell D10, Red in cell D12, I would expect to see 33 and 2 in the drop-down menu of cell D14. Is this possible? The data is in another tab called Data and the info is in column F (F3:F1000) that should appear in the drop-down menu of cell D14. D10 and D12 are both drop-down menus if that matters submitted by /u/LookAtThosePepperoni [link] [comments]
- How do I create dynamic dropdowns that works 2 ways?I have an Order Form sheet set up with a fair bit of code in the background. Part of the Form has order lines with headers of Code, Category and Product Description. I have a separate Products list which I wanted to create the dynamic drop down lists using formulas. I soon found out that you cannot type a FILTER formula directly into the Excel Data Validation source box. After a bit of researching, I figured how to to create dynamic dropdowns by referencing a spill range. The way I've set it up is probably a bit messy (but it works). At the moment the user has to select the category of product they want first. Then the drop down list in Product Description will show only items in that category. When they select the item they want, the drop down list for Code will show the specific code that item relates to. It can work but I was hoping to be able to use partial searches in any of the 3 columns to generate suggestions. eg. Start typing partial item name which would then narrow down options in the drop down list for Products as you type each letter. Select the item and the category and Item Code self populates. Also enable the user to partially type item code or category for a similar result. Hope this makes sense. And if so, any ideas? submitted by /u/AdComplete9707 [link] [comments]
- Change contents of cells based on a drop downSo, I'm trying to generate a spreadsheet to make the forms we use in the office to issue keys. When people are given an apartment, we have a sheet with all the key numbers on it, and then they sign for it. At the moment, we've got 22 different PDFs and Word documents, and it doesn't work. So, what I want to do is this. I've built the template in Excel and created a Reference sheet which will have all the numbers in it. What I am trying to do is set it up so that when you use the Apartment number from a dropdown, it populates with all the key numbers. This sounds like it should be easy to do, but my Excel brain is broken. Advice would be much appreciated! submitted by /u/Hot_Syrup_1774 [link] [comments]