 View Question
Q: Excel Question ( No Answer,   4 Comments ) Question
 Subject: Excel Question Category: Computers Asked by: tnsdan-ga List Price: \$10.00 Posted: 24 Oct 2006 13:31 PDT Expires: 23 Nov 2006 12:31 PST Question ID: 776480
 ```I have a sheet with 3 columns. Each of these columns has a drop down box. The first box has 3 options (the numbers 20, 30, & 40), the second box has 2 options (the numbers 9 & 13) and the third box has 2 options (the numbers 2 & 3). I would like the 4 column to display data based on what the first 3 choices are. This data is from another sheet within the same workbook. For example, I need a statement that says if A1=20 and B1=9 and C1=2 then display cell Z15 from the sheet with the data. I will also need this function to have the logic for all 12 possible results, as they lead to 12 different cells in the next sheet. I know absolutely nothing about conditional statements (especially those that are, at least for me, this complicated) in Excel, so I would appreciate it if someone could write this function for me. If it makes any difference, the name of the sheet with the data on it that I wish to reference is One Group.``` Clarification of Question by tnsdan-ga on 24 Oct 2006 16:27 PDT `Thank you toufaroo, that was perfect. Even the GA commentors are great:)` Answer
 There is no answer at this time. Comments
 Subject: Re: Excel Question From: toufaroo-ga on 24 Oct 2006 14:09 PDT
 ```This is going to be a tad tricky, because Excel only allows you to use 8 IF statements in any one field. That is to say, it only lets you differentiate between 8 choices, not 12. So, to work around this, we'll create three "hidden" boxes, each addressing four choices. We'll then create a fourth box that addresses the final result! For each of the first three "hidden boxes", we have to assume that A = 20, 30, and 40 respectively. So, for hidden box number one (A=20), your formula will look something like: =IF(AND(B1=9,C1=2),'One Group'!Z1,IF(AND(B1=9,C1=3),'One Group'!Z2,IF(AND(B1=13,C1=2),'One Group'!Z3,IF(AND(B1=13,C1=3),'One Group'!Z4)))) Place the above formula in cell G1 of your main worksheet. Where Z1, Z2, Z3, and Z4 are in sheet 'One Group' with the appropriate data. You'll have to adjust these values as necessary for your application. Hidden Box number two (recall that this is for A=30): =IF(AND(B1=9,C1=2),'One Group'!Z5,IF(AND(B1=9,C1=3),'One Group'!Z6,IF(AND(B1=13,C1=2),'One Group'!Z7,IF(AND(B1=13,C1=3),'One Group'!Z8)))) Place the above formula in cell G2 of your main worksheet. Hidden Box number three (A=40): =IF(AND(B1=9,C1=2),'One Group'!Z9,IF(AND(B1=9,C1=3),'One Group'!Z10,IF(AND(B1=13,C1=2),'One Group'!Z11,IF(AND(B1=13,C1=3),'One Group'!Z12)))) Place the above formula in cell G3 of your main worksheet. Your final box: =IF(A1=20, G1, IF(A1=30, G2, IF(A1=40, G3))) Place the above formula in cell D1 of your main worksheet. For simplicity, I recommend that you make the "hidden boxes" white text or otherwise make then invisible so that you don;t get confused while using the spreadsheet. Enjoy! -toufaroo```
 Subject: Re: Excel Question From: joet31124-ga on 24 Oct 2006 16:53 PDT
 ```To me, it seems the easiest way would be to use the Text function and a lookup table. Assume that the drop-down boxes refer to A1, B1, and C1. In cell D1 you would put the following formula: =TEXT(A1,"000")&TEXT(B1,"000")&TEXT(C1,"000"). Then, in cell E1, you would enter the formula =VLOOKUP(D1,\$M\$1:\$N\$12,2) where \$M\$1:\$N\$12 refers to a range of cells containing the possible combinations in the "M" column and the associated values in the "N" column. In this example I've just used the letters a,b,c, etc., but you could put any value in. M N 020009002 a 020009003 b 020013002 c 020013003 d 030009002 e 030009003 f 030013002 g 030013003 h 040009002 I 040009003 j 040013002 k 040013003 l I'm not sure if this makes any sense, but thought it might work. I swear it's much simpler than I'm making it sound.```
 Subject: Re: Excel Question From: toufaroo-ga on 24 Oct 2006 19:19 PDT
 ```You're welcome - I'm glad it worked out for you! -toufaroo```
 Subject: Re: Excel Question From: agbinfo-ga on 03 Nov 2006 16:56 PST
 ```You might want to try this instead, it avoids the multiple columns: =choose((A1/10-1)*4+(B1-9)/2+C1-1;\$V\$1;\$V\$2;\$V\$2;\$V\$3;\$V\$4;\$V\$5;\$V\$6;\$V\$7;\$V\$8;\$V\$9;\$V\$10;V\$11;V\$12) The formula (A1/10-1)*4+(B1-9)/2+C1-1 will convert your 3 values into a value from 1 through 12 and the choose function lets you select one of the 12 values based on that index. The dollar signs in \$V\$1 makes these adresses absolute so you can copy the function and it will keep the same values. Hope that helps. - Alain```
 Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service. If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for Google Home - Answers FAQ - Terms of Service - Privacy Policy