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.

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.

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.
.

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.
.

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.
.

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.