Multiple results in the same cell

[Vwman]

I have a column with 23 cells. (One for each workday) Each day I will enter data that will give a result in that column.
For example F3 to F25. I will enter data in C3 and a result will appear in F3. Then data in C4, result in F4, etc.

I want the result in the F column to appear also in F27 with F27 changing to the new result each time I enter data in the C column.
If I enter data in C5 the result will appear in F5 and F27. When I enter data in C6 the result will appear in F6 but I want it
also in F27 instead of the F5 data. I have different formulas, with different results, that I want to display in the same cell. Each new
result takes priority over the previous result. Each new result could be higher or lower than the previous result. How do I do it?

I hope that makes sense.

[Tom Urtis]

Can you clarify please, if there are any formulas involved or needing to be involved in column F? I'm not sure if you mean by your post that if you enter a "5" in C4, that a "5" becomes displayed in F4 and F27, or if you have something else in mind. Are all the values in column C manually entered, or do they change by formulas?

Thanks...it sounds do-able, just need a bit of clarification.

[Tom Urtis]

Well, my girlfriend just got my dinner ready (fried chicken tonite), so while I'm chewing on that, here's a stab at what I think you might be requesting.

Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C3:C25")) Is Nothing Then Exit Sub
Target.Offset(0, 3).Value = Target.Value
Range("$F$27").Value = Target.Value
End Sub

Any help? If not please repost.

[Yogi Anand]

Hi Vwman:
In addition to what Tom Urtis has suggested, you can also do it will formulas ... your post delineates the hierarcy of which formula has precedence over the others.

Regards!

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 13:18 ]

[Vwman]

Didn't work.

I will manually enter a number in C4. F4 is a couple of nested functions. Specifically:
=IF(C4>0,SUM($C$3:$C4)/B4*$B$27,0)
I want the result of this both in F4 and F27.
Then when I enter a number in C5, I want it displayed in F5 and F27.

Now, really, the only one that matters is F27. I just want it updated every time I add a number to the C column. In fact I would do better without the F3 to F25 cells but I need those formulas.

I'd be happy to email anyone a small sample of what I'm trying to do.

Thanks!

[Tom Urtis]

Well now that we know a little more about your set-up, try this code instead, again in your worksheet module (right click on the sheet tab, left click on View Code, and paste this in, in place of the first code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C3:C25")) Is Nothing Then Exit Sub
Range("$F$27").Value = Target.Offset(0, 3).Value
End Sub

Is this any better?

[Nimrod]

I have a solution that has only one requirement ! That requirement is that Column F must be empty for any day below the current entry. For example if today you were populating C7 and F7 then F8:F26 must be empty of values.
This requirement could be achieved by appending a small Value test to the formulas you have in F1:F26 like :=IF(C2>0,F2 FORUMLA HERE,"")

ANYWAY enough preamble heres the formula that worked for me when placed in F27

F27 should have the formula =OFFSET(F27,(COUNT(F1:F26)-27),0)

[Nimrod]

If you can have the C: column values be Blank below the current day entry then this forumla could also be used in F27: (this solution does not require any modifications to F1:F26)

Formula in F27:
=OFFSET(F27,(COUNT(C1:C26)-27),0)

[ This Message was edited by: Nimrod on 2002-05-07 21:34 ]

[Vwman]

Tom, your solution seems to work just fine.
Thank you! Your answer is over my head, I'll have to study it some.

Nimrod, your solution seemed to work just fine too, until I had an entry in the C column that was a zero. Then it fell apart. I'll have to study yours some too.
Thank you both for your help!

[Nimrod]

Like I said VWman there can be no value in the cells below the current days value. That includes zeros . To not have zeros show up in row F1:F16 use the modified formula I recommended
To not have zero's show up in C try going to Tools... optoins ... General Tab and UNCHECK the Show Zero checkbox.