dynamic autosort?

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
I have a worksheet where the information is something like:

A1 B1

Name Playing Date

Mike 10/12/2006

Say 10 or 20 of those with the data sorted descending by date. What I want is if the date changes....I want the whole table to resort based on descending dates. So, say mike is the fourth entry at 10/12/2006 and his date changes to 10/14.2006, then the list would refresh and autosort based on that change.
Is this even possible? Please tell me it is without VBA or macros.
Thanks in advance for any help.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
Never mind, I figured out a work around.
Basically did a RANK column and then did an indirect to dynamically sort and then a VLOOKUP to fill in the info. I'm sure there's an easier way, but I'm a master with Duct tape.
:)
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
Actually, This did and did not work. it did work to create a new column of data, but this isn't what I want. What I want is to enter the date and the EXISTING columns of data sort upon the new date entered automatically.
Please help???
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
xcellnoob

If I have understood correctly, you will need to use VBA. This code may need some modification but I think it is headed in the right direction for you. Post back if you need help with with how to get the code working for you.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

    <SPAN style="color:#00007F">Dim</SPAN> DataArea <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> NoRows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> Target.Count <> 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Column > 2 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    NoRows = Range("B65536").End(xlUp).Row
    <SPAN style="color:#00007F">Set</SPAN> DataArea = Range("A2:B" & NoRows)
    <SPAN style="color:#00007F">With</SPAN> DataArea
        .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
I don't know how to get this to work. I opened up VBA, pasted into this workbook and entered in some names and dates and nothing happened. Do I have to Run it? I would like for it to happen automatically. Hope this helps with understanding my dillemna.
Thanks!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
I don't know how to get this to work. I opened up VBA, pasted into this workbook and entered in some names and dates and nothing happened. Do I have to Run it? I would like for it to happen automatically. Hope this helps with understanding my dillemna.
Thanks!
In the VBA window, double click on the particular Sheet in the left pane (not This Workbook) and paste the code there.
 

Forum statistics

Threads
1,136,369
Messages
5,675,360
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top