Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Functions - Please help me
Im trying to create an exspenses account sheet that self calculates tax.
Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me |
#2
|
|||
|
|||
hi,
where is the list the user would choose from going to be. and how will it get selected? i would suggest another combo box but where to put it? -----Original Message----- Im trying to create an exspenses account sheet that self calculates tax. Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me . |
#3
|
|||
|
|||
I will create a tax rate column in F10 therefore if user selects none in E10
value should be 0, standard in E10 value should be 17.5, custom in E10 value should be drop down menu listing values 1 to 20 Any ideas " wrote: hi, where is the list the user would choose from going to be. and how will it get selected? i would suggest another combo box but where to put it? -----Original Message----- Im trying to create an exspenses account sheet that self calculates tax. Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me . |
#4
|
|||
|
|||
cant put 1st combo AND 2nd combo in e10.
-----Original Message----- I will create a tax rate column in F10 therefore if user selects none in E10 value should be 0, standard in E10 value should be 17.5, custom in E10 value should be drop down menu listing values 1 to 20 Any ideas " wrote: hi, where is the list the user would choose from going to be. and how will it get selected? i would suggest another combo box but where to put it? -----Original Message----- Im trying to create an exspenses account sheet that self calculates tax. Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me . . |
#5
|
|||
|
|||
2nd combo should be in F10 i want f10 to react to selection in E10 with
either 0, 17.5 or combo 1-20 If this is not possible how would you suggest i set up the sheet i need to be able to enter the gross amount, select whether taxable and if tax is not standard rate i need to be able to select rate so that sheet can show me net totals for any expenditure. " wrote: cant put 1st combo AND 2nd combo in e10. -----Original Message----- I will create a tax rate column in F10 therefore if user selects none in E10 value should be 0, standard in E10 value should be 17.5, custom in E10 value should be drop down menu listing values 1 to 20 Any ideas " wrote: hi, where is the list the user would choose from going to be. and how will it get selected? i would suggest another combo box but where to put it? -----Original Message----- Im trying to create an exspenses account sheet that self calculates tax. Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me . . |
#6
|
|||
|
|||
hi again
it is possible. from the toolbox, put a combo box at e10' right click the combo box, click properties rename it CB1. Set ListFillRange to IS1:IS3 in cell IS1 put None in cell IS2 put standart in cell IS3 put Custom You can put the fill range anywhere you want, this is just how i set it up. just make sure that where ever to put none, standard, and custom matches your fill range in the combo box. kill the property box. right click the combo box,click view code paste this code in it Private Sub CB1_Change() If CB1 = "None" Then Range("I10").Value = 0 Else If CB1 = "Standard" Then Range("I10").Value = Range("G10") / 1.175 Else If CB1 = "Custom" Then Range("I10").Value = Range("G10") / CB2 End If End If End If End Sub from the toolbox, put a combo box at F10' right click the combo box, click properties rename it CB2. Set ListFillRange to IT1:IT20 Set ListRows to 20 in cell IT1 put 1 in cell IT2 put 2 in cell IT3 put 3 ect on down. Unclick design mode. you are ready to rock and roll it works on my machine. -----Original Message----- 2nd combo should be in F10 i want f10 to react to selection in E10 with either 0, 17.5 or combo 1-20 If this is not possible how would you suggest i set up the sheet i need to be able to enter the gross amount, select whether taxable and if tax is not standard rate i need to be able to select rate so that sheet can show me net totals for any expenditure. " wrote: cant put 1st combo AND 2nd combo in e10. -----Original Message----- I will create a tax rate column in F10 therefore if user selects none in E10 value should be 0, standard in E10 value should be 17.5, custom in E10 value should be drop down menu listing values 1 to 20 Any ideas " wrote: hi, where is the list the user would choose from going to be. and how will it get selected? i would suggest another combo box but where to put it? -----Original Message----- Im trying to create an exspenses account sheet that self calculates tax. Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me . . . |
#7
|
|||
|
|||
much appreciated you are an anoymous genius one final thing can i get CB2 to
show value 0 when CB1 is "None" and Show value 17.5 when CB1 is "Standard" Also how can i repeat this all the way to row 39 " wrote: hi again it is possible. from the toolbox, put a combo box at e10' right click the combo box, click properties rename it CB1. Set ListFillRange to IS1:IS3 in cell IS1 put None in cell IS2 put standart in cell IS3 put Custom You can put the fill range anywhere you want, this is just how i set it up. just make sure that where ever to put none, standard, and custom matches your fill range in the combo box. kill the property box. right click the combo box,click view code paste this code in it Private Sub CB1_Change() If CB1 = "None" Then Range("I10").Value = 0 Else If CB1 = "Standard" Then Range("I10").Value = Range("G10") / 1.175 Else If CB1 = "Custom" Then Range("I10").Value = Range("G10") / CB2 End If End If End If End Sub from the toolbox, put a combo box at F10' right click the combo box, click properties rename it CB2. Set ListFillRange to IT1:IT20 Set ListRows to 20 in cell IT1 put 1 in cell IT2 put 2 in cell IT3 put 3 ect on down. Unclick design mode. you are ready to rock and roll it works on my machine. -----Original Message----- 2nd combo should be in F10 i want f10 to react to selection in E10 with either 0, 17.5 or combo 1-20 If this is not possible how would you suggest i set up the sheet i need to be able to enter the gross amount, select whether taxable and if tax is not standard rate i need to be able to select rate so that sheet can show me net totals for any expenditure. " wrote: cant put 1st combo AND 2nd combo in e10. -----Original Message----- I will create a tax rate column in F10 therefore if user selects none in E10 value should be 0, standard in E10 value should be 17.5, custom in E10 value should be drop down menu listing values 1 to 20 Any ideas " wrote: hi, where is the list the user would choose from going to be. and how will it get selected? i would suggest another combo box but where to put it? -----Original Message----- Im trying to create an exspenses account sheet that self calculates tax. Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me . . . |
#8
|
|||
|
|||
hi again,
yes you can do that too. Add a text box and name it TB1. this will hold the number of rows down form row 11 that you want the calculation to occur. sorry cant use row number. in CB1, replace the code i gave you yesterday and replace it with this. Private Sub CB1_Change() If CB1 = "None" Then Range("I10").Offset(TB1, 0).Value = 0 CB2 = 0 Else If CB1 = "Standard" Then Range("I10").Offset(TB1, 0).Value _ = Range("G10").Offset(TB1, 0) / 1.175 CB2 = 17.5 Else If CB1 = "Custom" Then Range("I10").Offset(TB1, 0).Value _ = Range("G10").Offset(TB1, 0) / CB2 End If End If End If End Sub -----Original Message----- much appreciated you are an anoymous genius one final thing can i get CB2 to show value 0 when CB1 is "None" and Show value 17.5 when CB1 is "Standard" Also how can i repeat this all the way to row 39 " wrote: hi again it is possible. from the toolbox, put a combo box at e10' right click the combo box, click properties rename it CB1. Set ListFillRange to IS1:IS3 in cell IS1 put None in cell IS2 put standart in cell IS3 put Custom You can put the fill range anywhere you want, this is just how i set it up. just make sure that where ever to put none, standard, and custom matches your fill range in the combo box. kill the property box. right click the combo box,click view code paste this code in it Private Sub CB1_Change() If CB1 = "None" Then Range("I10").Value = 0 Else If CB1 = "Standard" Then Range("I10").Value = Range("G10") / 1.175 Else If CB1 = "Custom" Then Range("I10").Value = Range("G10") / CB2 End If End If End If End Sub from the toolbox, put a combo box at F10' right click the combo box, click properties rename it CB2. Set ListFillRange to IT1:IT20 Set ListRows to 20 in cell IT1 put 1 in cell IT2 put 2 in cell IT3 put 3 ect on down. Unclick design mode. you are ready to rock and roll it works on my machine. -----Original Message----- 2nd combo should be in F10 i want f10 to react to selection in E10 with either 0, 17.5 or combo 1-20 If this is not possible how would you suggest i set up the sheet i need to be able to enter the gross amount, select whether taxable and if tax is not standard rate i need to be able to select rate so that sheet can show me net totals for any expenditure. " wrote: cant put 1st combo AND 2nd combo in e10. -----Original Message----- I will create a tax rate column in F10 therefore if user selects none in E10 value should be 0, standard in E10 value should be 17.5, custom in E10 value should be drop down menu listing values 1 to 20 Any ideas " wrote: hi, where is the list the user would choose from going to be. and how will it get selected? i would suggest another combo box but where to put it? -----Original Message----- Im trying to create an exspenses account sheet that self calculates tax. Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me . . . . |
#9
|
|||
|
|||
oh, I forgot to mentions. when doing custom, allways
select the rate first. other wise you will get caught in 17.5 loop. -----Original Message----- much appreciated you are an anoymous genius one final thing can i get CB2 to show value 0 when CB1 is "None" and Show value 17.5 when CB1 is "Standard" Also how can i repeat this all the way to row 39 " wrote: hi again it is possible. from the toolbox, put a combo box at e10' right click the combo box, click properties rename it CB1. Set ListFillRange to IS1:IS3 in cell IS1 put None in cell IS2 put standart in cell IS3 put Custom You can put the fill range anywhere you want, this is just how i set it up. just make sure that where ever to put none, standard, and custom matches your fill range in the combo box. kill the property box. right click the combo box,click view code paste this code in it Private Sub CB1_Change() If CB1 = "None" Then Range("I10").Value = 0 Else If CB1 = "Standard" Then Range("I10").Value = Range("G10") / 1.175 Else If CB1 = "Custom" Then Range("I10").Value = Range("G10") / CB2 End If End If End If End Sub from the toolbox, put a combo box at F10' right click the combo box, click properties rename it CB2. Set ListFillRange to IT1:IT20 Set ListRows to 20 in cell IT1 put 1 in cell IT2 put 2 in cell IT3 put 3 ect on down. Unclick design mode. you are ready to rock and roll it works on my machine. -----Original Message----- 2nd combo should be in F10 i want f10 to react to selection in E10 with either 0, 17.5 or combo 1-20 If this is not possible how would you suggest i set up the sheet i need to be able to enter the gross amount, select whether taxable and if tax is not standard rate i need to be able to select rate so that sheet can show me net totals for any expenditure. " wrote: cant put 1st combo AND 2nd combo in e10. -----Original Message----- I will create a tax rate column in F10 therefore if user selects none in E10 value should be 0, standard in E10 value should be 17.5, custom in E10 value should be drop down menu listing values 1 to 20 Any ideas " wrote: hi, where is the list the user would choose from going to be. and how will it get selected? i would suggest another combo box but where to put it? -----Original Message----- Im trying to create an exspenses account sheet that self calculates tax. Cells are E10 TAX y/n , G10 Gross, I10 total tax I need to- Create a drop down menu for cell E10 offering three options, None, Standard 17.5%, Custom. If custom is selected the user must be able to select the tax rate from a list between 1% and 20% Cell i10 must be 0 if E10 is none, must be G10 divided by 1.175 if E10 is standard and finally G10 divided by 1.user defined value if custom is selected ( eg if user selected 5%would be G10/1.05 ). Please somebody help me . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use variables for workheet name references in Excel functions? | Excel Discussion (Misc queries) | |||
# of Functions per cell | Excel Worksheet Functions | |||
PivotTable canned functions | Excel Discussion (Misc queries) | |||
How to load Engineering Functions into the Fx function wizard? | Excel Worksheet Functions | |||
Where can I see VBA code for financial functions? | Excel Worksheet Functions |