| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating nested formulas from drop down box
Hi, I have cell A1 with a drop down box containing 26 available choices. B1
has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="ONE (1)"),S2,IF((G2="TWO (2)"),S2,IF((G2="THREE (3)"),S2,IF((G2="FOUR (4)"),S2,IF((G2="FIVE (5)"),S2,IF((G2="SIX (6)"),S2,IF((G2="SEVEN (7)"),S2,IF((G2="EIGHT (8)"),S2,IF((G2="NINE (9)"),S2,"NA")))))))) I've maxed the nesting formula...is there any other way? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
creating nested formulas from drop down box
Insert a new row 1, so that all those cells you describe are now on
row 2. Put the names of the months in row1, so that F1 contains January, G1 contains February etc. Then in F2 you can use this formula: =IF($A2=F$1,$E2,"n/a") You can change the "n/a" to zero if you wish (may be better if you want to do any arithmetic on the range). Then you can copy the formula across to the December column. PS. I didn't understand how your formula related to your description. Hope this helps. Pete On Feb 18, 2:16*am, Kathleen wrote: Hi, *I have cell A1 with a drop down box containing 26 available choices. *B1 has the dollar amount matching to the choice in A1 using vlookup. *E1 totals several cells including B1 together. *I want F1 to look at A1 and either enter the number from E1 or NA. *Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 *(total of b-d1) * F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="ONE (1)"),S2,IF((G2="TWO (2)"),S2,IF((G2="THREE (3)"),S2,IF((G2="FOUR (4)"),S2,IF((G2="FIVE (5)"),S2,IF((G2="SIX (6)"),S2,IF((G2="SEVEN (7)"),S2,IF((G2="EIGHT (8)"),S2,IF((G2="NINE (9)"),S2,"NA")))))))) I've maxed the nesting formula...is there any other way? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
creating nested formulas from drop down box
Hi Pete,
Thank you for the quick response. I apologize for the poor explanation. The goal of the formula is to identify if a payment is due that month. If January is picked in the dropdown box, then the column for January would show the amount due. If February is picked, then the January column would be NA and the February column would show the amount due. In most cases, payments may not become due until several months after January...so those cells would show NA. I used months as an example. In reality, I'm using payperiods 1 - 26. "Pete_UK" wrote: Insert a new row 1, so that all those cells you describe are now on row 2. Put the names of the months in row1, so that F1 contains January, G1 contains February etc. Then in F2 you can use this formula: =IF($A2=F$1,$E2,"n/a") You can change the "n/a" to zero if you wish (may be better if you want to do any arithmetic on the range). Then you can copy the formula across to the December column. PS. I didn't understand how your formula related to your description. Hope this helps. Pete On Feb 18, 2:16 am, Kathleen wrote: Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="ONE (1)"),S2,IF((G2="TWO (2)"),S2,IF((G2="THREE (3)"),S2,IF((G2="FOUR (4)"),S2,IF((G2="FIVE (5)"),S2,IF((G2="SIX (6)"),S2,IF((G2="SEVEN (7)"),S2,IF((G2="EIGHT (8)"),S2,IF((G2="NINE (9)"),S2,"NA")))))))) I've maxed the nesting formula...is there any other way? . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
creating nested formulas from drop down box
Perhaps you can describe what you have more accurately, then any
solutions proposed would be tailored to your situation. Pete On Feb 18, 1:43*pm, Kathleen wrote: Hi Pete, Thank you for the quick response. *I apologize for the poor explanation.. * The goal of the formula is to identify if a payment is due that month. *If January is picked in the dropdown box, then the column for January would show the amount due. *If February is picked, then the January column would be NA and the February column would show the amount due. *In most cases, payments may not become due until several months after January...so those cells would show NA. *I used months as an example. *In reality, I'm using payperiods 1 - 26. "Pete_UK" wrote: Insert a new row 1, so that all those cells you describe are now on row 2. Put the names of the months in row1, so that F1 contains January, G1 contains February etc. Then in F2 you can use this formula: =IF($A2=F$1,$E2,"n/a") You can change the "n/a" to zero if you wish (may be better if you want to do any arithmetic on the range). Then you can copy the formula across to the December column. PS. I didn't understand how your formula related to your description. Hope this helps. Pete On Feb 18, 2:16 am, Kathleen wrote: Hi, *I have cell A1 with a drop down box containing 26 available choices. *B1 has the dollar amount matching to the choice in A1 using vlookup. *E1 totals several cells including B1 together. *I want F1 to look at A1 and either enter the number from E1 or NA. *Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 *(total of b-d1) * F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="ONE (1)"),S2,IF((G2="TWO (2)"),S2,IF((G2="THREE (3)"),S2,IF((G2="FOUR (4)"),S2,IF((G2="FIVE (5)"),S2,IF((G2="SIX (6)"),S2,IF((G2="SEVEN (7)"),S2,IF((G2="EIGHT (8)"),S2,IF((G2="NINE (9)"),S2,"NA")))))))) I've maxed the nesting formula...is there any other way? .- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
creating nested formulas from drop down box
Very true...should have been more clear in the first place. Sorry about that.
Ok, here goes.... I have 26 payperiods in which a payment could be due to begin. The payment due is made up of several other columns and totals into an "amount due" column. There is a dropdown box with 1-26 payperiods in it. If the user chooses, payperiod 1, then the payperiod 1 field looks at the amount due column and populates with that number. Now, if the user chooses payperiod 6, payperiods 1-5 will change to na and payperiod 6 field will show the amount due. The formula in my initial posting is working great. Problem is that it stops at payperiod 8 due to nesting formula limits. I'm wondering if there is a work around or if there is a better way to do it. "Pete_UK" wrote: Perhaps you can describe what you have more accurately, then any solutions proposed would be tailored to your situation. Pete On Feb 18, 1:43 pm, Kathleen wrote: Hi Pete, Thank you for the quick response. I apologize for the poor explanation.. The goal of the formula is to identify if a payment is due that month. If January is picked in the dropdown box, then the column for January would show the amount due. If February is picked, then the January column would be NA and the February column would show the amount due. In most cases, payments may not become due until several months after January...so those cells would show NA. I used months as an example. In reality, I'm using payperiods 1 - 26. "Pete_UK" wrote: Insert a new row 1, so that all those cells you describe are now on row 2. Put the names of the months in row1, so that F1 contains January, G1 contains February etc. Then in F2 you can use this formula: =IF($A2=F$1,$E2,"n/a") You can change the "n/a" to zero if you wish (may be better if you want to do any arithmetic on the range). Then you can copy the formula across to the December column. PS. I didn't understand how your formula related to your description. Hope this helps. Pete On Feb 18, 2:16 am, Kathleen wrote: Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="ONE (1)"),S2,IF((G2="TWO (2)"),S2,IF((G2="THREE (3)"),S2,IF((G2="FOUR (4)"),S2,IF((G2="FIVE (5)"),S2,IF((G2="SIX (6)"),S2,IF((G2="SEVEN (7)"),S2,IF((G2="EIGHT (8)"),S2,IF((G2="NINE (9)"),S2,"NA")))))))) I've maxed the nesting formula...is there any other way? .- Hide quoted text - - Show quoted text - . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
creating nested formulas from drop down box
Forgot to mention that I am using Excel 2003
"Kathleen" wrote: Very true...should have been more clear in the first place. Sorry about that. Ok, here goes.... I have 26 payperiods in which a payment could be due to begin. The payment due is made up of several other columns and totals into an "amount due" column. There is a dropdown box with 1-26 payperiods in it. If the user chooses, payperiod 1, then the payperiod 1 field looks at the amount due column and populates with that number. Now, if the user chooses payperiod 6, payperiods 1-5 will change to na and payperiod 6 field will show the amount due. The formula in my initial posting is working great. Problem is that it stops at payperiod 8 due to nesting formula limits. I'm wondering if there is a work around or if there is a better way to do it. "Pete_UK" wrote: Perhaps you can describe what you have more accurately, then any solutions proposed would be tailored to your situation. Pete On Feb 18, 1:43 pm, Kathleen wrote: Hi Pete, Thank you for the quick response. I apologize for the poor explanation.. The goal of the formula is to identify if a payment is due that month. If January is picked in the dropdown box, then the column for January would show the amount due. If February is picked, then the January column would be NA and the February column would show the amount due. In most cases, payments may not become due until several months after January...so those cells would show NA. I used months as an example. In reality, I'm using payperiods 1 - 26. "Pete_UK" wrote: Insert a new row 1, so that all those cells you describe are now on row 2. Put the names of the months in row1, so that F1 contains January, G1 contains February etc. Then in F2 you can use this formula: =IF($A2=F$1,$E2,"n/a") You can change the "n/a" to zero if you wish (may be better if you want to do any arithmetic on the range). Then you can copy the formula across to the December column. PS. I didn't understand how your formula related to your description. Hope this helps. Pete On Feb 18, 2:16 am, Kathleen wrote: Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="ONE (1)"),S2,IF((G2="TWO (2)"),S2,IF((G2="THREE (3)"),S2,IF((G2="FOUR (4)"),S2,IF((G2="FIVE (5)"),S2,IF((G2="SIX (6)"),S2,IF((G2="SEVEN (7)"),S2,IF((G2="EIGHT (8)"),S2,IF((G2="NINE (9)"),S2,"NA")))))))) I've maxed the nesting formula...is there any other way? .- Hide quoted text - - Show quoted text - . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating drop downs in a cell contingent on another drop down | Excel Discussion (Misc queries) | |||
| Creating Nested If Fuctions | Excel Worksheet Functions | |||
| Cross-referenced drop-down menu (nested drop-downs?) | Excel Worksheet Functions | |||
| Creating a nested COUNTIF???? | Excel Worksheet Functions | |||
Hybrid Mode
