|
Connected calculation of three cells
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 |
Connected calculation of three cells
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 |
Connected calculation of three cells
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 "Pieman" wrote in message ... 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 |
Connected calculation of three cells
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" a écrit dans le message de ... 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 |
Connected calculation of three cells
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" a écrit dans le message de ... 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 |
Connected calculation of three cells
|
Connected calculation of three cells
|
Connected calculation of three cells
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 '--------------------------------------- |
| All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com