Mixed variable problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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
Click to expand...
the percentage(C1). Is there a way to do this with a logic statement
somehow? I would appreciat any suggestions.
 
Back
Top