hello. i need to utilize a drop down menu to choose a value, and then have that choice populate 5 other values automatically. Can someone help me with this please/ thank you
hello. i need to utilize a drop down menu to choose a value, and then have that choice populate 5 other values automatically. Can someone help me with this please/ thank you
More information is needed.
Here is a tutorial on creating a dropdown list: https://support.office.com/en-nz/art...2-95b6aeeb73c9
To then use that value to populate 5 other cells, you can just use a reference. For instance, if your dropdown is in A1, just use =A1 in your other cells. If your need is more complex you need to give more details on your intended result.
ok. i am creating some analysis around the discount rate. I would like to choose a duration of a potential project. lets just use a duration of 5 years. I would use a drop down to choose 5 years from a list of options. From there i would like to have that duration choice automatically fill in other details about the potential project such as the associated treasury rate for that duration, the market return, and 3 other categories. Im not sure how i could create this in excel
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Here is one way of doing what you describe.
If the 5 responses are not consecutive like I have shown, enter the column numbers into the VLOOKUP manually to suit the response that you need.
This part of the VLOOKUP formula (ROWS($B$3:B3)-1)*1+2 is just a counter that increments by 1 starting at #2
You can replace this with the number of the column of the VLOOKUP table entered manually.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
i highlighted the drop down and the details i want to auto populate below it
The workbook has me confused: What is the cell location of the Drop-down (including worksheet name) and where are the cells that you want filled with values?
I just don't see what you want in your workbook as there are a few highlighted areas.
theres a drop down for cell E103. I would like to be able to change that to different durations using the drop down and then have cells E105, e106, e107, e108 and e109 automatically populate with data that i choose.
The dropdown is located at 'one year'!E57. The 5 dependant cells are at E59:E63.
How should the values in E59:E63 change depending on the selection in E57? It seems as if there should be a table somewhere that could be referenced with a lookup, but the table is not included in the workbook.
I see these descriptions in column G, but I am not sure if they are just leftover from your sanitization of your workbook or if they are intended to describe the purpose of the corresponding cell in column E
If the descriptions above are intended to describe the source of the data for column E, then it seems as if the data is coming from an external source and this could get complicated pretty quickly.Yahoo finance
expected rtn on mkt during period: 7%
Historical avg from Morningstar Ibbotson SBBI 2011 Valuation Yearbook.
Negotiated with Auditors. KPMG gave Fin reporting a range of 0.5 to 1.5%
Last edited by Whizbang; 05-04-2015 at 05:03 PM.
i'm not sure what else to say. i have attached another spreadsheet. the source data is above, which is how i created the drop down in cell E103. My goal is to choose a duration from the drop down in cell E102 and then have cells E105 through E109 update automatically. Can i do that somehow> using and If/then statement or vlookup?
Ah. I see the problem now. The sheet "one year" was active on open, but you were referring to sheet "Sheet1 (2)".
A vlookup is what I would recommend, except I see no table that correlates the options available in E103 with anything.
E 103 correlates to column B rows 2 through 10. how do i do a vlookup
Yes, I see that the Data Validation for E103 is pointing at B2:B10. What happens when someone selects a value in E103? If you were to do this manually, where would you look using that value to get the corresponding value in E105?
i want cells E105 through E109 to populate automatically. i will then create a tab for other durations like a 10 year duration and i will want E105 through E109 to populate with the five numbers i choose
Pretend I am a dumb computer. You have given me the value "5 yr". What am I supposed to do with it? "Update automatically" tells me nothing. I need to take the value "5 yr" and use it in some way, but you have not given me any instructions on what that is.
You have 9 choices from which to choose in cell E103. For each of those choices you need a list named for each of those choices with all the choices to be made for each of those choices....you are going to have a lot of lists.
For example if you choose 10 years from E103, you will need a list for each of E105, E106, E107, E108 and E109 that pertains to the 10 year choice.
At a minimum it would appear that you will need 9 vlookup tables to cover the choice in E103 or a table that can accommodate many different lookups.
Last edited by newdoverman; 05-04-2015 at 05:32 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks