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

 

[all4excel]

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?

Thanks P45Cal for the help and as per your advice I have also rated the thread but have not found any option to Mark it as SOLVED as you mentioned it may or may not be there.

Well I have started a New Query on Random placement of fixed values which is in slight connection to this one..

Would appreciate if you can try helping me on the other one too as its about placing the Known values randomly in fixed columns which are marked by a TIck..

Regards
all4excel

 

[all4excel]

Dear P45cal,

The solution provided is very good but there's a slight change in the Column Positioning so can you please help me on the same..

My New Columns would be as mentioned below:

Column F Column U Column V
<table border="0" cellpadding="0" cellspacing="0" width="340"><tbody><tr 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">
</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">Rate</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></tr></tbody></table>
Not only has the Placement of the Columns changed but also there has been an interchange in the position of the Rate and the Cloth Cost..

One more thing , if you can add to the existing code would be quite beneficial...there are times when the Value in the Column F changes though not frequently but it does change as per the new code it blanks the other two cells...

I am fine with the Blanking but to be on the safer side can we have those values stored in Comments for those 2 very cells and also made visible at the same time only when there's a a change in the First Column F as in the New Placement...

So what would happen is though we blank both the cells we have them stored before clearing them and are storing them in the same cells and making the comments visible immediately so one knows what the previous values were..

This would ensure that we never lose the previous data as we can always use either of the 2 values and get the new results as per the new Total Area in Sq Mtrs.

Regards
all4excel

 

[p45cal]

The following will allow you to have the data in any three columns but there are caveats:
1. The headers must be in row 1
2. The three headers must be exactly as in the code (or change the code):
Total Area in Mtrs, Rate, Cloth Cost
3. Since it looks at the whole sheet there should not be anything below the table in those columns otherwise the code will work on them too.

You need to hover over the cell with the mouse to see the comment.

I played with allowing the user to change multiple cells at once but came into problems when the change event recognised clearing formatting as a change, or deleting/adding columns as a change and would behave as if the Area column had all been changed and then blanked the other two columns!

On that note, would it not be usual, if changing the Area, rather than blanking the other two cells, to keep the Rate value? If so, it would be easier to programme for multiple changes, as in copying and pasting multiple values, changing a bunch of rates at the same time by dragging down etc. At the moment, to get it to recalculate you have to go into each cell (double-click it or press F2) and press enter (effectively to re-enter it) so that the code runs to update the value(s) on the same row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set AreaColumn = Rows(1).Find(what:="Total Area in Mtrs", LookIn:=xlFormulas, lookat:=xlWhole)
If AreaColumn Is Nothing Then Exit Sub
Set RateColumn = Rows(1).Find(what:="Rate")
If RateColumn Is Nothing Then Exit Sub
Set CostColumn = Rows(1).Find(what:="Cloth Cost")
If CostColumn Is Nothing Then Exit Sub
Set SensitiveRegion = Intersect(UsedRange.Offset(1), Union(AreaColumn.EntireColumn, RateColumn.EntireColumn, CostColumn.EntireColumn))
Application.EnableEvents = False
On Error GoTo ****Up
If Not Intersect(SensitiveRegion, Target) Is Nothing Then
    Select Case Target.Column
        Case AreaColumn.Column
            With Cells(Target.Row, CostColumn.Column)
                .ClearComments
                .AddComment "Last Value: " & .Value
                .Comment.Shape.Height = 25    'leave out to leave default comment size
                .Comment.Visible = False
                .Value = Empty
            End With
            With Cells(Target.Row, RateColumn.Column)
                .ClearComments
                .AddComment "Last Value: " & .Value
                .Comment.Shape.Height = 25    'leave out to leave default comment size
                .Comment.Visible = False
                .Value = Empty
            End With
            Application.DisplayCommentIndicator = xlCommentIndicatorOnly
        Case CostColumn.Column
            Cells(Target.Row, RateColumn.Column).Value = Target.Value / Cells(Target.Row, AreaColumn.Column).Value
            Cells(Target.Row, RateColumn.Column).ClearComments
            Cells(Target.Row, CostColumn.Column).ClearComments
        Case RateColumn.Column
            Cells(Target.Row, CostColumn.Column).Value = Target.Value * Cells(Target.Row, AreaColumn.Column).Value
            Cells(Target.Row, RateColumn.Column).ClearComments
            Cells(Target.Row, CostColumn.Column).ClearComments
    End Select
End If
****Up:
Application.EnableEvents = True
End Sub

 

[all4excel]

Dear P45Cal,

Excellent thanks a lot, this is what I was looking out for..but the comments can they be made like Show Comments by default instead of the hovering part if possible ...

I was also trying to display them at a particuar distance away from the Actual Cells with a particular .AutoShapeType = 16 i.e the Folded Corner..

and also some Rupees formatting and also some SpellIndian Fucntion..

I tried it but no good..

Please find my desperate attempt..

    Select Case Target.Column
        Case AreaColumn.Column
            With Cells(Target.Row, CostColumn.Column)
                .ClearComments
                .AddComment "Last Value: " & .Value
                .Comment.Shape.Height = 25    'leave out to leave default comment size
                .Comment.AutoShapeType = 16
                .Comment.Visible = True
                .Comment.Shape.Top = .Comment.Parent.Top + 27
                .Comment.Parent.Offset(0, 2).Left 6
                .Value = Empty
            End With
            With Cells(Target.Row, RateColumn.Column)
                .ClearComments
                .AddComment "Last Value: " & .Value
                .Comment.Shape.Height = 25    'leave out to leave default comment size
                .Comment.AutoShapeType = 16
                .Comment.Visible = True
                .Comment.Shape.Top = .Comment.Parent.Top + 27
                .Comment.Parent.Offset(0, 1).Left 6
                .Value = Empty
            End With

Application.WorksheetFunction.Text(Cells(Target.Row, 8).Value, "[>9999999]""Rs ""#\,##\,##\,##0;[>99999]""Rs ""#\,##\,##0;""Rs ""#,##0")

This helps in giving any number the Rupees Format such as :
Regular number 2332244
Rs 20,32,244

SPELLINDIAN(Cells(Target.Row, 8).Value

This is not the actual code but just an example as this should preoduce a result of the Last Value with Rupees Twenty Lacs and Thirty Thousand and Two Hundred and Forty Four only for the number such as
20,32,244

Thanks a lot again your improvisation is really going to help me immensely and I am really grateful to you for that..

Regards
all4excel

 

[p45cal]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set AreaColumn = Rows(1).Find(what:="Total Area in Mtrs", LookIn:=xlFormulas, lookat:=xlWhole)
If AreaColumn Is Nothing Then Exit Sub
Set RateColumn = Rows(1).Find(what:="Rate")
If RateColumn Is Nothing Then Exit Sub
Set CostColumn = Rows(1).Find(what:="Cloth Cost")
If CostColumn Is Nothing Then Exit Sub
Set SensitiveRegion = Intersect(UsedRange.Offset(1), Union(AreaColumn.EntireColumn, RateColumn.EntireColumn, CostColumn.EntireColumn))
Application.EnableEvents = False
On Error GoTo ****Up
If Not Intersect(SensitiveRegion, Target) Is Nothing Then
  Union(Cells(Target.Row, CostColumn.Column), Cells(Target.Row, RateColumn.Column)).NumberFormat = "[>9999999]""Rs ""#\,##\,##\,##0;[>99999]""Rs ""#\,##\,##0;""Rs ""#,##0"
  Select Case Target.Column
    Case AreaColumn.Column
      With Cells(Target.Row, CostColumn.Column)
        .ClearComments
        .AddComment "Last Value: " & .Value
        .Comment.Shape.Height = 25    'leave out to leave default comment size
        .Comment.Shape.AutoShapeType = 16
        .Comment.Shape.Top = .Comment.Parent.Top + 27
        .Comment.Shape.Left = .Comment.Parent.Offset(0, 2).Left + 6
        .Value = Empty
        .Comment.Visible = True
      End With
      With Cells(Target.Row, RateColumn.Column)
        .ClearComments
        .AddComment "Last Value: " & .Value
        .Comment.Shape.AutoShapeType = 16
        .Comment.Shape.Top = .Comment.Parent.Top + 27
        .Comment.Shape.Left = .Comment.Parent.Offset(0, 1).Left - 6
        .Comment.Shape.Height = 25    'leave out to leave default comment size
        .Value = Empty
        .Comment.Visible = True
      End With
    Case CostColumn.Column
      Cells(Target.Row, RateColumn.Column).Value = Target.Value / Cells(Target.Row, AreaColumn.Column).Value
      Cells(Target.Row, RateColumn.Column).ClearComments
      Cells(Target.Row, CostColumn.Column).ClearComments
    Case RateColumn.Column
      Cells(Target.Row, CostColumn.Column).Value = Target.Value * Cells(Target.Row, AreaColumn.Column).Value
      Cells(Target.Row, RateColumn.Column).ClearComments
      Cells(Target.Row, CostColumn.Column).ClearComments
  End Select
End If
****Up:
Application.EnableEvents = True
End Sub

 

[all4excel]

Thanks once again P45Cal for the Modification...

I made a mistake in explaining as it was at midnight when I was replying ..

I wanted the Number Formats to appear for both the Comments as well and one more line mentioning the Numbers in Words to help the person recollect..

SO the Comments would read like this:

Last Value : Rs 20,32,244
Rupees Twenty Lacs and Thirty Thousand and Two Hundred and Forty Four only

So both the Comments the way they are now plus just these 2 changes...

I really liked the idea of getting the previous values in the comments for future usage..

Regards
all4excel

 

[p45cal]

change both instances of

.AddComment "Last Value: " & .Value

to

.AddComment "Last Value: " & Application.WorksheetFunction.Text(.Value, "[>9999999]""Rs ""#\,##\,##\,##0;[>99999]""Rs ""#\,##\,##0;""Rs ""#,##0")

With regard to SpellIndian, this is beyond the scope of this thread, so start a new one, and remember, the MrExcel site is here to help people with their excel problems and coding, rather than to be a free code-writing service. There is a lot of code on the interweb, including functions to spell numbers out in words in indian currency, I suggest you search for them, try them out, and only start a new thread if you can't make any of them work, explaining how far you've got.

 

[all4excel]

Thanks a lot for the help for the Format..

I was not looking for the help on SpellIndian as I already have the code for the same but was finding it difficult to use in the Comments Syntax..

The way you helped with this line

.AddComment "Last Value: " & Application.WorksheetFunction.Text(.Value, "[>9999999]""Rs ""#\,##\,##\,##0;[>99999]""Rs ""#\,##\,##0;""Rs ""#,##0")

I actually needed to know how to write something as Spellindian((.Value)..

DOnt need help on that ...

YOur help was extremely helpful and very grateful to you for all the efforts taken so far..its just that due to a very tight work schedule I am not able to focus much in my queries and therefore missed certain minute details such as that..

Anyways thanks once again..