Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Multiple results in the same cell

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Houston, Texas
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Houston, Texas
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    May 2002
    Location
    Houston, Texas
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


  10. #10
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •