J
jswasson
I am creating a rate sheet that currently uses just one combo box and "if"
formulas.
Rates are A, B, C, D, E and the formula is
=IF($J$3=1,Data!$C$2,(IF($J$3=2,Data!$D$2,IF($J$3=3,Data!$E$2,IF($J$3=4,Data!
$F$2,IF($J$3=5,Data!$G$2,IF($J$3=6,Data!$H$2,"")))))))
J3 is the output from the Combo box.
I have 81 products that are listed on the Main worksheet, each with this
formula so that when a Rate is choosen from the Combo box then it populates
the Main worksheet with the appropriate data.
All of this works beautifully. Until....
I currently have the rates calculated on a weekly bases, but there are some
clients that need to be bi-weekly, or bi-monthly or even monthly.
So.....
I wanted to create a 2nd Combo box and have those 4 options. Use that data
from that combo box in conjuntion with the data from the first combo box to
populate the worksheet.
I created an IF formula, but by the time I got it all in like I needed it,
Excel said it was too long.
I know that I could create 4 different worksheets one for each of the "time"
varable, but I would like for it all to be on one sheet. (I am catering to
the least common demoniator in the office
)
I have looked the the postings and couldn't really find anything that matches
this problem.
Here is what I got right now:
http://www.oakhillphoto.com/6ClassRateSheet.xls
If there is a better way to do this I am willing to learn.
Thanks in advance.
formulas.
Rates are A, B, C, D, E and the formula is
=IF($J$3=1,Data!$C$2,(IF($J$3=2,Data!$D$2,IF($J$3=3,Data!$E$2,IF($J$3=4,Data!
$F$2,IF($J$3=5,Data!$G$2,IF($J$3=6,Data!$H$2,"")))))))
J3 is the output from the Combo box.
I have 81 products that are listed on the Main worksheet, each with this
formula so that when a Rate is choosen from the Combo box then it populates
the Main worksheet with the appropriate data.
All of this works beautifully. Until....
I currently have the rates calculated on a weekly bases, but there are some
clients that need to be bi-weekly, or bi-monthly or even monthly.
So.....
I wanted to create a 2nd Combo box and have those 4 options. Use that data
from that combo box in conjuntion with the data from the first combo box to
populate the worksheet.
I created an IF formula, but by the time I got it all in like I needed it,
Excel said it was too long.
I know that I could create 4 different worksheets one for each of the "time"
varable, but I would like for it all to be on one sheet. (I am catering to
the least common demoniator in the office
)I have looked the the postings and couldn't really find anything that matches
this problem.
Here is what I got right now:
http://www.oakhillphoto.com/6ClassRateSheet.xls
If there is a better way to do this I am willing to learn.
Thanks in advance.