Display answer only in another cell of one containing a formula

M

Mally

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

Jason Morin

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
 
M

Mally

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?
 
B

Bob Phillips

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
 
Top