How can I make a single selection drop down auto fill a ton of other cells?
Our take
I've not used excel much so I'm in way over my head. I've watched a bunch of videos and can get one cell to fill from a drop down but it seems to only work if they're 'lined up' with each other? For example in the 1st picture below, the drop down I have I can get 5 and 10-year to autofill MSUP 1st and 2nd Coats respectively, but only if they're next to each other as shown. I may not be explaining this well, sorry in advance if I am, I pretty much just want to select one product / warranty and have it auto fill all the green. I have templates on another sheet posted below that I've been copying and pasting when I price out jobs but it's just not efficient at all.
I suspect that I have to organise or format my 'templates' somehow in order to make the V or X lookup function reference it? Either way, I'm pretty lost, so any help is appreciated, thanks guys!!~\~
EDIT: ok I've managed to get the drop down list to populate the correct items for 1 product by 'tagging' but this is reliant on putting this formula in each of these highlighted cells. My problem now is these cells are 'locked' looking for data from the same area as this 1 product, but I need it to search the entire sheet. When I select the entire sheet for the 'return array' it gives a value error presumably because it's trying to pull more than 1 value and put it in a single cell from my 'tags'. I suppose I could make a sheet for each system template we have but that would be cumbersome to work on. Is there a better way to do this? Am I even making sense? haha
=XLOOKUP($B$7,Sheet1!A20,Sheet1!B20)
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Using dropbox selection to populate a cell from a separate sheet within a workbook.Hey all, sorry to ask such a stupid question, but I cannot get the right thing to happen no matter what I try. Essentially, I want to select a name from a drop down list and have the merged cells below it return an address from a separate sheet in the same workbook. I have tried some different VLOOKUP functions, but I just cannot get them to work for the life of me. I have attached a link to a PDF with some photos of my spreadsheet (I created some templates so I could keep my actual clients anonymous), but the cells in the templates would be the cells in reference on my actual sheets. Any assistance would be greatly appreciated. Edit: Since I cannot post anything in the main post with a link or photo, nor can I start with asking for help, I hope attempt number three works, and then I will try and respond with a link to a PDF of the spreadsheet in question. Edit: I figured it out. Thanks everybody, sorry to waste your time because I am an idiot. submitted by /u/ClandestineGhost [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]
- Pulling data when looking at a specific cell?I know its possible to make a drop down menu, but is it possible to make it so that when I select something in the drop down menu and type in a value in the box to the left of the drop down menu, it looks up data from another sheet that Auto fills into to other boxes? I have read about the "LOOKUP" command, but im not sure it does what is want to do. I have posted a pic that hopefully shows what i mean Hope someone can help. submitted by /u/GGS1LENCE [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]