•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Using a drop down menu's selection in a formula?
Our take
Are you looking to enhance your spreadsheet's interactivity by using a dropdown menu to drive formulas? Imagine having a cell (A1) where you can select from options like "Name 1" or "Name 2," which then dynamically updates other cells (A2 and A3) to display relevant information from your data table. This approach not only simplifies data entry but also transforms how you analyze information.
So say I have a table of data E1:G3
I'd like a cell "A1" to have a drop down menu, that drop down menu references E1:E3 so options are "Name 1", "Name 2"....
cell "A2" looks at A1 and displays the contents of a cell that column (E:2)
cell "A3" looks at A1 and displays the contents of a cell that column (E:3)
is it possible to reference location of a selection in a drop down?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Lookup in table, return a formulaIs there a way to lookup a name/value of a cell in a table and the resulting answer is a formula? I am wanting to build a calculation, which could use 1 or more variables, depending on the value of a dropdown menu (Im assuming a drop down, since that would force the exact name/format of the formula callout). https://preview.redd.it/bhib489h9fpg1.png?width=1115&format=png&auto=webp&s=c79605dbb3388ec2e3471e60aecb4dd1f304bcfd Above is a quick and dirty example. If in Cell8, there is a drop down that listed the values in column J (1 Face Area, Length Face Area, etc.). In Cells D-F8, a user can input the thickness, length & width. Cell H8 would lookup the value from C8, reference the table from J5:K11, and after finding the formula, it will do the appropriate calculation. What I listed as a formula is just text for the example. I am open to adjusting as needed. This is just an example. I know I could do a bunch of imbedded IF statements in this example, but assume the table from J5:K11, could be many many lines. The table could be as many columns and rows as needed. Further, the example is just a single line (row 8), the intent would be to have many (10's, 100's or even 1,000's) rows of data, so calculating the answers and looking those up the table wouldn't work, like a single line entry. Is this even possible? I would prefer to accomplish this without VBA, if possible. EDIT: Here is another version of the example. As you can see I am planning on having multiple lines of entries. Some will have the same dropdown formula requested (i.e. Length Face Area), while others may never be requested. Hopefully that clarifies a bit. Also, while I would prefer to do without VBA, if that is the "easiest" solution, I am not opposed to entertaining. https://preview.redd.it/kqil9xiljfpg1.png?width=759&format=png&auto=webp&s=71f6d68f69bd0f6a5550d256b344032d7bc9ccbb submitted by /u/ernstm63 [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]
- 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]