Remember Me?

#1
January 21st 05, 10:55 AM
 Mally Posts: n/a
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
January 21st 05, 11:11 AM
 R.VENKATARAMAN Posts: n/a

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

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
January 21st 05, 11:39 AM
 Mally Posts: n/a

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

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
January 21st 05, 12:08 PM
 Bob Phillips Posts: n/a

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

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
January 21st 05, 01:07 PM
 Mally Posts: n/a

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Ian Charts and Charting in Excel 3 January 7th 05 01:12 AM Sarah Excel Discussion (Misc queries) 3 December 15th 04 06:02 PM ocbecky Excel Discussion (Misc queries) 4 December 10th 04 08:39 PM Matt Excel Discussion (Misc queries) 2 December 7th 04 08:37 PM xmasbob Excel Discussion (Misc queries) 1 December 6th 04 05:09 PM

All times are GMT +1. The time now is 05:25 AM.