Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update two cells by changing another
In column A, I have salary. Column B has increase %, column C has
increase amount, column D has total new salary. I need a macro that allows me to update column B, C, or D by entering infomation in either of those. So, if someone enters an increase of 5% in column B on a 10,000 salary, then column C should change to $500 and column D should change to 10,500. Or, if they enter 10,500 in column D, the other two cells should change, etc. Can anyone help with this? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update two cells by changing another
One way:
Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim nCol As Long With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Range("B2:D1000")) Is Nothing Then On Error GoTo ErrorOrExit Application.EnableEvents = False nCol = .Column With Cells(.Row, 1).Resize(1, 4) Select Case nCol Case 2 .Cells(3).Value = .Cells(1).Value * .Cells(2).Value .Cells(4).Value = .Cells(1).Value + .Cells(3).Value Case 3 .Cells(2).Value = .Cells(3).Value / .Cells(1).Value .Cells(4).Value = .Cells(1).Value + .Cells(3).Value Case 4 .Cells(3).Value = .Cells(4).Value - .Cells(1).Value .Cells(2).Value = .Cells(3).Value / .Cells(1).Value End Select End With ErrorOrExit: Application.EnableEvents = True End If End With End Sub In article .com, wrote: In column A, I have salary. Column B has increase %, column C has increase amount, column D has total new salary. I need a macro that allows me to update column B, C, or D by entering infomation in either of those. So, if someone enters an increase of 5% in column B on a 10,000 salary, then column C should change to $500 and column D should change to 10,500. Or, if they enter 10,500 in column D, the other two cells should change, etc. Can anyone help with this? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update two cells by changing another
You can do this with 2 columns or rows in Excel. One to hold the input of
B, C, D and the other to do the conditional calculations. An input rule would have to be followed that that only one cell in the input set (B,C,D) has a value at any given time; meaning that the user will have to clear the values in the set before entering a new value. I would set it up as follows. Headings A1: Salary A2: % Increase A3: Increase Amount A4: New Total Highlight cells B2, B3, B4, C1; these are the input cells. Only one B cell can have a value at any one time. Format all cells Comma Style (the comma button on the Formatting toolbar) except cells B2 and C2 which should be formatted Percent Style (the percent button). B4: =IF(COUNT(B2:B4)1, "Too many inputs", "") C1: 100000 C2: =IF(NOT(ISBLANK(B2)),B2,IF(NOT(ISBLANK(B3)), B3/C1, B4/C1-1)) C3: =IF(NOT(ISBLANK(B3)),B3,IF(NOT(ISBLANK(B2)),B2*C1, B4-C1)) C4: =IF(NOT(ISBLANK(B4)),B4,IF(NOT(ISBLANK(B2)),(1+B2) *C1,B3+C1)) All done. You can change any off the highlighted cells but, again, be sure that only one of the B cells has a value at any one time. E wrote in message oups.com... In column A, I have salary. Column B has increase %, column C has increase amount, column D has total new salary. I need a macro that allows me to update column B, C, or D by entering infomation in either of those. So, if someone enters an increase of 5% in column B on a 10,000 salary, then column C should change to $500 and column D should change to 10,500. Or, if they enter 10,500 in column D, the other two cells should change, etc. Can anyone help with this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update worksheet when changing another sheet | New Users to Excel | |||
update a sheet automatically with changing values from another she | Excel Worksheet Functions | |||
Why won't my pie charts update with changing data? | Charts and Charting in Excel | |||
Update date when changing a cell | Excel Programming | |||
How do I set my webpage to automatically update a changing spread. | Excel Discussion (Misc queries) |