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?

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?

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?

.

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 -

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 -

.

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 -

.