Inserting Calculated Values based on Two Cells in one of the Same Two Cells if one of them is Empty and the other Filled

[all4excel]

Inserting Calculated Values based on Two Cells in one of the Same Two Cells if one of them is Empty and the other Filled.

Dear Masters,

I need to make life easier when entering values in a 3 Cell Calculation.

There are 2 Cells which are always going to be manually Filled and from the other 2, either can be calculated and can be manually entered.

So, what I want is that one cell involved in this calculation is always containing values Entered Manually however, from the other 2 one of them if entered manually then let the third remaining one get the Calculated value Automatically however if this cell is entered Manually then the second cell needs to get the Calculated value Automatically..

I know this cannot be done using Formulas as we cannot overwrite on a Formula and therefore this needs to be done using VBA..

Example:
<TABLE style="WIDTH: 256pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=340><COLGROUP><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696" width=53><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4000" width=125><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" span=2 width=81><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 40pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=21 width=53></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=125>Col A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=81>Col B</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=81>Col C</TD></TR><TR style="HEIGHT: 45.75pt; mso-height-source: userset" height=61><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl75 height=61>ROW 1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; WIDTH: 94pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=125>Total Area in Mtrs</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=81>Cloth Cost</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=81>Rate</TD></TR><TR style="HEIGHT: 19.5pt" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 19.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl75 height=26>ROW 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #953735; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 align=right>153</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=81>30600</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #00b0f0; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=81>200</TD></TR><TR style="HEIGHT: 19.5pt" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 19.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl75 height=26>ROW 3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #953735; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 align=right>153</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #00b0f0; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 width=81>30600</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=81>200</TD></TR><TR style="HEIGHT: 19.5pt" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 19.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=26></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=81></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=81></TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; WIDTH: 256pt; HEIGHT: 50.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 height=67 rowSpan=2 width=340 colSpan=4>If the Manual Entry is done in the Cell B2 then the Cell C2 needs to be VBA Calculated.</TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #00b0f0; WIDTH: 256pt; HEIGHT: 39.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=53 rowSpan=2 width=340 colSpan=4>If the Manual Entry is done in the Cell C2 then the Cell B2 needs to be VBA Calculated.</TD></TR><TR style="HEIGHT: 24.75pt; mso-height-source: userset" height=33></TR></TBODY></TABLE>

Is this possible if yes then please offer a solution as a lot of time is wasted in doing tis calculation and the Fixed Columns is much towards the Left hand side of the screen in the actual file..

Regards
all4excel

 

[p45cal]

This should get you started; in the code module for the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("$B$2:$C$10"), Target) Is Nothing Then
  If Target.Column = 2 Then
'    Target.Offset(, 1) = Target.Value / Cells(Target.Row, 1).Value
    Target.Offset(, 1).FormulaR1C1 = "=RC[-1]/RC[-2]"
  Else
'    Target.Offset(, -1) = Target.Value * Cells(Target.Row, 1).Value
    Target.Offset(, -1).FormulaR1C1 = "=RC[-1]*RC[1]"
  End If
End If
Application.EnableEvents = True
End Sub

There are no checks made for people changing multiple cells at the same time, say by copying data blocks, or deleting whole ranges.

As it stands the code places formulae in the cells, but if you use the commented-out lines instead, values are placed. This has the disadvantage that if people change the value in column A, no recalculation takes place (unless they also change either the value in column B or C of the same row afterwards).

 

[all4excel]

There are no checks made for people changing multiple cells at the same time, say by copying data blocks, or deleting whole ranges.

As it stands the code places formulae in the cells, but if you use the commented-out lines instead, values are placed. This has the disadvantage that if people change the value in column A, no recalculation takes place (unless they also change either the value in column B or C of the same row afterwards).

WOw it reallys works the way I wanted but can you convert the Formulas into Values after Calculation instead of keeping the Formula..

I just tried to put the same code in the Selection_Change Event and it worked partially..

SO is it possible to achieve the same by even making it change whenever there are changes if any of these three cells and not just the 2nd and 3rd...
Sorry, I forgot to mention that earlier as had not figured that untill you made this possible..

Thanks a Lot P45Cal..

Regards
all4excel

 

[p45cal]

If someone changes column A, do you want to adjust column B's or column C's value?
Remember, with the formulae in place, sometimes it's the rate that changes, sometimes the cost.
With the existing formulae in place, nothing has to happen as recalculation takes place automatically.

 

[all4excel]

If someone changes column A, do you want to adjust column B's or column C's value?
Remember, with the formulae in place, sometimes it's the rate that changes, sometimes the cost.
With the existing formulae in place, nothing has to happen as recalculation takes place automatically.

I agree completely with you, its a dicey situation but the values dont change often and the First value seldom changes twice..

So can that be achieved in that case...

What you are saying is absolutely correct that with the Formula in place, it can re-calculate automatically but then incase is someone checks the Individual Cells would be tempted to drag TOp-To-Bottom thinking it as a Linear Formula.

Thanks in advance.

Regards
all4excel

 

[p45cal]

the values dont change often and the First value seldom changes twice..

So can that be achieved in that case...

Unfortunately, computers don't work very well with terms like seldom and often (they're succesful because they deal with 0s and 1s and nothing else).
How about if someone changes the column A cell, the other 2 are blanked out, obliging them to fill in one of them?

 

[all4excel]

Unfortunately, computers don't work very well with terms like seldom and often (they're succesful because they deal with 0s and 1s and nothing else).
How about if someone changes the column A cell, the other 2 are blanked out, obliging them to fill in one of them?

Actually the value in the Col A will be not entered Manually it will be arrived from another Sheet using VLOOKUP so it will be there always so in that case , will it be possible or then if not then we just go your way of blanking..

Thnx already..

Regards
all4excel

 

[p45cal]

The following will not respond to changes in value in column A as a result of cells referred to in the vlookup formula changing.
It will blank columns B and C in the same row when someone manually changes a cell in column A, whether to a formula or a value
Only values are placed in cells:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Range("$A$2:$C$10"), Target) Is Nothing Then
  Select Case Target.Column
    Case 1
      Target.Offset(, 1).Resize(, 2) = Empty
    Case 2
      Target.Offset(, 1) = Target.Value / Cells(Target.Row, 1).Value
      'Target.Offset(, 1).FormulaR1C1 = "=RC[-1]/RC[-2]"
    Case 3
      Target.Offset(, -1) = Target.Value * Cells(Target.Row, 1).Value
      'Target.Offset(, -1).FormulaR1C1 = "=RC[-1]*RC[1]"
  End Select
End If
Application.EnableEvents = True
End Sub

 

[all4excel]

Thanks a lot P45Cal,

This is exactly what I needed and it also is in values so great..!

If this has helped and you can't be bothered to acknowledge it, then I can't be bothered to look at further posts from you.

I definitely acknowledge and appreciate the help provided, however, apart from mentioning it in my post, how else do I add some points for you?
Genuinely , is there an option to add reputation ..

Regards
all4excel

 

[p45cal]

I dunno… having a look… I don't think you do.
There's a rate this thread and mark as solved (the latter only in some browsers) but those aren't necessarily related to a person, just the thread.
My signature line is there because I got tired of posting solutions and never hearing from the OP at all, so I was left in a vacuum: was it any good? was it rubbish? Who knows?