Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Sort By Lookup & Ranking

  1. #1
    Join Date
    25th October 2006
    Location
    Montreal, Canada
    Posts
    41

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Sort Values That Have Formulas

    Hi,

    See the attachment.

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  3. #3
    Join Date
    25th October 2006
    Location
    Montreal, Canada
    Posts
    41

    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 at 11:01.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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

  5. #5
    Join Date
    25th October 2006
    Location
    Montreal, Canada
    Posts
    41

    Re: Sort Values That Have Formulas

    Great. Thanks very much.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Conditional Format & Sort Macro For Ranking
    By LAE0720 in forum Excel General
    Replies: 2
    Last Post: November 21st, 2007, 03:02
  2. Sort Data With Reference List And Lookup
    By Baloo in forum Excel General
    Replies: 1
    Last Post: September 27th, 2006, 03:24
  3. Filter, sort, lookup from other sheet
    By falloutx in forum Excel General
    Replies: 1
    Last Post: January 19th, 2006, 22:14
  4. Replies: 5
    Last Post: July 23rd, 2003, 18:29

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno