Posts by jindon


    Insert one column anywhere you like, say col.C, and put

    in cell C6

    then drag down as you want

    Second problem

    In Cell B5
    In cell B6

    then drag down both cells

    hope this works

    Here's how it works.

    Formula in col.C on Totals Sheet is to find the rank among "EVAL TOTALS", col.B
    and I thought this would be the original ranking.
    One problem!
    Function RANK will rank the same if the test value is the same.
    When it happens, function SMALL will always return the first comer only.
    To avoid this problem, I always use another working column which adds or subtracts ROW(A1)/100 to or from the test value.
    It really avoids this problem. (The attached has been modified already, ref col.G and formula in co.C is now =IF(B2<>"",RANK(G2,G:G),"") )

    Formula in col.L on sheet Shift Input
    It finds the original ranking of the value in col.C from Totals sheet

    Formula in Col.F on sheet Shit Input
    It finds the first smallest value in col.L for the first row and find ‚ƒ‚‚’‚’‚…‚“‚‚‚Ž‚„‚‰‚Žg value in col.A on Totals sheet and second smallest for the second row and so forth.

    Try the attached and compare with the other one

    In cell M1


    holding down Ctrl+Shift and Enter to get out from the formula bar

    then drag down to row 1000th

    I'm not sure if this is what you want.


    VBA, try attached.
    with Sheet1
    When value of last row of col.A changes, the value appear in the last row of col.M.

    Cell N1 has bbeen used as working cell.

    hope it works

    Using Index+Match

    Table must in descending order
    i.e. B1=30, C1=20, D1=10


    returns larger value than the test value in the list

    Hi, Tanis

    When A1 changes, same value should be posted to the first available cell in col.M.
    Then A2 changes, same value to the next cell in col. M
    Is it possible A1 changes again?
    What would you like to do when it happens?

    Other solution

    Select the range
    Choose Edit - Copy
    Holding down Shift key and choose Edit - Paste Picture Link

    Then you can move the linked picture on the top row and freeze pane.



    First of all, at this end, it doesn't sort like you desire even I change the format from number to text. The only way I can get sorted that way is what I mentioned above and it doesn't matter if the values in column A are formatted as number or text.

    Anyway, if you want convert numbers in column A to text in column B is as follow

    1) Copy columnA and select columnB then pastespecial and check Values then OK
    2) Right click and select cell format then change from General to Text

    Alternatively, the given formula in cell B1
    changes value to Text from numbers
    If you put =ISTEXT(B1) returns TRUE means Text already.

    I hope you can get what you wish,


    Using working column:
    If the data is in column A
    fildown to the last row of col. A

    then sort by column b as first criteria and a for second.

    Hope this does good for you.