Auto sort help needed

%3CLINGO-SUB%20id%3D%22lingo-sub-2835750%22%20slang%3D%22en-US%22%3EAuto%20sort%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2835750%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All-%20I%20have%20a%20spreadsheet%20where%20I%20want%20it%20to%20sort%20by%20status%2C%20column%20J%2C%20but%20I%20would%20like%20it%20to%20auto%20sort%20so%20anytime%20I%20add%20a%20new%20candidate%20the%20sheet%20automatically%20updates%20the%20sort%20when%20I%20add%20their%20status.%20I%20was%20told%20the%20easiest%20way%20to%20do%20this%20was%20to%20make%20it%20a%20table%20and%20then%20add%20the%20sort%2C%20but%20I%20did%20that%20and%20it%20isn't%20auto%20sorting%20when%20I%20add%20a%20new%20row%20of%20information.%20I%20read%20online%20about%20ways%20to%20do%20this%20by%20formula%20or%20VBA%20but%20I%20haven't%20been%20able%20to%20figure%20out%20how%20to%20do%20that.%20I've%20never%20used%20VBA%20before%2C%20as%20an%20FYI%20so%20that%20would%20be%20new%20to%20me.%20There%20is%20also%20conditional%20formatting%20in%20the%20sheet%20tied%20to%20the%20status%20as%20well%2C%20it%20turns%20the%20row%20a%20specific%20color%20based%20on%20the%20status%2C%20so%20I%20would%20need%20to%20be%20able%20to%20keep%20that%20too.%20Basically%2C%20when%20I%20put%20in%20a%20new%20candidate%20in%20a%20row%2C%20when%20I%20add%20their%20status%20I%20want%20the%20row%20to%20turn%20the%20pre-determined%20color%20and%20I%20would%20like%20it%20to%20auto%20sort%20by%20my%20custom%20list%20so%20I%20know%20where%20someone%20is%20in%20the%20proces%20(and%20anytime%20I%20update%20someone's%20status%20I%20want%20it%20to%20resort).%20Is%20this%20possible%3F%20Any%20help%20would%20be%20greatly%20appreciated!!%20Thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2835750%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836599%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20sort%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1182502%22%20target%3D%22_blank%22%3E%40Lizzasso%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20cannot%20auto%20sort%20your%20source%20table%2C%20however%20you%20can%20create%20a%20formula%20in%20another%20sheet%20that%20references%20your%20source%20table%20and%20using%20the%20SORT()%20or%20SORTBY()%20functions%20will%20auto%20sort%20the%20spilled%20output%20of%20your%20dynamic%20subset.%26nbsp%3B%20Another%20solution%20you%20can%20try%20is%20power%20query%20where%20you%20can%20set%20the%20sort%20order%20and%20the%20resultant%20query%20will%20always%20be%20sorted%20the%20way%20you%20set%20your%20query%20up%3A%3C%2FP%3E%3CP%3EReferences%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FkOTyjQ1jFZA%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FkOTyjQ1jFZA%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1634020273390.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316673iFB04D929D17C2C92%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1634020273390.jpeg%22%20alt%3D%22Yea_So_0-1634020273390.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Flessons%2Fsort-and-sortby-with-multiple-columns%3Fwvideo%3Dmdnm19g7sj%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EExcel%20tutorial%3A%20SORT%20and%20SORTBY%20with%20multiple%20columns%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2F9W2mlLgfwwA%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2F9W2mlLgfwwA%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836614%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20sort%20help%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1182502%22%20target%3D%22_blank%22%3E%40Lizzasso%3C%2FA%3E%26nbsp%3BI%20noticed%20that%20your%20table%20in%20Sheet1%20(it%20was%20called%20Table6)%20was%20set-up%20to%20cover%20all%20rows%20for%20columns%20A%20to%20M%20and%20that%20you%20had%20the%20filter%20buttons%20hidden.%20Let%20the%20table%20only%20contain%20%22used%20rows%22%2C%20and%20unhide%20the%20filter%20buttons.%20Now%20after%20you%20made%20a%20few%20new%20entries%2C%20press%20the%20filter%20button%20and%20choose%20the%20sorting%20option%20you%20want.%20Why%20%22pollute%22%20your%20schedule%20with%20VBA%20code%20that%20basically%20does%20what%20a%20simple%20click%20on%20the%20filter%20button%20can%20achieve.%20Especially%20when%20you%20have%20never%20used%20VBA.%3C%2FP%3E%3CP%3EMade%20some%20changes%20to%20your%20file.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi All- I have a spreadsheet where I want it to sort by status, column J, but I would like it to auto sort so anytime I add a new candidate the sheet automatically updates the sort when I add their status. I was told the easiest way to do this was to make it a table and then add the sort, but I did that and it isn't auto sorting when I add a new row of information. I read online about ways to do this by formula or VBA but I haven't been able to figure out how to do that. I've never used VBA before, as an FYI so that would be new to me. There is also conditional formatting in the sheet tied to the status as well, it turns the row a specific color based on the status, so I would need to be able to keep that too. Basically, when I put in a new candidate in a row, when I add their status I want the row to turn the pre-determined color and I would like it to auto sort by my custom list so I know where someone is in the proces (and anytime I update someone's status I want it to resort). Is this possible? Any help would be greatly appreciated!! Thank you so much!

2 Replies

@Lizzasso 

 

You cannot auto sort your source table, however you can create a formula in another sheet that references your source table and using the SORT() or SORTBY() functions will auto sort the spilled output of your dynamic subset.  Another solution you can try is power query where you can set the sort order and the resultant query will always be sorted the way you set your query up:

References:

https://youtu.be/kOTyjQ1jFZA

Yea_So_0-1634020273390.jpeg

 

Excel tutorial: SORT and SORTBY with multiple columns

 

https://youtu.be/9W2mlLgfwwA

 

@Lizzasso I noticed that your table in Sheet1 (it was called Table6) was set-up to cover all rows for columns A to M and that you had the filter buttons hidden. Let the table only contain "used rows", and unhide the filter buttons. Now after you made a few new entries, press the filter button and choose the sorting option you want. Why "pollute" your schedule with VBA code that basically does what a simple click on the filter button can achieve. Especially when you have never used VBA.

Made some changes to your file. See attached.