Connected calculation of three cells

P

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
 
B

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

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
 
A

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

'---------------------------------------------------------------------------
 
P

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
 
D

Don Guillett

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

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
'---------------------------------------
 
D

Don Guillett

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