Sort used range to left of named cell

Need to sort a range of data (ascending) by using a key cell that is three rows below a named cell ("sort_start").
The used range is going to be to the left of the "sort_start" cell and needs to include columns all the way over to Column A since there may be an empty column within the target area. The key cell may be in a different location depending on which sheet template I might have to use; that's why I named a cell relative to the key. Make sense?
ex. Key might be in AC14; all used rows below need to be sorted in ascending order.

Any other information needed for help on this?

Thank you,
Delain

Please post your workbook highlighting all the items mentioned in your post.
Click on "go advanced" next t0 "post quick reply" and click on paperclip icon.
thanks

Kevin, thanks for offering to help.

I'm trying to attach the sheet, but the posting interface is not working. When I click on the paperclip, nothing happens; the options are empty.
Tried it in Firefox and Chrome; not working.

Delain

Try looking below the advanced reply area and click on Manage Attachments - it should work for you

You'd think I'd never posted to the forum before; geesh!!
Perhaps not to this one, but others certainly. Shouldn't the "managed" attachment show up in the paperclip list(?) ?

Attached is the page I'm needing this to work on. The only highlighting I did was to point out the location of the named cell with a mention of the rows I'm needing to sort.

Of course, the quantity of rows will vary from xls to xls.

Let me know if it isn't what you need.

Thank you,
Delain

Any suggestions on this?

Thank you,
Delain

Kevin, thanks for the great reply and layout of your explanation.

To clarify and re-emphasize from my original post and the remark in the text box on my XLS:

Here is "start_sort"
I want to sort the rows in the section three rows down (items 1 thru 12), using the cell with the $13.47 as the key or starting point for a descending sort.

From my original post:

that's why I named a cell relative to the key.

So, since the key cell (that's what I'm calling it) may not be the same cell, from time to time, I thought using a static named cell (sort_start) would be a good idea.
In the sample XLS, the sort key would therefore by AF14 - three rows down from the named cell. One XLS may have more columns in between Column B to the key. However, I have named each sort_start in the various workbook templates I use.
So, sort_start is always a pre-established location.

My primary need is to be able to sort the rows while capturing all of the columns from the key cell all the way to the left and across any blank cells that might be in a column within the sort range. That's why I intentionally included a blank Column D.
Also, there will always be a different quantity of rows in the workbooks I will use. The sample I had sent is one of my template worksheets I use to capture date exported out of a database search program that dumps the values/data into the XLT. Sometimes the dump will be ten rows; other may be thirty.

Please Login or Register  to view this content.

If I'm interpreting your code correctly, AF11 is not going to be an appropriate starting point. It may be one or two columns over. But, naming it would solve that; hence, "sort_start".

I will experiment with your code a bit; have to break off of this for now.
Thanks again for your time.

Delain

Any suggestions on this?

Thank you,
Delain

Bump this again

Ascending ? Descending ?
Change 1 to 2 for Descending order.

Please Login or Register  to view this content.

No error....

Please Login or Register  to view this content.

Currently column A last entry determines how many rows there are in the sort table - is it correct to assume that there will always be an entry in column A regardless of which other columns are in use?

Yes, the way my templates are setup, there is always an entry in each row in column A of the area that I'm wanting to sort.

Currently the sort table is includes all columns B to AF to make sure that whatever the width of your data it will always be accommodated by this range - is this what is required?
- could the table go wider than AF?

The table could go wider than AF and that's why I thought to use a Named Cell as a landmark for any template that has more columns than the example I provided.

The callout shape within the worksheet asks for "descending" sort, but post#1 says "ascending" - which one is it?

Sort needs to be Ascending (smaller to larger); sorry for the mixed message.

Currently the sort column is determined by whatever cell reference is typed into cell AF11 , but this is not quite what you want. It may be cell AF12 or AE13 (or whatever) depending on which data table is pulled into "Main Page"
- what determines which cell this will be?
- is it always the cell above the last column of data?
- and what about the value in that cell? (is it pre-determined in the table being pulled into "Main Page"?)

Not sure what happened here. In my original attachment, in post#5, there was no cell reference displayed or typed in AF11.
I believe that was something that was injected within your solution proposal in post#7. There will be a dollar value displayed
which comes from referencing another cell; in this case I11. But, AF11 is not a key to the sort needed; it is simply the
cell I chose to establish the landmark "sort_start". Does that help with the subsequent questions also?

I'm really sorry this has gotten so complicated; I did my best to explain in narrative and with the attachment in #5 with callouts.

@jindon
Running your code in one of my other existing worksheets seems to work beautifully and does use the sort_start as a landmark as needed.
I will continue to experiment with it and ensure it is catching all the columns needed to be included in the sort area within
various worksheet layouts that I use.

For the moment, unless I'm missing something, I think this is the solution.

I thank you both for your time and will post back if I have a problem.

Thank you,
Delain

Delain,

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

I will tag that.

Can you take a moment and respond to Kevin#'s previous question: "It is very elegant code, very concise - can you explain it?"

Delain

I have already done it.

See my post #16.

Overlooked; thanks.