how to rank with an updating list

[labogola]

list.xlsxlist.xlsx


If I keep adding items to column A and B what's the best way for me to make the ranks move with it? should I use a if function? and just set it up for the entire column?

[martindwilson]

=IF(B2="","",RANK(B2,$B$2:$B$50,0)) range as long as you are likely to need

[labogola]

Once I copy a lot of these if functions my excel file balloons into a monster over 15mb and becomes unusable. The problem is I have long if functions and also will need to copy down many of them to allow the user to enter lots of data. Is there some kind of option in VBA to update the spreadsheet for this?

[Fotis1991]

Add 2 buttons(1 for eatch macro) and use the function when you really need it.

Sub macro1()
Range("E2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",RANK(RC[-3],R2C2:R10000C2,0))"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E10000"), Type:=xlFillDefault
    Range("E2:E10000").Select
End Sub
Sub clear()

    Range("E2:E10000").Select
    Selection.ClearContents
End Sub