Home 
Search 
Today's Posts 
#1




Display answer only in another cell of one containing a formula
I have a a cell containing a formula to display a total time.
e.g. A1 contains =SUMIF($E$5:$E$25,$E28,$D$5:$D$25). This is my actual formula. The answer it gives is 01:00 in time format. Is it possible to display the answer to the formula in cell B1 but not to contain the formula. B1 needs to change as A1 does. 
#2




type in B1
=INDIRECT("A1") still B1 will contain some formula but may not be the formula of A1. however as the formula in A1 changes the value in B1 also will change. is this what you want?? Mally wrote in message ... I have a a cell containing a formula to display a total time. e.g. A1 contains =SUMIF($E$5:$E$25,$E28,$D$5:$D$25). This is my actual formula. The answer it gives is 01:00 in time format. Is it possible to display the answer to the formula in cell B1 but not to contain the formula. B1 needs to change as A1 does. 
#3




Rightclick on the worksheet tab, View Code, and insert
the code below. Press ALT+Q to active XL: Sub Worksheet_Calculate() With Range("B1") ..Value = .Offset(0, 1).Value End With End Sub  B1 will equal the value of A1 whenever the sheet is recalc'ed. HTH Jason Atlanta, GA Original Message I have a a cell containing a formula to display a total time. e.g. A1 contains =SUMIF($E$5:$E$25,$E28,$D$5:$D$25). This is my actual formula. The answer it gives is 01:00 in time format. Is it possible to display the answer to the formula in cell B1 but not to contain the formula. B1 needs to change as A1 does. . 
#4




Thanks for the help but this comes up with a compile error on the Sub
Worksheet_Calculate() line. Does this mean i havent got this function installed? Also i used A1 and B1 as an example. I would need this function to work in about 10 different cells H28 will be equal to D28 H29 will be equal to D29 etc. How would i get around this? "Jason Morin" wrote: Rightclick on the worksheet tab, View Code, and insert the code below. Press ALT+Q to active XL: Sub Worksheet_Calculate() With Range("B1") ..Value = .Offset(0, 1).Value End With End Sub  B1 will equal the value of A1 whenever the sheet is recalc'ed. HTH Jason Atlanta, GA Original Message I have a a cell containing a formula to display a total time. e.g. A1 contains =SUMIF($E$5:$E$25,$E28,$D$5:$D$25). This is my actual formula. The answer it gives is 01:00 in time format. Is it possible to display the answer to the formula in cell B1 but not to contain the formula. B1 needs to change as A1 does. . 
#5




Mally,
It should work, are you sure you followed Jason's instructions and put it in the worksheet module. On the other part, use Private Sub Worksheet_Calculate() Range("H28").Value = Range("D28").Value End Sub and just add extra lines for the other cells along the lines shown  HTH Bob Phillips "Mally" wrote in message ... Thanks for the help but this comes up with a compile error on the Sub Worksheet_Calculate() line. Does this mean i havent got this function installed? Also i used A1 and B1 as an example. I would need this function to work in about 10 different cells H28 will be equal to D28 H29 will be equal to D29 etc. How would i get around this? "Jason Morin" wrote: Rightclick on the worksheet tab, View Code, and insert the code below. Press ALT+Q to active XL: Sub Worksheet_Calculate() With Range("B1") ..Value = .Offset(0, 1).Value End With End Sub  B1 will equal the value of A1 whenever the sheet is recalc'ed. HTH Jason Atlanta, GA Original Message I have a a cell containing a formula to display a total time. e.g. A1 contains =SUMIF($E$5:$E$25,$E28,$D$5:$D$25). This is my actual formula. The answer it gives is 01:00 in time format. Is it possible to display the answer to the formula in cell B1 but not to contain the formula. B1 needs to change as A1 does. . 
#6




thanks for your help everyone, i think i have it now
Mally "R.VENKATARAMAN" wrote: type in B1 =INDIRECT("A1") still B1 will contain some formula but may not be the formula of A1. however as the formula in A1 changes the value in B1 also will change. is this what you want?? Mally wrote in message ... I have a a cell containing a formula to display a total time. e.g. A1 contains =SUMIF($E$5:$E$25,$E28,$D$5:$D$25). This is my actual formula. The answer it gives is 01:00 in time format. Is it possible to display the answer to the formula in cell B1 but not to contain the formula. B1 needs to change as A1 does. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How do I set a cell to "Empty" so that it does not display in a ch  Charts and Charting in Excel  
Cell contents vs. Formula contents  Excel Discussion (Misc queries)  
I want the results of a formula to show in cell, NOT THE FORMULA!  Excel Discussion (Misc queries)  
Paste is is copying in formula, but display is wrong.  Excel Discussion (Misc queries)  
Display actual contents of cell  Excel Discussion (Misc queries) 