Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Formula to identify next cell in column with different value

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Must leave on travel, will probably be a few days before I can try your formula and respond. Thanks, appreciate all the efforts.

  2. #12
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, here's a small sample

    {"YEARS","SICK","VACATION";
    1,116.25,77.5;2,155,77.5;
    3,155,77.5;4,232.5,77.5;
    5,232.5,116.25;6,232.5,116.25;
    7,310,116.25;8,310,116.25;
    9,310,116.25;10,465,116.25;
    11,465,116.25;12,465,116.25;
    13,465,116.25;14,465,116.25;
    15,620,155;16,620,155}

    [ This Message was edited by: Juan Pablo G. on 2002-03-19 19:37 ]

  3. #13
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Meant to include a bit more explanation. Will write formula to determine which yearly anniversary falls in this year. Assume 4th. Sample data shows 232.5 sick and 77.5 vacation earned in current year. Those values get plugged in cells on employee's worksheet. Also want to show when next higher level of sick and vacation will happen. Sample data shows year 7 for sick and year 5 for vacation. Employee worksheet will then show HireDate + 7 years is next sick increment and hours goes to 310; HireDate + 5 years is next vacation increment and hours goes to 116.25.

    [ This Message was edited by: pilot on 2002-03-19 19:17 ]

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

    Default

    On 2002-03-19 19:15, pilot wrote:
    Meant to include a bit more explanation. Will write formula to determine which yearly anniversary falls in this year. Assume 4th. Sample data shows 232.5 sick and 77.5 vacation earned in current year. Those values get plugged in cells on employee's worksheet. Also want to show when next higher level of sick and vacation will happen. Sample data shows year 7 for sick and year 5 for vacation. Employee worksheet will then show HireDate + 7 years is next sick increment and hours goes to 310; HireDate + 5 years is next vacation increment and hours goes to 116.25.

    [ This Message was edited by: pilot on 2002-03-19 19:17 ]
    Pilot,

    Thanks providing the sample data & explanation.

    I'll assume that A1:C17 in sheet x houses the data you provided, including labels. I'll further assume that you have a sheet called Employee, which has 4 as value (your example) in A2 (either computed or inputted).

    In B2 enter:

    =OFFSET(x!$B$2,MATCH(A2,x!$A$2:$A$17),0)

    In C2 enter:

    =OFFSET(x!$C$2,MATCH(A2,x!$A$2:$A$17,0)-1,0)

    In A3 enter:

    =INDEX(x!$A$2:$A$17,MATCH(B3,x!$B$2:$B$17,0))

    In B3 enter:

    =OFFSET(x!$B$2,MATCH(B2,x!$B$2:$B$17),0)

    In C3 enter:

    =OFFSET(x!$C$2,MATCH(B2,x!$B$2:$B$17),0)

    [ If the formula does not work the way you want it, try

    =OFFSET(x!$C$2,MATCH(A3,x!$A$2:$A$17),0)

    instead. Both formulas return the same value, but pick out that value differently. ]

    A3:C3 should be downwards draggable/copiable.

    Alternatively, in x, name the range A2:A17 Years via the Name box, the range B2:B17 SickLevel, and the range C2:C17 Vacation. Then,

    in B2 enter:

    =INDEX(SickLevel,MATCH(A2,Years))

    in C2 enter:

    =INDEX(Vacation,MATCH(A2,Years),0)

    in A3 enter:

    =INDEX(Years,MATCH(B3,SickLevel,0))

    in B3 enter:

    =INDEX(SickLevel,MATCH(B2,SickLevel)+1)

    in C3 enter:

    =INDEX(Vacation,MATCH(B2,SickLevel),0)

    [ If the formula does not work the way you want it, try

    =INDEX(Vacation,MATCH(A3,Years),0)

    instead. Both formulas return the same value, but pick out that value differently. ]

    A3:C3, like as above, should be downwards draggable/copiable.

    That is what I get in the processing area in Employee:

    {4,232.5,77.5;
    7,310,116.25}

    Hope this is what you're looking for.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-20 14:56 ]

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
  •