Easy way to sort without highlighting everything

[andrewb90]

Hello,

Is there a function or a button of some sort that would allow me to sort a portion of a sheet based on alphabetical order or the contents of a particular column? I have been able to do this by hand, but I am making this sheet for somebody else, and would like there to be a more conveniant way to sort the list of products based on Name, Frequency of Purchase, or Profit.

Thanks
Andrew

 

[Norie]

Andrew

Isn't there a sort button in Excel?

Or are you looking for something else?

 

[andrewb90]

There is a button and there are two reasons why I don't want to use it.
1. I have to custom sort the field to show what I want.
2. When I sort all of my formulas become inaccurate.

I would like a few buttons or something that I could make and have auto sort certain rows based on the contents of a particular column. Just like the actual function, but I don't have to enter in my specifications on the sort every time.

Thanks,

Andrew

 

[Norie]

Andrew

Could you tell us what 'custom' sort you want to perform?

Can you already do this manually?

If you can I would suggest the best place to start would be to turn on the macro recorder and do so.

That should generate code, might not be exactly what you need, but it'll definitely be a start.

 

[andrewb90]

Norie,

I don't think the Macro recorder would work for me. Because when I sort items my function no longer remain accurate. So I can not actually do my sorting manually and make all of my functions still work.
Basically on each sheet I have it split into two sections. On the top I have a list of Orders with date of order, expiration date, and quantities, and a few other things. The bottom portion is just a list of every product that we carry (that is relevant to the sheet. i.e, alcohol,sodas, ice cream, etc.). That portion contains sales data, and pricing info. I have various functions in most of the cells that are based off of info put into cells in both the top and bottom sections.
I would like to be able to have some button or function to sort the items by, expiration or purchase date, or quantity on hand, and back to the original format which is alphabetical. Then the bottom section would get sorted by, biggest profit, and biggest discounts, as well as back to alphabetical.
The problem I face, is when I sort items in one section of the worksheet, the data that changes based on numbers in the lower portion are now different because the cell is in a different spot.
For example, I would highlight rows 3-18 in my ice cream section. I would select custom sort, and sort by column D (expiration dates) I would choose values and newest to oldest, then sort. Now all the ordered ice creams are in order by expiration date, but now the amount of ice cream I have is wrong because the cell functionss have changed.
I hope this makes sense.

Andrew