

Subject:
Excel Question
Category: Computers Asked by: tnsdanga List Price: $10.00 
Posted:
24 Oct 2006 13:31 PDT
Expires: 23 Nov 2006 12:31 PST Question ID: 776480 

There is no answer at this time. 

Subject:
Re: Excel Question
From: toufarooga 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: joet31124ga 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 dropdown 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: toufarooga on 24 Oct 2006 19:19 PDT 
You're welcome  I'm glad it worked out for you! toufaroo 
Subject:
Re: Excel Question
From: agbinfoga on 03 Nov 2006 16:56 PST 
You might want to try this instead, it avoids the multiple columns: =choose((A1/101)*4+(B19)/2+C11;$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/101)*4+(B19)/2+C11 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 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 