Announcement

Collapse
No announcement yet.

Sort By Lookup & Ranking

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sort By Lookup & Ranking

    Hello there,

    I have a beginner's question to ask.
    I am trying to sort a range using VBA. However each cell in the range is fed by formulas.

    I have attached a spreadsheet that shows how far I am now.

    Essentially, I have a range of data that a user copies into the spreadsheet every business quarter (A31:Y59). From that range, a new range is constructed (A1:E20) for producing a graph.
    The new range (A1:E20) is currently ordered in the way that the data from the old range is ordered.

    However, I need the new range to be sorted automatically in descending order using the "Size" column as the Key.
    By "automatic", I mean that it has to sort itself when the old range is updated without needing anyone to use the Sort button or the like.
    I found it tricky because of the formulas in each cell of the new range. The only solution i could think of was to use Paste Special:Values in VBA (using a button, unfortunately...) and then Sort the newly created range.

    I would prefer avoiding creating a button like i did.
    The button in my spreadsheet copies correctly but the Sort does not work. I followed MSDN examples and searched a little but can't figure.

    Thus,
    1) how to fix the button code?
    2) how to get rid of the button and do it all automatically?

    Any help is appreciated, thanks.
    Attached Files

  • #2
    Re: Sort Values That Have Formulas

    Hi,

    See the attachment.

    HTH
    Attached Files
    Kris

    ExcelFox

    Comment


    • #3
      Re: Sort Values That Have Formulas

      That's great! Thank you.

      However I would like to recreate what you have done on multiple sheets, so if you could please explain how you created and applied your RANK, VLOOKUP and other formulas, that would be super.

      Thank you very much.
      Last edited by Dave Hawley; December 21st, 2006, 11:01.

      Comment


      • #4
        Re: Sort Values That Have Formulas

        OK. Insert 6 columns before Col a. Select G1:K20 (earlier a1:e20), cut, then copy into A35.

        In F37 and copied down,

        =RANK(B37,$B$37:$B$54)+COUNTIF($B$37:B37,B37)-1

        In A3 and copied down,

        =INDEX($A$37:$A$54,MATCH(ROWS($A$3:$A3),$F$37:$F$54,0))

        In B3 and copied down & across,

        =VLOOKUP($A3,$A$37:$E$54,COLUMNS($B$1:B$1)+1,0)

        HTH
        Kris

        ExcelFox

        Comment


        • #5
          Re: Sort Values That Have Formulas

          Great. Thanks very much.

          Comment

          Working...
          X