get cells to populate based off two data validation entries:
I have two data validation cells:
- In one a user can select: All Fixed, Current Income, Balanced Appreciation, Balanced, Balanced Return, Balanced Appreciation, Appreciation, or All Equity.
- In the second, the user can select Accredited or Non-Accredited
Based off these two selections, I need cells to populate with allocation percentages (ie: if they choose Balanced Income and Non-Accredited, certain cells need to populate with 0% (representing cash), 20% (representing large cap), 4% (representing mid cap), 3% (representing small cap), 7% (for both int'l and emerging markets), etc.......
Each one of these would combinations would have a different % output.
Any ideas? I think I am way over my head.
Thanks for the help!
Add a sheet to your workbook called TABLE.
In the table put your income types down column A starting at A2. These would be:
Accredited-All Fixed
Accredited-Current Income
Accredited-Balanced Appreciation
Accredited-Balanced
Accredited-Balanced Return
Accredited-Balanced Appreciation
Accredited-Appreciation
Accredited-All Equity
Non-Accredited-All Fixed
Non-Accredited-Current Income
Non-Accredited-Balanced Appreciation
Non-Accredited-Balanced
Non-Accredited-Balanced Return
Non-Accredited-Balanced Appreciation
Non-Accredited-Appreciation
Non-Accredited-All Equity
Put in B1 - CASH
Put in C1 - LARGE CAP
Put in D1 - MID CAP
Etc....
Now fill in the table with the percentages.
This creates a lookup table.
Back on another sheet you would lookup percentages using an INDEX/MATCH.
For Accredited (in cell A1) and All Fixed in cell (A2) and MID CAP (in cell A3) the formula would read:
=INDEX(Table!$B$2:$Z$100, MATCH($A1 & "-" & $A2, Table!$A$2:$A100, 0), MATCH($A3, Table!$B$1:$Z$1, 0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank you for responding. I attached a test sheet. I don't have a Index/Match choice in my formula bar. I can only choose one or the other. Based off the attached sheet, I want to choose two options in the yellow cells (ie: balanced apprecation / accredited) and then have the corresponding percentages return below that in the brown cells. (ie: have the 0% show next to cash, 29% show next to us large cap, etc)
I know I am missing something.
Thanks again for your help. This is great!
Format B6 as percentage, then enter this formula and copy downward:
=INDEX($E$5:$Q$20, MATCH($B$3&"-"&$B$4, INDEX($C$5:$C$20&"-"&$D$5:$D$20, 0), 0), MATCH($A6, $E$4:$Q$4, 0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Perfect. Thanks!
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks