Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

format calculator in same cell

Is there anyway in excel, if a user enters a value in a cell, i.e. 4, it automatically performs a calculation to change that value. For example in A1, if a user types 4, in cell B1 I have the value 4 pre-populated, so I want A1 to show the output of the formula

A1*B1,

so if the user types 4, it will return 16. I cant see anyway to do this via formatting.
0
pma111
Asked:
pma111
  • 3
  • 3
2 Solutions
 
Rgonzo1971Commented:
Hi,

Why don't you want the result in another cell?

Regards
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

I don't think so that the same can be done.

So Rethink on it....
0
 
MacroShadowCommented:
Put this code in the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Value = Target.Value * Target.Offset(, 1).Value
End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Rgonzo1971Commented:
Hi,

Of course it is possible to make it but it is bad design to have input and result at the same place
Say your are tipping 12 answer a call and do not remember what you tipped you won't know whether 12 is the input or the result.

Regards
0
 
MacroShadowCommented:
Sorry, this is the code you need:
Dim blnFlag As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Not blnFlag Then
            blnFlag = True
            Target.Value = CLng(Target.Value) * CLng(Target.Offset(, 1).Value)
        End If
    End If
    blnFlag = False
End Sub

Open in new window

0
 
Rgonzo1971Commented:
@MacroShadow

Wouldn't be easier to disable the events instaed of a flag

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Target.Value = Target.Value * Target.Offset(, 1).Value
    End If
Application.EnableEvents = True
End Sub

Open in new window

0
 
MacroShadowCommented:
Yes it would. Although personally I wouldn't recommend it without thoroughly examining the workflow.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now