Sort Dynamic Table with Condition

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have the below table, what I need is to sort the numbering by ascending order, it's easy if I have a fixed table, but the challenge is this table is dynamic, it changes whenever I change the EE ID. Can you help me with the formula that solve this issue?

EE IDAAAA
KEY_EEIDGoal#Goal Overall ProgressObjectiveCategoryMetric/OutcomesActive/DeletedNumbering
AAAA11NOBBBBBBOEnnnnnActive3.2
AAAA22NOCCCCCOEjjjjjActive3.1
AAAA33YESKKKKBEeeeActive4.2
AAAA44YESLLLLOErrrActive1.1

<tbody>
</tbody>



Thank you
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

It seems to me the dynamic part is in fact when you are changing the Key_EEID ....

If it is the case, an event macro could run your Sort macro as soon as you are changing anything in Column A ...

Hope this will help
 

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows
Hi,

Thank you for your respond! Yes, you're right. Can you please share the suggested macro?
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi again,

In your sheet module, you could test following:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
If Target.Target > 1 Then Exit Sub
Dim last As Long
last = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A1:H" & last).Sort [A1], xlAscending, Header:=xlYes
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Hope this will help
 

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I copied the macro to my sheet and when I tried to run it, it didn't show this macro in my drop-down list of macros, I even copied it to a new workbook and the same happened. Is there anything I can do to fix? I have named the range of each column if you want I can share them with you?
 

zinah

Board Regular
Joined
Nov 28, 2018
Messages
179
Office Version
  1. 365
Platform
  1. Windows
Hi,

That's super great! thank you so much for your help and guidance.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,237
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top