AUTO SORT

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
123
Office Version
  1. 2013
Platform
  1. Windows
In Cells T4:T500, I make the user fill the donors name, I already have a data validation for these cells to prevent duplicate entry

Now in Cells A4:A5000, I use the data validation for list range T4:t500

Can anyone please suggest a formula for T4:T500 such that when ever the user fills the donors name in this range, it gets auto sorted when pressed enter after entry, so that it will be easy for him to select the same from the list when filling Cells A4:A5000
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,345
Office Version
  1. 2016
Platform
  1. Windows
I think you need VBA :
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then
    Exit Sub
ElseIf Not Intersect(Target, [T4:T500]) Is Nothing Then
    [T4:T500].Sort Key1:=[T4], Order1:=xlAscending, Header:=xlNo
End If
End Sub
 

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
123
Office Version
  1. 2013
Platform
  1. Windows
I think you need VBA :
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then
    Exit Sub
ElseIf Not Intersect(Target, [T4:T500]) Is Nothing Then
    [T4:T500].Sort Key1:=[T4], Order1:=xlAscending, Header:=xlNo
End If
End Sub
there are 3 sheets in the file, I pasted the above VB as a module on the worksheet ( not under individual sheets ) and saved the file as macro enabled. It is not working, The cells T4:T500 are in the sheet named "Receipts", DO I need to make any changes in the above or insert the VB module in the said sheet, Tried with the following also , still not working

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Then
Exit Sub
ElseIf Not Intersect(Target, ['RECEIPTS'!T4:T500]) Is Nothing Then
['RECEIPTS'!T4:T500].Sort Key1:=[T4], Order1:=xlAscending, Header:=xlNo
End If
End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,345
Office Version
  1. 2016
Platform
  1. Windows
Put it in the Receipts sheet module. Don't make any changes to the code.
 

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
123
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Put it in the Receipts sheet module. Don't make any changes to the code.
still not working, DO I have to put some command or it will autosort after in enter the next entry in the list?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,345
Office Version
  1. 2016
Platform
  1. Windows
It will auto-sort after you make an entry to a single cell in T4:T500.
 

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
123
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

It will auto-sort after you make an entry to a single cell in T4:T500.
NOT WORKING

AN
BC
BA
AD
 

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
123
Office Version
  1. 2013
Platform
  1. Windows
Sorry to bother you repeatedly, but can you tell me what I am doing wrong?
I pressed ALT+F11, then Insert module after selecting the kali mandir receipt sheet, pasted the code in the module and saved, then saved file as macro enabled file
 

Attachments

  • Screenshot_1.jpg
    Screenshot_1.jpg
    118.6 KB · Views: 5
  • Screenshot_2.jpg
    Screenshot_2.jpg
    12.2 KB · Views: 6

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,345
Office Version
  1. 2016
Platform
  1. Windows
Check :
- the code is in correct sheet module (i.e. the sheet containing the T4:T500 list)
- your macro settings allow macros to run
- if still no good, try closing Excel and re-opening (in case event procedures have been disabled)
- try putting a breakpoint against the first line of the macro to see if is being triggered
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,480
Messages
5,770,337
Members
425,612
Latest member
martinijr

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