Functions - Please help me

[jay]

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

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
>
>.
>

[jay]

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

"anonymous@discussions.microsoft.com" 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
> >
> >.
> >
>

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
>
>"anonymous@discussions.microsoft.com" 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
>> >
>> >.
>> >
>>
>.
>

[jay]

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.

"anonymous@discussions.microsoft.com" 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
> >
> >"anonymous@discussions.microsoft.com" 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
> >> >
> >> >.
> >> >
> >>
> >.
> >
>

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.
>
>"anonymous@discussions.microsoft.com" 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
>> >
>> >"anonymous@discussions.microsoft.com" 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
>> >> >
>> >> >.
>> >> >
>> >>
>> >.
>> >
>>
>.
>

[jay]

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

"anonymous@discussions.microsoft.com" 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.
> >
> >"anonymous@discussions.microsoft.com" 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
> >> >
> >> >"anonymous@discussions.microsoft.com" 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
> >> >> >
> >> >> >.
> >> >> >
> >> >>
> >> >.
> >> >
> >>
> >.
> >
>

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
>
>"anonymous@discussions.microsoft.com" 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.
>> >
>> >"anonymous@discussions.microsoft.com" 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
>> >> >
>> >> >"anonymous@discussions.microsoft.com" 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
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >>
>> >> >.
>> >> >
>> >>
>> >.
>> >
>>
>.
>

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
>
>"anonymous@discussions.microsoft.com" 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.
>> >
>> >"anonymous@discussions.microsoft.com" 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
>> >> >
>> >> >"anonymous@discussions.microsoft.com" 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
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >>
>> >> >.
>> >> >
>> >>
>> >.
>> >
>>
>.
>