Prev: "manual page breaks error"
Next: Wonderware Active Factory Add In Problem
From: Toppers on 8 Aug 2007 15:12
See Peo's comments: I had overlooked the fact you wanted to calculate the NET
or TOTAl as well as VAT! I just calculated VAT from either.

"Toppers" wrote:

> You can replace with 17.5% or better still with a cell containing the VAT
> value.
>
> =IF($F$10="",round(F8*X1,2),round(($F$10/(1+X1)*X1),2))
>
> X1 contains your VAT %.
>
>
> "Tina Harrison" wrote:
>
> > Thank you
> >
> > do you mean where you wrote vat you input 17.5% ?
> > Sorry if this is simple but i am very very rusty!!
> >
> > Many thanks
> >
> > Tina
> >
> > "Toppers" wrote:
> >
> > > =IF($F$10="",rond(F8*vat,2),round(($F$10/(1+vat)*vat),2))
> > >
> > > Vat is named range containing VAT%
> > >
> > > "Tina Harrison" wrote:
> > >
> > > > Hi all
> > > >
> > > > I have a worksheet like below, It might be easy for some but i'm a bit rusty.
> > > >
> > > > F8 F9 F10
> > > > Net VAT Total
> > > >
> > > > What i need is a function that will allow me to enter a amount in either Net
> > > > or Total and it would work out Vat and either Net or Total depending where i
> > > > have put the amount. Hope this makes sense!
> > > >
> > > > Many thanks
> > > >
> > > > Tina
> > > >
> > > >
From: Tina Harrison on 8 Aug 2007 15:18
I don't think i explained myself very well.
I need to enter data in either F9 or H9
so if i enter data in F9 the answer will be in H9 and vise versa
and then maybe enter a formula in G10 to show the VAT.
Can this be done?

Thank you for your help

Tina

"Peo Sjoblom" wrote:

> You can't have a function in a cell then enter something in that same cell
>
> So if you create a 4th and 5th column
>
> to get the Net if you enter something in the total use
>
> =F10/(1+17.5%)
>
> to get the VAT
>
> =F10-(F10/(1+17.5%))
>
> If you put in the Net in F8
>
>
> then use
>
> =F8*17.5%
>
> to get the VAT
>
> and
>
> =F8*(1+17.5%)
>
> to get the total
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
>
> "Tina Harrison" <TinaHarrison(a)discussions.microsoft.com> wrote in message
> news:CAEAB0AD-6248-4AF9-B007-908F0D2C7DCB(a)microsoft.com...
> > Hi all
> >
> > I have a worksheet like below, It might be easy for some but i'm a bit
> > rusty.
> >
> > F8 F9 F10
> > Net VAT Total
> >
> > What i need is a function that will allow me to enter a amount in either
> > Net
> > or Total and it would work out Vat and either Net or Total depending where
> > i
> > have put the amount. Hope this makes sense!
> >
> > Many thanks
> >
> > Tina
> >
> >
>
>
>
From: Peo Sjoblom on 8 Aug 2007 15:31
No, as I said

"You can't have a function in a cell then enter something in that same cell"

Meaning that if you have a function in F12 to calculate the total then you
can't type in the total in the same cell, once you do you clear out the
function.


--
Regards,

Peo Sjoblom




"Tina Harrison" <TinaHarrison(a)discussions.microsoft.com> wrote in message
news:BD2956C4-0479-4E22-BC86-A5C68E01C4B3(a)microsoft.com...
>I don't think i explained myself very well.
> I need to enter data in either F9 or H9
> so if i enter data in F9 the answer will be in H9 and vise versa
> and then maybe enter a formula in G10 to show the VAT.
> Can this be done?
>
> Thank you for your help
>
> Tina
>
> "Peo Sjoblom" wrote:
>
>> You can't have a function in a cell then enter something in that same
>> cell
>>
>> So if you create a 4th and 5th column
>>
>> to get the Net if you enter something in the total use
>>
>> =F10/(1+17.5%)
>>
>> to get the VAT
>>
>> =F10-(F10/(1+17.5%))
>>
>> If you put in the Net in F8
>>
>>
>> then use
>>
>> =F8*17.5%
>>
>> to get the VAT
>>
>> and
>>
>> =F8*(1+17.5%)
>>
>> to get the total
>>
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>> "Tina Harrison" <TinaHarrison(a)discussions.microsoft.com> wrote in message
>> news:CAEAB0AD-6248-4AF9-B007-908F0D2C7DCB(a)microsoft.com...
>> > Hi all
>> >
>> > I have a worksheet like below, It might be easy for some but i'm a bit
>> > rusty.
>> >
>> > F8 F9 F10
>> > Net VAT Total
>> >
>> > What i need is a function that will allow me to enter a amount in
>> > either
>> > Net
>> > or Total and it would work out Vat and either Net or Total depending
>> > where
>> > i
>> > have put the amount. Hope this makes sense!
>> >
>> > Many thanks
>> >
>> > Tina
>> >
>> >
>>
>>
>>


From: Sandy Mann on 8 Aug 2007 15:44
To do what you want you would need VBA. Right-click on the sheet tab and
select View Code then enter this Macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VAT As Double
VAT = 0.175

If Intersect(Target, Range("F8:F10")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

If Target.Row = 8 Then
Cells(9, 6).Value = Application.Round(Target.Value * VAT, 2)
Cells(10, 6).Value = Target.Value + Cells(9, 6).Value
End If

If Target.Row = 9 Then
Cells(8, 6).Value = Application.Round(Target.Value / VAT, 2)
Cells(10, 6).Value = Target.Value + Cells(8, 6).Value
End If

If Target.Row = 10 Then
Cells(8, 6).Value = Application.Round(Target.Value / (1 + VAT), 2)
Cells(9, 6).Value = Target.Value - Cells(8, 6).Value
End If

Application.EnableEvents = True


End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2(a)mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Tina Harrison" <TinaHarrison(a)discussions.microsoft.com> wrote in message
news:BD2956C4-0479-4E22-BC86-A5C68E01C4B3(a)microsoft.com...
>I don't think i explained myself very well.
> I need to enter data in either F9 or H9
> so if i enter data in F9 the answer will be in H9 and vise versa
> and then maybe enter a formula in G10 to show the VAT.
> Can this be done?
>
> Thank you for your help
>
> Tina
>
> "Peo Sjoblom" wrote:
>
>> You can't have a function in a cell then enter something in that same
>> cell
>>
>> So if you create a 4th and 5th column
>>
>> to get the Net if you enter something in the total use
>>
>> =F10/(1+17.5%)
>>
>> to get the VAT
>>
>> =F10-(F10/(1+17.5%))
>>
>> If you put in the Net in F8
>>
>>
>> then use
>>
>> =F8*17.5%
>>
>> to get the VAT
>>
>> and
>>
>> =F8*(1+17.5%)
>>
>> to get the total
>>
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>> "Tina Harrison" <TinaHarrison(a)discussions.microsoft.com> wrote in message
>> news:CAEAB0AD-6248-4AF9-B007-908F0D2C7DCB(a)microsoft.com...
>> > Hi all
>> >
>> > I have a worksheet like below, It might be easy for some but i'm a bit
>> > rusty.
>> >
>> > F8 F9 F10
>> > Net VAT Total
>> >
>> > What i need is a function that will allow me to enter a amount in
>> > either
>> > Net
>> > or Total and it would work out Vat and either Net or Total depending
>> > where
>> > i
>> > have put the amount. Hope this makes sense!
>> >
>> > Many thanks
>> >
>> > Tina
>> >
>> >
>>
>>
>>
>


First  |  Prev  | 
Pages: 1 2
Prev: "manual page breaks error"
Next: Wonderware Active Factory Add In Problem