Posts by jindon

  • Reference Above Cell if no value is entered in referenced cell

    morsejon


    MAX(IF(Sheet1!A$1:A1<>"",ROW(A$1:A1)))


    Since Sheet!A$1 and ROW(A$1 are fixed, they won't change as you go down the row, but A1s change, increase by 1 as you go down.
    Formula above returns the corresponding row number out of 1:1 (ROW(A$1:A1)) is equal to maximum row which contains any value within the range of A1 to A and current row on Sheet1.


    Therefore, if you need to modify above portion, you need to match dynamic part of range which is A1 part to the first refernce row address.
    In case of A7, the portion is like
    MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7)))
    then, in any cell on Sheet2
    =IF(Sheet1!A7<>"",Sheet1!A7,INDEX(Sheet1!A$1:A7,MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7))),1))


    Or, is this easier to understand?
    =IF(Sheet1!A7<>"",Sheet1!A7,INDIRECT("Sheet1!A"&MAX(IF(Sheet1!A$1:A7<>"",ROW(A$1:A7)))))
    Good luck


    Jindon

  • Ranking system

    1) I used column L in "People in Shift" sheet as working column to find rank if cells in column A has value
    [INDENT]e.g. If A8 is Brenda, L8 finds Brenda's rank from Ranking Sheet which is 5.
    Each cell up to L23 does the same for corresponding A value.
    And I made the column invisible setting font colour with white.[/INDENT]


    2) Cells in Column F then find the smallest value within column L and display corresponding value in column G in Ranking sheet.
    Actually, I'd like to cahnge the formula for column F as shown below
    [INDENT]Cell F8:
    =IF(A8="","",INDEX(Ranking!G:I,MATCH(SMALL(L:L,ROW(A1)),Ranking!I:I,0),1))
    Then just Enter instead of Ctrl+Shift+Enter.
    SMALL(L:L,ROW(A1))
    ROW(A1) is the substitute of 1 and it increases as you drag down, therefore if you go next row, it will be the second smallest and so forth.
    ROW(A1), therefore, nothing to do with actual cell A1.[/INDENT]


    Does it help you?


    Jindon

  • Split sentence

    Hi, ComaGlove


    I hope the following set of formula will help you to do what you want.
    If you have your sentence in Col A then
    B1:
    =IF(COUNTA(A1),LEFT(A1,FIND(" ",A1,1)-1),"")
    C1:
    =IF(COUNTA(A1),RIGHT(A1,LEN(A1)-LEN(B1)-1),"")


    Jindon

  • vlookup problem

    Hi, Eagle


    I wonder if the formula right?


    I'm using comma, NOT SEMICOLON, as a separater like:
    =VLOOKUP(A2,$L$2:$M$554,2,FALSE)


    If it doesn't help, would you check the cell format?
    When you set "Text" on that cell, it looks like that.
    You need to set it back to General and also go back to the cell and enter once.
    I hope that will give you the right answer


    Jindon

  • Duplicating contents of one Excel sheet column variable times into another.

    Hi, brerigo


    This is how it works: =INDIRECT("A"&ROUNDUP(ROW(A1)/$C$1,0))
    1) Function INDIRECT gives you the value of the cell you named.
    e.g. If you put =INDIRECT("A1") in any cell, you can get the value of the cell A1
    2) ROUNDUP(ROW(A1)/$C$1,0) part is the key.
    ROW(A1) is in substitute for 1, and it increases as you fill down.
    Assume 3 entered in cell C1. It gives you the calculation of ROUNDUP OF 1/3 with no decimal.
    If you put that part of the fomula =ROUNDUP(ROW(A1)/$C$1,0), you can get 1 as a result. So, simply, if you want to start the duplicate data from the second row, put the given formula in the second row and it starts from there.


    3) If the original data has heading in the first row, you need to add 1 in that part
    That is ROUNDUP(ROW(A1)/$C$1,0)+1
    So the formula looks like =INDIRECT("A"&ROUNDUP(ROW(A1)/$C$1,0)+1)


    I hope you can now manage it as you like.


    Jindon