Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 21st 05, 10:55 AM
Mally
 
Posts: n/a
Default 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   Report Post  
Old January 21st 05, 11:11 AM
R.VENKATARAMAN
 
Posts: n/a
Default

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   Report Post  
Old January 21st 05, 11:18 AM
Jason Morin
 
Posts: n/a
Default

Right-click 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   Report Post  
Old January 21st 05, 11:39 AM
Mally
 
Posts: n/a
Default

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:

Right-click 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   Report Post  
Old January 21st 05, 12:08 PM
Bob Phillips
 
Posts: n/a
Default

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:

Right-click 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   Report Post  
Old January 21st 05, 01:07 PM
Mally
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I set a cell to "Empty" so that it does not display in a ch Ian Charts and Charting in Excel 3 January 7th 05 01:12 AM
Cell contents vs. Formula contents Sarah Excel Discussion (Misc queries) 3 December 15th 04 06:02 PM
I want the results of a formula to show in cell, NOT THE FORMULA! ocbecky Excel Discussion (Misc queries) 4 December 10th 04 08:39 PM
Paste is is copying in formula, but display is wrong. Matt Excel Discussion (Misc queries) 2 December 7th 04 08:37 PM
Display actual contents of cell xmasbob Excel Discussion (Misc queries) 1 December 6th 04 05:09 PM


All times are GMT +1. The time now is 01:49 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017