gump

well the title should explain it i'm a word processing excel dummie. im needing a spreadsheet that has several columns and need it to be able to sort. this i did but i did something while sorting that caused all the info to change columns and mis-sort everything. dummie me saved it and thought i could fix it when i got home but it was so screwed up i had to junk it all.

is there a way of locking the columns where everything stays together? this is kind of hard to explain without seeing the file so here's what im needing CLICK ME . i need to be able to sort by the item number and when it sorts it will bring the other info with each item. id appreciate any help im needing this badly! thanks

maestroxl

I know exactly what you're talking about.

First, here's what you did:
1. Identified the column you wanted to sort
2. Clicked the column header (the letter representing the column: "A", "E", etc., you'll see the entire column get highlighted)
3. Clicked the sort button

When you do this, Excel thinks you want to sort just that column's data while leaving the other surrounding data intact.

Now, here's what you wanted to do:
1. Identify the column by which you want to sort
2. Simply click the first cell in that column (only one cell will be selected, ie. have a border around it)
3. Click the sort button.

All data in your table will sort according to that column.

Another approach is to go to the "Data" menu and click "Sort...". Select which columns you want to sort and by what order.

Cheers.

__________________
be the change you want

MikeSty

Data > Sort > Sort By: Item#

Try clicking the square in the top left to select the entire data sheet.

gump

thanks for the replies i appreciate it. thats what i was doing but i must have screwed it up some how. you think a access database would work better and be less likely to have human error involved?

maestroxl

RE: Access; It depends on your goals and needs. If you're just trying to work with a simple table of data, Excel does what you need without confusing the issue. Some controls are just not intuitive. Don't get discouraged!

Access would only be recommended if you had so much data that it needed to be normalized (e.g., split into multiple tables to prevent duplication). It's much less intuitive and has a higher learning curve than Excel.

__________________
be the change you want

meanSpleen

Have you tried Auto-Filter yet? It really is a wonderful tool for finding information. Not really a great sorting function, but it has a really good amount of uses

Notes:
-Auto Filter will make absolutely NO CHANGEs as far as the placement of cells in their columns.
-You can turn it on at off at will with no effect on your data
-It makes finding certain values easy
-It makes finding certain groups of values easy (Choose to view only items with a cost of $1.00, even if they are different items)
-You can customize what you want to see with intermediate formulas and functions
-You can choose to view data all by using a drop down box
-You can turn off Auto Filter, sort everything like maestroxl and MikeSty recommended, and turn Auto Filter back on. The sort you just did will still appear exactly how you left it when you turn Auto Filter on.

Anyway, enough notes - download and open this file http://cowmoomoo.com/Upload/template.xls - It is on my site, and yes it is safe.

You'll see that I updated it with some really basic info. Naturally, you will be updating or creating a new one to suit your needs and your data.

On Row 1, where you have your column headers, do you see the dropdown arrow? These are the auto filters that were set up. Try clicking on the one in cell A1 and choose '1'. Do you see how it selected all items that were item 1? If you had more data, you may have a lot of items with the same (insert column whatever here: price, MFG, type, vendor, etc).

Lets get everything back to normal without going through the filter. Click on the same down arrow in the Item# cell, and choose 'All' (it might say something else, I use Open Office). Anyway, we just want to make sure we turned off the selection and everything is showing up again.

Filtering down. Lets say we need to find Item# 1, with a MFG# of C. If you have a ton of data, it may be a bit hard. With Auto Filtering it is fairly easy. Choose Item# 1 just like you did above. Next, choose the drop down in MFG#, and choose 'C'. You have just successfully filtered down to all items that are Item# 1 and MFG# C. Again, just an example. Chances are you won't have more than one Item# 1.

There are other options you can play around with, it will not hurt or change any of your data unless you change something in a cell and save it. If you do, then only the cells that you entered things into will be updated.

Turning On/Turning Off - In Open Office, and probably Excel too, there is Data option in the File - Edit - View - etc toolbar. Select one or more cells with data in your table. It really does not matter how many cells you choose, just one needs to be select. Choose Data, then select Filter, then Auto Filter. Thats pretty much the extent of turning Auto Filter on and off.

****
Edit:

Oh, and as far as making your Item# frozen so the numbers do not move around, you need to mess with Cell Protection, and Sheet Protection which is generally more of a headache than anything else. Protection is most useful when you are sending things out to people and you don't want them to move things around or edit cells.

__________________
Like TFP? Donate To Keep It Alive!!

gump

meansplean my friend that will work perfectly. it took me a second to understand everything but thats going to work. thanks again for everybody replies!