Forum Discussion
Excel Custom Sort Beyond Default Range
- Thanks, Detlef, that's the example I was looking for! When I click on the "(More columns...)" and put SP in the box, then click OK, I get an error message. However, when I type in SP:SP it works perfectly. That's not intuitively obvious from the message, and I was unable to find that answer in any of the Help sections. Thanks again!
Thanks for the reply and appreciate the comments.
We provide a marketing research service called PBRMarketTools. One of those services is ThePBR1000, which is tracking 1,000 of the largest publicly held corporations in the US. The spreadsheet has basic info like name & address info. In addition we provide 5 years of info on revenues, profits, earning per share, share outstanding, dividends, assets, equity, 5 years of many related ratios, number of employees, 10 years of monthend stock prices, several analytics on those stocks, 5 years of price earnings ratios, several years of monthend market capitalization values, number of equivalent shares, key information on the board, management, and the CEO, CEO pay ratios info, and much more. The spreadsheet starts in Column A, currently ends in Column XT.
The spreadsheet is also divided into 3 sections, all with the same format. The first third are the 1,000 rows of current The PBR1000 companies, there is a section of around 500 Prospect companies, and another section made of of around 500 companies that are no longer on The PBR1000 through acquisition, going private, going bankrupt, etc. In all there are about 2,500 rows.
Column SP is the date we last updated the CEO info. Periodically we sort from oldest to newest and update the info. We only do that for The PBR1000.
When we select the rows 8-1007(The PBR1000 companies), go to the Sort & Filter, Click on the Custom Sort, it only shows through Column SF, then has an option for more columns, but displays a format that is a little cryptic.
Hopefully that explains the structure of the spreadsheet for you and why it has so many columns.
Thanks again, hope this helps you understand the situation.
It seems you reached one of many restrictions in Excel.
It is very clear what the message says:
Click on the button on the right, select the column, click on the button on the right again, click OK.
Alternatively you can type the column reference into the box: TS:TS
- Thanks, Detlef, that's the example I was looking for! When I click on the "(More columns...)" and put SP in the box, then click OK, I get an error message. However, when I type in SP:SP it works perfectly. That's not intuitively obvious from the message, and I was unable to find that answer in any of the Help sections. Thanks again!