Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: What Do I do NOW

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Macon, Georgia USA
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have this formula:
    =if(or(c6=11,c6=0),"",vlookup(c6,dutycode!a4:a15,4,))

    My problem is when a number is not on the lookup list I get a #N/A in the same cell as the formula (F6).
    sheet 01 cell c6 is where I input a code,
    sheet(dutycode) is where vlookup searches for the correct codes to enter in CELL F6.
    I do not know what to do?
    this is what is also needed;
    if c6>=39
    if c6<=410
    if c6>=412
    if c6<=610
    if c6>=612,"")
    The codes that need to have a response to in cell F6 are 11,12,26,27,29,31,32,38,411,611
    that is why I want everything else to show a blank.
    Thank you,
    Walter

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-05 19:27, TiggerToo wrote:
    I have this formula:
    =if(or(c6=11,c6=0),"",vlookup(c6,dutycode!a4:a15,4,))

    My problem is when a number is not on the lookup list I get a #N/A in the same cell as the formula (F6).
    sheet 01 cell c6 is where I input a code,
    sheet(dutycode) is where vlookup searches for the correct codes to enter in CELL F6.
    I do not know what to do?
    this is what is also needed;
    if c6>=39
    if c6<=410
    if c6>=412
    if c6<=610
    if c6>=612,"")
    The codes that need to have a response to in cell F6 are 11,12,26,27,29,31,32,38,411,611
    that is why I want everything else to show a blank.
    Thank you,
    Walter
    I think
    =IF(COUNTIF(dutycode!$A$1:$A$10,C6),VLOOKUP(C6,dutycode!$A$1:$D$10,4,0),"")

    would do the trick, assuming your range is A1:D10 on you dutycode page. the countif range should be the left most column of your range.



  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,633
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-05 19:27, TiggerToo wrote:
    I have this formula:
    =if(or(c6=11,c6=0),"",vlookup(c6,dutycode!a4:a15,4,))

    My problem is when a number is not on the lookup list I get a #N/A in the same cell as the formula (F6).
    sheet 01 cell c6 is where I input a code,
    sheet(dutycode) is where vlookup searches for the correct codes to enter in CELL F6.
    I do not know what to do?
    this is what is also needed;
    if c6>=39
    if c6<=410
    if c6>=412
    if c6<=610
    if c6>=612,"")
    The codes that need to have a response to in cell F6 are 11,12,26,27,29,31,32,38,411,611
    that is why I want everything else to show a blank.
    Thank you,
    Walter
    Walter,

    The formula that IML suggests in response to your current thread has been already given to you in the previous thread.

    Apparently, such a formula doesn't fit your needs. That's surprising.

    Care to post the lookup table that you're trying to use?

    Aladin

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Macon, Georgia USA
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the lookup table I am using.
    column A Column B Column C
    DUTY CODES SHRED CODES EXPLANATION
    11 Direct Labor (REG) Blank space
    411 Over Time OT
    611 Compensate CT
    12 Direct TDY
    26 30 Shop Clean-up SHPC
    27 1 Training TRNG
    29 Union Activities UNIN
    31 Annual Leave AL
    32 Sick Leave SL
    33 44 Holiday HOL
    38 Compensatory Leave CTL
    What I explained earlier, I would like for
    a blank to appear whenever a number that
    is not on the lookup table is entered in
    column

    Below is a small sample of my excel sheet that I use for time. This is the way

    col a col b col c col d
    date code hrs type code

    ______________________________
    M 3-1-02 | 27 | 3 | TRNG
    M 3-1-02 | 11 | 3 | (REG)Work is Blank
    M 3-1-02 | 31 | 2 | AL
    T 3-2-02 | 11 | 8 |
    W 3-3-02 | 11 | 2 |
    w 3-3-02 | 27 | 2 |Inserted extra line
    W 3-3-02 | 32 | 4 | SL

    NOW For Part 2!!!

    I also need a formula to total each day's hours, not to exceed 8 hrs, unless OT code is in col B.
    My other problem is on the summary sheet, I have 14 columns, by 26 rows (PayPeriods), One column for each day of the payperiod. The only times that go on this summary sheet are ANNUAL LEAVE, SICK LEAVE or HOLIDAY. I do not know how to write a formula to look at more than one line if the same date goes on more than one line.
    If a line is inserted in the above form, Annual Leave will move over one day later which I do not want to happen)or it will not appear at all.
    # PayPeriod.....S.M.T.W.T.F.S/S.M.T.W.T.F.S
    06 Mar 24 Apr 06....3.4
    07 Apr 07 Apr 20....
    08 Apr 21 May 04
    09 May 05 May 18
    10 May 19 Jun 01

    HELP,

    Thanks
    Walter

    [ This Message was edited by: TiggerToo on 2002-03-07 02:39 ]

    [ This Message was edited by: TiggerToo on 2002-03-08 18:24 ]

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
  •