Connected calculation of three cells

[Pieman]

Hi

I have three cells in a single worksheet that represent a weekly, monthly
and annual sales target. Does anyone know the formula to ensure that if a
target is eneterd in any of three cells, the other two are automatically
calculated.

For example, if I enter a weekly target, the monthly target is calculated by
multiplying the weekly figure entered by 52 and dividing by 12. This should
also calculate the annual figure in the third cell.

The part I can't work out is how to allow the target to be entered in any
three cells and the other two be automatically calculated.

Any ideas would be greatly appreciated.

Thanks
Simon

[bpeltzer]

If you want to allow any one of the cells to be entered and the other two
calculated, I think doing this entirely with formulas would require circular
references. I'd either use VBA to detect the data entry (the
worksheet_change event) and fill in the other two cells or separate the input
cells from the three different output cells.
If you choose the latter approach, I'd probably provide two input cells, one
for the target and the other a dropdown (using data validation) to select
'Per Week', 'Per Month' or 'Per Year'. Then I'd calculate the annual target
based on those two cells, and the weekly and monthly goals by multiplying the
annual target by 1/52 and 1/12, respectively.

"Pieman" wrote:

> Hi
>
> I have three cells in a single worksheet that represent a weekly, monthly
> and annual sales target. Does anyone know the formula to ensure that if a
> target is eneterd in any of three cells, the other two are automatically
> calculated.
>
> For example, if I enter a weekly target, the monthly target is calculated by
> multiplying the weekly figure entered by 52 and dividing by 12. This should
> also calculate the annual figure in the third cell.
>
> The part I can't work out is how to allow the target to be entered in any
> three cells and the other two be automatically calculated.
>
> Any ideas would be greatly appreciated.
>
> Thanks
> Simon

[Don Guillett]

This can be done with a worksheet_change event where
if target.column=2 then
target.offset(0,-1)=
target.offset(0,1)=
else
eee
end if


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Pieman" <Pieman@discussions.microsoft.com> wrote in message
news:65E9B799-F422-435A-96DC-B906BAF557BB@microsoft.com...
> Hi
>
> I have three cells in a single worksheet that represent a weekly, monthly
> and annual sales target. Does anyone know the formula to ensure that if a
> target is eneterd in any of three cells, the other two are automatically
> calculated.
>
> For example, if I enter a weekly target, the monthly target is calculated
> by
> multiplying the weekly figure entered by 52 and dividing by 12. This
> should
> also calculate the annual figure in the third cell.
>
> The part I can't work out is how to allow the target to be entered in any
> three cells and the other two be automatically calculated.
>
> Any ideas would be greatly appreciated.
>
> Thanks
> Simon

[Ardus Petus]

Assuming your data (Week/Month/Year) are in cells B1 to B3,
enter following sub in your worksheet's code:

'---------------------------------------------------------------------------
---
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B3")) Is Nothing _
Or Target.Count <> 1 _
Then Exit Sub
Application.EnableEvents = False
With Target
If .Value = "" Then
Range("B1:B3").Value = ""
Else
Select Case Target.Row
Case 1 'Weekly
Range("B2").Value = .Value * 52 / 12
Range("B3").Value = .Value * 52
Case 2 'Monthly
Range("B1").Value = .Value * 12 / 52
Range("B3").Value = .Value * 12
Case 3 'Yearly
Range("B1").Value = .Value / 12
Range("B2").Value = .Value / 52
End Select
End If
End With
Application.EnableEvents = True
End Sub

'---------------------------------------------------------------------------
---
HTH
--
AP

"Pieman" <Pieman@discussions.microsoft.com> a écrit dans le message de
news:65E9B799-F422-435A-96DC-B906BAF557BB@microsoft.com...
> Hi
>
> I have three cells in a single worksheet that represent a weekly, monthly
> and annual sales target. Does anyone know the formula to ensure that if a
> target is eneterd in any of three cells, the other two are automatically
> calculated.
>
> For example, if I enter a weekly target, the monthly target is calculated
by
> multiplying the weekly figure entered by 52 and dividing by 12. This
should
> also calculate the annual figure in the third cell.
>
> The part I can't work out is how to allow the target to be entered in any
> three cells and the other two be automatically calculated.
>
> Any ideas would be greatly appreciated.
>
> Thanks
> Simon

[Pieman]

Ardus

Thanks for your response. The three cells are E4:E6 on worksheet 'Adviser
Stats'. I have inserted the following code in the VB editor but the
calculations don't work:

------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E4:E6")) Is Nothing _
Or Target.Count <> 1 _
Then Exit Sub
Application.EnableEvents = False
With Target
If .value = "" Then
Range("E4:E6").value = ""
Else
Select Case Target.Row
Case 1 'Weekly
Range("E5").value = .value * 52 / 12
Range("E6").value = .value * 52
Case 2 'Monthly
Range("E4").value = .value * 12 / 52
Range("E6").value = .value * 12
Case 3 'Yearly
Range("E4").value = .value / 12
Range("E5").value = .value / 52
End Select
End If
End With
Application.EnableEvents = True
End Sub
-----------------------------------------------------

Any ideas what I'm doing wrong?

Thanks
Simon

"Ardus Petus" wrote:

> Assuming your data (Week/Month/Year) are in cells B1 to B3,
> enter following sub in your worksheet's code:
>
> '---------------------------------------------------------------------------
> ---
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Intersect(Target, Range("B1:B3")) Is Nothing _
> Or Target.Count <> 1 _
> Then Exit Sub
> Application.EnableEvents = False
> With Target
> If .Value = "" Then
> Range("B1:B3").Value = ""
> Else
> Select Case Target.Row
> Case 1 'Weekly
> Range("B2").Value = .Value * 52 / 12
> Range("B3").Value = .Value * 52
> Case 2 'Monthly
> Range("B1").Value = .Value * 12 / 52
> Range("B3").Value = .Value * 12
> Case 3 'Yearly
> Range("B1").Value = .Value / 12
> Range("B2").Value = .Value / 52
> End Select
> End If
> End With
> Application.EnableEvents = True
> End Sub
>
> '---------------------------------------------------------------------------
> ---
> HTH
> --
> AP
>
> "Pieman" <Pieman@discussions.microsoft.com> a écrit dans le message de
> news:65E9B799-F422-435A-96DC-B906BAF557BB@microsoft.com...
> > Hi
> >
> > I have three cells in a single worksheet that represent a weekly, monthly
> > and annual sales target. Does anyone know the formula to ensure that if a
> > target is eneterd in any of three cells, the other two are automatically
> > calculated.
> >
> > For example, if I enter a weekly target, the monthly target is calculated
> by
> > multiplying the weekly figure entered by 52 and dividing by 12. This
> should
> > also calculate the annual figure in the third cell.
> >
> > The part I can't work out is how to allow the target to be entered in any
> > three cells and the other two be automatically calculated.
> >
> > Any ideas would be greatly appreciated.
> >
> > Thanks
> > Simon
>
>
>

[Don Guillett]

In THIS PARTICULAR case you would need to change case to 4,5,6 instead of
1,2,3

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Pieman" <Pieman@discussions.microsoft.com> wrote in message
news:6D7F611C-CDAA-4646-BABB-395C9D59D1E6@microsoft.com...
> Ardus
>
> Thanks for your response. The three cells are E4:E6 on worksheet 'Adviser
> Stats'. I have inserted the following code in the VB editor but the
> calculations don't work:
>
> ------------------------------------------------------------------
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Intersect(Target, Range("E4:E6")) Is Nothing _
> Or Target.Count <> 1 _
> Then Exit Sub
> Application.EnableEvents = False
> With Target
> If .value = "" Then
> Range("E4:E6").value = ""
> Else
> Select Case Target.Row
> Case 1 'Weekly
> Range("E5").value = .value * 52 / 12
> Range("E6").value = .value * 52
> Case 2 'Monthly
> Range("E4").value = .value * 12 / 52
> Range("E6").value = .value * 12
> Case 3 'Yearly
> Range("E4").value = .value / 12
> Range("E5").value = .value / 52
> End Select
> End If
> End With
> Application.EnableEvents = True
> End Sub
> -----------------------------------------------------
>
> Any ideas what I'm doing wrong?
>
> Thanks
> Simon
>
> "Ardus Petus" wrote:
>
>> Assuming your data (Week/Month/Year) are in cells B1 to B3,
>> enter following sub in your worksheet's code:
>>
>> '---------------------------------------------------------------------------
>> ---
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Intersect(Target, Range("B1:B3")) Is Nothing _
>> Or Target.Count <> 1 _
>> Then Exit Sub
>> Application.EnableEvents = False
>> With Target
>> If .Value = "" Then
>> Range("B1:B3").Value = ""
>> Else
>> Select Case Target.Row
>> Case 1 'Weekly
>> Range("B2").Value = .Value * 52 / 12
>> Range("B3").Value = .Value * 52
>> Case 2 'Monthly
>> Range("B1").Value = .Value * 12 / 52
>> Range("B3").Value = .Value * 12
>> Case 3 'Yearly
>> Range("B1").Value = .Value / 12
>> Range("B2").Value = .Value / 52
>> End Select
>> End If
>> End With
>> Application.EnableEvents = True
>> End Sub
>>
>> '---------------------------------------------------------------------------
>> ---
>> HTH
>> --
>> AP
>>
>> "Pieman" <Pieman@discussions.microsoft.com> a écrit dans le message de
>> news:65E9B799-F422-435A-96DC-B906BAF557BB@microsoft.com...
>> > Hi
>> >
>> > I have three cells in a single worksheet that represent a weekly,
>> > monthly
>> > and annual sales target. Does anyone know the formula to ensure that if
>> > a
>> > target is eneterd in any of three cells, the other two are
>> > automatically
>> > calculated.
>> >
>> > For example, if I enter a weekly target, the monthly target is
>> > calculated
>> by
>> > multiplying the weekly figure entered by 52 and dividing by 12. This
>> should
>> > also calculate the annual figure in the third cell.
>> >
>> > The part I can't work out is how to allow the target to be entered in
>> > any
>> > three cells and the other two be automatically calculated.
>> >
>> > Any ideas would be greatly appreciated.
>> >
>> > Thanks
>> > Simon
>>
>>
>>

[Pieman]

Great, thank you, that works perfect.

"Don Guillett" wrote:

> In THIS PARTICULAR case you would need to change case to 4,5,6 instead of
> 1,2,3
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Pieman" <Pieman@discussions.microsoft.com> wrote in message
> news:6D7F611C-CDAA-4646-BABB-395C9D59D1E6@microsoft.com...
> > Ardus
> >
> > Thanks for your response. The three cells are E4:E6 on worksheet 'Adviser
> > Stats'. I have inserted the following code in the VB editor but the
> > calculations don't work:
> >
> > ------------------------------------------------------------------
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Intersect(Target, Range("E4:E6")) Is Nothing _
> > Or Target.Count <> 1 _
> > Then Exit Sub
> > Application.EnableEvents = False
> > With Target
> > If .value = "" Then
> > Range("E4:E6").value = ""
> > Else
> > Select Case Target.Row
> > Case 1 'Weekly
> > Range("E5").value = .value * 52 / 12
> > Range("E6").value = .value * 52
> > Case 2 'Monthly
> > Range("E4").value = .value * 12 / 52
> > Range("E6").value = .value * 12
> > Case 3 'Yearly
> > Range("E4").value = .value / 12
> > Range("E5").value = .value / 52
> > End Select
> > End If
> > End With
> > Application.EnableEvents = True
> > End Sub
> > -----------------------------------------------------
> >
> > Any ideas what I'm doing wrong?
> >
> > Thanks
> > Simon
> >
> > "Ardus Petus" wrote:
> >
> >> Assuming your data (Week/Month/Year) are in cells B1 to B3,
> >> enter following sub in your worksheet's code:
> >>
> >> '---------------------------------------------------------------------------
> >> ---
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> If Intersect(Target, Range("B1:B3")) Is Nothing _
> >> Or Target.Count <> 1 _
> >> Then Exit Sub
> >> Application.EnableEvents = False
> >> With Target
> >> If .Value = "" Then
> >> Range("B1:B3").Value = ""
> >> Else
> >> Select Case Target.Row
> >> Case 1 'Weekly
> >> Range("B2").Value = .Value * 52 / 12
> >> Range("B3").Value = .Value * 52
> >> Case 2 'Monthly
> >> Range("B1").Value = .Value * 12 / 52
> >> Range("B3").Value = .Value * 12
> >> Case 3 'Yearly
> >> Range("B1").Value = .Value / 12
> >> Range("B2").Value = .Value / 52
> >> End Select
> >> End If
> >> End With
> >> Application.EnableEvents = True
> >> End Sub
> >>
> >> '---------------------------------------------------------------------------
> >> ---
> >> HTH
> >> --
> >> AP
> >>
> >> "Pieman" <Pieman@discussions.microsoft.com> a écrit dans le message de
> >> news:65E9B799-F422-435A-96DC-B906BAF557BB@microsoft.com...
> >> > Hi
> >> >
> >> > I have three cells in a single worksheet that represent a weekly,
> >> > monthly
> >> > and annual sales target. Does anyone know the formula to ensure that if
> >> > a
> >> > target is eneterd in any of three cells, the other two are
> >> > automatically
> >> > calculated.
> >> >
> >> > For example, if I enter a weekly target, the monthly target is
> >> > calculated
> >> by
> >> > multiplying the weekly figure entered by 52 and dividing by 12. This
> >> should
> >> > also calculate the annual figure in the third cell.
> >> >
> >> > The part I can't work out is how to allow the target to be entered in
> >> > any
> >> > three cells and the other two be automatically calculated.
> >> >
> >> > Any ideas would be greatly appreciated.
> >> >
> >> > Thanks
> >> > Simon
> >>
> >>
> >>
>
>
>

[Ardus Petus]

You only have to change the constants
HTH
--
AP

'-------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Const addrWeek As String = "$E$4"
Const addrMonth As String = "$E$5"
Const addrYear As String = "$E$6"
Dim WMY As Range
Set WMY = Range( _
addrWeek & "," & _
addrMonth & "," & _
addrYear)
If Intersect(Target, WMY) Is Nothing _
Or Target.Count <> 1 _
Then Exit Sub
Application.EnableEvents = False
With Target
If .Value = "" Then
WMY.Value = ""
Else
Select Case Target.Address
Case addrWeek 'Weekly
Range(addrMonth).Value = .Value * 52 / 12
Range(addrYear).Value = .Value * 52
Case addrMonth 'Monthly
Range(addrWeek).Value = .Value * 12 / 52
Range(addrYear).Value = .Value * 12
Case addrYear 'Yearly
Range(addrWeek).Value = .Value / 12
Range(addrMonth).Value = .Value / 52
End Select
End If
End With
Application.EnableEvents = True
End Sub
'---------------------------------------

[Don Guillett]

glad to help. This could be re-written to be more flexible for other
situations.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Pieman" <Pieman@discussions.microsoft.com> wrote in message
news:704A0867-9783-414E-9D04-8ED3307427E3@microsoft.com...
> Great, thank you, that works perfect.
>
> "Don Guillett" wrote:
>
>> In THIS PARTICULAR case you would need to change case to 4,5,6 instead of
>> 1,2,3
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Pieman" <Pieman@discussions.microsoft.com> wrote in message
>> news:6D7F611C-CDAA-4646-BABB-395C9D59D1E6@microsoft.com...
>> > Ardus
>> >
>> > Thanks for your response. The three cells are E4:E6 on worksheet
>> > 'Adviser
>> > Stats'. I have inserted the following code in the VB editor but the
>> > calculations don't work:
>> >
>> > ------------------------------------------------------------------
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > If Intersect(Target, Range("E4:E6")) Is Nothing _
>> > Or Target.Count <> 1 _
>> > Then Exit Sub
>> > Application.EnableEvents = False
>> > With Target
>> > If .value = "" Then
>> > Range("E4:E6").value = ""
>> > Else
>> > Select Case Target.Row
>> > Case 1 'Weekly
>> > Range("E5").value = .value * 52 / 12
>> > Range("E6").value = .value * 52
>> > Case 2 'Monthly
>> > Range("E4").value = .value * 12 / 52
>> > Range("E6").value = .value * 12
>> > Case 3 'Yearly
>> > Range("E4").value = .value / 12
>> > Range("E5").value = .value / 52
>> > End Select
>> > End If
>> > End With
>> > Application.EnableEvents = True
>> > End Sub
>> > -----------------------------------------------------
>> >
>> > Any ideas what I'm doing wrong?
>> >
>> > Thanks
>> > Simon
>> >
>> > "Ardus Petus" wrote:
>> >
>> >> Assuming your data (Week/Month/Year) are in cells B1 to B3,
>> >> enter following sub in your worksheet's code:
>> >>
>> >> '---------------------------------------------------------------------------
>> >> ---
>> >> Private Sub Worksheet_Change(ByVal Target As Range)
>> >> If Intersect(Target, Range("B1:B3")) Is Nothing _
>> >> Or Target.Count <> 1 _
>> >> Then Exit Sub
>> >> Application.EnableEvents = False
>> >> With Target
>> >> If .Value = "" Then
>> >> Range("B1:B3").Value = ""
>> >> Else
>> >> Select Case Target.Row
>> >> Case 1 'Weekly
>> >> Range("B2").Value = .Value * 52 / 12
>> >> Range("B3").Value = .Value * 52
>> >> Case 2 'Monthly
>> >> Range("B1").Value = .Value * 12 / 52
>> >> Range("B3").Value = .Value * 12
>> >> Case 3 'Yearly
>> >> Range("B1").Value = .Value / 12
>> >> Range("B2").Value = .Value / 52
>> >> End Select
>> >> End If
>> >> End With
>> >> Application.EnableEvents = True
>> >> End Sub
>> >>
>> >> '---------------------------------------------------------------------------
>> >> ---
>> >> HTH
>> >> --
>> >> AP
>> >>
>> >> "Pieman" <Pieman@discussions.microsoft.com> a écrit dans le message de
>> >> news:65E9B799-F422-435A-96DC-B906BAF557BB@microsoft.com...
>> >> > Hi
>> >> >
>> >> > I have three cells in a single worksheet that represent a weekly,
>> >> > monthly
>> >> > and annual sales target. Does anyone know the formula to ensure that
>> >> > if
>> >> > a
>> >> > target is eneterd in any of three cells, the other two are
>> >> > automatically
>> >> > calculated.
>> >> >
>> >> > For example, if I enter a weekly target, the monthly target is
>> >> > calculated
>> >> by
>> >> > multiplying the weekly figure entered by 52 and dividing by 12. This
>> >> should
>> >> > also calculate the annual figure in the third cell.
>> >> >
>> >> > The part I can't work out is how to allow the target to be entered
>> >> > in
>> >> > any
>> >> > three cells and the other two be automatically calculated.
>> >> >
>> >> > Any ideas would be greatly appreciated.
>> >> >
>> >> > Thanks
>> >> > Simon
>> >>
>> >>
>> >>
>>
>>
>>