Mixed variable problem
Hello,
I have a WS that looks like this A B C 1 Net Sales: 1,411,676 Cost % 2 Salaries: 52,800 3.74% Basically C2 is equal to what percentage B2 is of B1. B2= B1 * C2 C2= B2 / B1 I need to design a worksheet where you can change eitehr the cost(B1) or the percentage(C1). Is there a way to do this with a logic statement somehow? I would appreciat any suggestions. Thank you |
Mixed variable problem
You would need to use a macro to do it. A cell can contain a formula or a
value, but not both. Here is some code I posted for Fwday who, strangely enough, had a problem very similar to yours: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo Errhandler If Target.Count 1 Then Exit Sub If Target.Column = 2 Or Target.Column = 3 Then If Len(Trim(Target.Value)) = 0 Then Exit Sub Application.EnableEvents = False If Target.Column = 2 And Target.Row Mod 2 = 0 Then If IsNumeric(Target.Offset(-1, 0)) And _ Not IsEmpty(Target.Offset(-1, 0)) Then If Target.Offset(-1, 0).Value < 0 Then Target.Offset(0, 1).Value = _ Target / Target.Offset(-1, 0) End If End If ElseIf Target.Column = 3 And Target.Row Mod 2 = 0 Then If IsNumeric(Target) Then Target.Offset(0, -1) = Target.Offset(-1, -1) * _ Target End If End If End If Errhandler: Application.EnableEvents = True End Sub This assumes your wages row is an even row and your entry will either be in column B (dollars) or in Column C (percent). The Sales row will always be an odd row. So it works on all cells in columns B and C -- Regards, Tom Ogilvy "Tom" wrote in message ... Hello, I have a WS that looks like this A B C 1 Net Sales: 1,411,676 Cost % 2 Salaries: 52,800 3.74% Basically C2 is equal to what percentage B2 is of B1. B2= B1 * C2 C2= B2 / B1 I need to design a worksheet where you can change eitehr the cost(B1) or the percentage(C1). Is there a way to do this with a logic statement somehow? I would appreciat any suggestions. Thank you |
All times are GMT +1. The time now is 04:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com