Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Formula output result to a different cell

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 13-07-2011
Member
 
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.

Reply With Quote
  #2  
Old 13-07-2011
Member
 
Join Date: May 2009
Posts: 523
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.
Reply With Quote
  #3  
Old 13-07-2011
Member
 
Join Date: Apr 2009
Posts: 479
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
Reply With Quote
  #4  
Old 13-07-2011
Member
 
Join Date: May 2009
Posts: 531
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.
Reply With Quote
  #5  
Old 13-07-2011
Member
 
Join Date: Apr 2009
Posts: 564
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.
Reply With Quote
  #6  
Old 13-07-2011
Member
 
Join Date: May 2009
Posts: 520
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
Reply With Quote
  #7  
Old 15-07-2011
Member
 
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
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Formula output result to a different cell"
Thread Thread Starter Forum Replies Last Post
The Search Result in Outlook 2007 does not show current years result Shika Windows Software 4 24-04-2012 07:53 AM
How to remove the default zero in a empty cell with formula of Excel Irritator MS Office Support 4 25-01-2012 11:05 AM
Clear Content in cell without deleting formula ARTHUR18 Windows Software 3 05-06-2009 11:03 AM
How to change cell formula to text in VBA Chhaya Software Development 3 08-05-2009 12:12 PM
Text not visible in cell only in Formula Bar (excel 2003) NavinS MS Office Support 1 14-02-2008 02:32 AM


All times are GMT +5.5. The time now is 03:25 PM.