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

Thread: populating cells based on references from other cells

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'll try to explain. I have two columns of military time (FROMTIME and TOTIME)

    I have other columns within the same sheet titled 0000-0059, 0100-0159, 0200-0259, etc.

    What I need to do is based on the FROMTIME and TOTIME is to place an X in the appropriate columns.

    For example, if the FROMTIME is 1045 and the TOTIME is 1200, I need an X placed in the 1000-1059 column and the 1100-1159 column (not the 1200-1259 column, because the incident didn't potentially happen during that "hour span" - that's a management thing, long story).

    Also, if an incident occurrs from 2300-0145, I need to be able to put the x in the last column (2300-2359) and the first column (0000-0059).

    Is this do-able or is it another "can you total this diagonally for me?" type question?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What's the data type of FROMTIME and TOTIME?

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Consider this where A1:F4 contains...

    {"FROMTIME","TOTIME","0000-0059","0100-0159","0200-0259","0300-0359"
    ;"0025","0101","X","X","",""
    ;"0133","0301","","X","X","X"
    ;"0145","0300","","X","X",""}

    ...and cell C2 (the 1st "X" above) contains the formula...

    =IF(AND($A2>=LEFT(C$1,4),$A2<=RIGHT(C$1,4)),"X",IF(AND($B2>LEFT(C$1,4),$A2<=RIGHT(C$1,4)),"X",""))

    ...which is filled right to column F:F and filled down to row 4:4.

    [ This Message was edited by: Mark W. on 2002-04-17 12:31 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I do it one of two ways -

    Formatted as a custom type "0000", or as time in hh:mm format "14:35"

    I get the information in a data dump from a mainframe, which gets read as a number field, and I usually have that transferred as =(LEFT,a2,2)&":"&(RIGHT,A2,2) into a time formatted field.

    It just depends if I'm going to be fiddling with time calculations or not. So I'm flexible. If one way makes it easier than the other, then I'll do it that way.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm guessing that you missed my reply above. Take a look!

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    heh, I replied while you were replying, I think

    Anyway, that WORKED! Thank you!!!!!

    Usually, it's Aladin who gives me the only concise, working solution.

    You rool. I figured out what half of that formula means, now I just have to figure out the rest of it all that embedding. I'll get it, eventually...

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    oops! if I put in FROMTIME 2345 and TOTIME 0125, it doesn't work

    Seem like it can't handle wrapping from one "day" to the next.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 12:54, jennfurr wrote:
    oops! if I put in FROMTIME 2345 and TOTIME 0125, it doesn't work

    Seem like it can't handle wrapping from one "day" to the next.
    I overlooked that "last" requirement. Let me see what I can do.

    [ This Message was edited by: Mark W. on 2002-04-17 13:28 ]

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, try this in cell C2 (following the same instructions as above)...


    =IF(AND($A2>=LEFT(C$1,4),$A2<=RIGHT(C$1,4)),"X",
    IF(AND($B2>LEFT(C$1,4),$A2<=RIGHT(C$1,4)),"X",
    IF(AND($A2>$B2,$A2<=RIGHT(C$1,4)),"X",
    IF(AND($A2>$B2,OR(LEFT(C$1,4)="0000",RIGHT(C$1,4)="2359")),"X",""))))

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    almost works...

    when I put in something like 2200-0058, that works, but when i put in 2200-0700, it only captures the 2200-2259, 2300-2359, and 0000-0059 columns. It didn't get the other columns up to 0600-0659.


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
  •