Results 1 to 7 of 7

Thread: Formula output result to a different cell

  1. #1
    Join Date
    Jun 2011
    Posts
    98

    Formula output result to a different cell

    Someone help me as soon as possible. Actually I want output the result to a different cell using a formula. In more precise words formula in a1 but the result should be in a2 of the formula. I donít mind if the result in a1 and a2 is duplicated. Also I want the cell to which the result is sent should not be an edited cell i.e. it should not contain any formula. These are my requirements.

  2. #2
    Join Date
    May 2009
    Posts
    527

    Re: Formula output result to a different cell

    Your requirements are little confusing. I think you want A1= in cell A2 type or automatic copy of the result. Let me tell you that I am pretty familiar with A1 type commands, so I can help on that part. For that you need formula in A1 to dictate the results that and those results will be displayed in A2 without actually putting any formula in A2 cell. You have to hide A1 in hidden column and A2 should be visible. To hide the formula, just protect the worksheet but it wonít solve the problem. You should have macro that needs to be able to read the displayed value.

  3. #3
    Join Date
    Apr 2009
    Posts
    491

    Re: Formula output result to a different cell

    In order to change the format of targeting cell you need a macro that read the displayed value. I can help you with this macro event.


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    Target.Offset(1, 0).Value = Target.Value
    End If
    End Sub

  4. #4
    Join Date
    May 2009
    Posts
    542

    Re: Formula output result to a different cell

    As per my concern I am not sure that it is possible to send results of calculation or formula to a cell rather than the one in which there is formula. You can try an event that can replicate the value in order to change the format of the target cell. A macro is capable of reading hidden cell value as well as formula such as

    Code:
    MsgBox Cells(1, 1).Value & " formula is " & Cells(1, 1).
    This will show you the formula.

  5. #5
    Join Date
    Apr 2009
    Posts
    570

    Re: Formula output result to a different cell

    If you ask me then I would like to assure you that if the problem can be solve with the macro event than there is no need to hide anything. The calculation is very easy. Below is the code of a target cell F5. You can take reference from it.
    Code:
    =IF(D5<18.5,"UNDERWEIGHT",IF(D5<25,"NORMAL",IF(D5<30,"OVERWEIGHT",IF(D5>29.9,"OBESE"))))
    The only issue with it is that if you change the value of D5 then F5 does not change or refresh. To change it you have to double click on F5 after each value change.

  6. #6
    Join Date
    May 2009
    Posts
    529

    Re: Formula output result to a different cell

    The following is a code that will help you to control you formatting of cell with micro. Just use the code in your actual source code.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer

    Code:
    If Not Intersect(Target, Range("F5:F7")) Is Nothing Then
    Select Case Target
    Case "UNDERWEIGHT"
    icolor = 3
    Case "NORMAL"
    icolor = 10
    Case "OVERWEIGHT"
    icolor = 3
    Case "OBESE"
    icolor = 3
    Case Else
    'Whatever
    End Select
    
    Target.Interior.ColorIndex = icolor
    End If
    End Sub

  7. #7
    Join Date
    Nov 2008
    Posts
    1,514

    Re: Formula output result to a different cell

    Take reference from the below coding and use in the actual source of the cell. I have edited as per your requirement, so only thing you have to do is copy paste the code.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub
    Range("A2").value = Range("A1").value
    End Sub

Similar Threads

  1. Replies: 4
    Last Post: 24-04-2012, 07:53 AM
  2. Replies: 4
    Last Post: 25-01-2012, 11:05 AM
  3. Clear Content in cell without deleting formula
    By ARTHUR18 in forum Windows Software
    Replies: 3
    Last Post: 05-06-2009, 11:03 AM
  4. How to change cell formula to text in VBA
    By Chhaya in forum Software Development
    Replies: 3
    Last Post: 08-05-2009, 12:12 PM
  5. Text not visible in cell only in Formula Bar (excel 2003)
    By NavinS in forum MS Office Support
    Replies: 1
    Last Post: 14-02-2008, 02:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •