Functions - Please help me

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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


.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

oh, I forgot to mentions. when doing custom, allways
select the rate first. other wise you will get caught in
17.5 loop.