Sorting with a Named Range List

[Spritz]

Hi there. I have 4 columns: Last Name, First Name, ID, Location

The last name column is a named range list that is dynamically updated everytime I add a new person to it. This makes it easy for me to search through the list for a name and everything else auto populates from picking that last name. My problem is that i have the other 3 columns auto filtering so if someone wants to sort by location they can. The problem is if i sort Ascending or Descending only the last 3 columns sort and the Last Name column doesn't because it is a separate list. Is there anyway to go about making the Last Name column sort with the rest?

I am the only one using this at the moment so I know not to make a mistake but others will begin to start using it down the road and I am worried if someone accidentally sorts using ascending or descending and saves it will completely ruin the file.

Any help is appreciated!

[p24leclerc]

If your 4 columns are contiguous, sorting should sort all 4 columns at once no matter the first column has a named range.

[Spritz]

Hi Pierre,

I believe it is because the named range is also a list and lists have their own sort function. In any case, only the other 3 sort and not the named range.

If i try to highlight all 4 columns and enable autofilter, the autofilter function is grayed out but it will allow me to highlight the 3 no named/list ranges and auto filter them then i have to autofilter the list separately.

[p24leclerc]

can you attach your workbook with dummy data so I can see what's going on?

[Spritz]

Attached. Sorry i don't have too many comments in my code right now but most of it should be easy to understand.

Try sorting the location in ascending or descending order and you will notice the Last Name column doesn't sort with it.

[p24leclerc]

Why do you use a Table? If it is only to have range named that grows with your data, try this formula in the Refers To of your LastName range named. It will grow with your data and you can get rid of the Table that prevent proper sorting.

Please Login or Register  to view this content.

[Spritz]

Why do you use a Table? If it is only to have range named that grows with your data, try this formula in the Refers To of your LastName range named. It will grow with your data and you can get rid of the Table that prevent proper sorting.

Please Login or Register  to view this content.

Haha i use too much VBA to realize i could have just done this :P.

Thanks!