Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



How To Lock Cells From Excel Sort Function

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hey,

I have a table in which the first row of cells are headings for each column and can sort data from all the columns. On the second row are the totals of all the columns with formulas.

Every time i sort the table the totals row gets sorted with all the other data messing up the table.

Is there anyway to lock or protect a row from the sort function in excel?

Thank you for your time

View Answers     

Similar Excel Tutorials

Case Sensitive Sorting in Excel
How to perform case sensitive sorting in Excel. Select a cell within the data that you want to sort and then go to ...
Sort Data Alphabetically or Numerically in Excel 2007 and Later
This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...
Freeze or Lock Specific Rows and Columns when Scrolling in Excel
Prevent specific rows or columns from moving when you scroll through a spreadsheet in Excel. This allows you to kee ...
Sort Data Left to Right in Excel
How to sort columns of data in Excel.  This is the same as sorting left to right. This will change the position of ...

Helpful Excel Macros

Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Sort Data With Headers in Descending Order in Excel
- This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1 - o
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a

Similar Topics








I have a file with 500 row, 7+ columns of data. At some point last year, I
must have sorted it without highlighting the last column. Consequently, it
is all discombobulated.

In future, I will sort it by highting the rows rather than all the cells.

However, I am wondering if there is some way to 'lock in' a sort so that
every time I sort it, all the columns are automatically included (ie - is
there a way to remove stupid human error)?


--
Joe



I have an excel worksheet (2007) that is in a table format. Several of the columns contain calculations which I have hidden and would like to lock so that users cannot change the formulas. The users need to be able to edit the visible cells, and sort the table.

When I protect the hidden columns, the table will not allow sorting. When I click sort in the table filter, an error message pops up, "The cell or chart that you are trying to change is protected and therefore read-only..." (I do enable sorting of protected content). What am I doing wrong?


My steps are as follows: Allow Users to Edit Ranges (I select the range that I would like to allow changes to) Protect Sheet Check: Protect worksheet and contents of locked cells, Select Locked cells, select unlocked cells, SORT, Use AutoFilter, Use PivotTable reports I enter a password and hit ok If users could hide and unhide the non-protected cells, that would aslo be great!

Thanks!


I have an excel worksheet (2007) that is in a table format. Several of the columns contain calculations which I have hidden and would like to lock so that users cannot change the formulas. The users need to be able to edit the visible cells, sort the table, and hide and unhide the unlocked columns.

I have figured out how to do most all of this, but the table will not allow sorting. When I click sort in the table filter, an error message pops up, "The cell or chart that you are trying to change is protected and therefore read-only..." (I do enable sorting of protected content). What am I doing wrong?


My steps are as follows: Allow Users to Edit Ranges (I select the range that I would like to allow changes to) Protect Sheet Check: Protect worksheet and contents of locked cells, Select Locked cells, select unlocked cells, SORT, Use AutoFilter, Use PivotTable reports I enter a password and hit ok If users could hide and unhide the non-protected cells, that would also be great!

Thanks!


I have an excel worksheet (2007) that is in a table format. Several of the columns contain calculations which I have hidden and would like to lock so that users cannot change the formulas. The users need to be able to edit the visible cells, sort the table, and hide and unhide the unlocked columns.

I have figured out how to do most all of this, but the table will not allow sorting. When I click sort in the table filter, an error message pops up, "The cell or chart that you are trying to change is protected and therefore read-only..." (I do enable sorting of protected content). What am I doing wrong?


My steps are as follows: Allow Users to Edit Ranges (I select the range that I would like to allow changes to) Protect Sheet Check: Protect worksheet and contents of locked cells, Select Locked cells, select unlocked cells, SORT, Use AutoFilter, Use PivotTable reports I enter a password and hit ok If users could hide and unhide the non-protected cells, that would aslo be great!

Thanks!


I have a table setup where the first 2 columns in my table need to stay in place, but I need to select the rest of the columns from, say row 2-100. So basically I am selecting C2:DD100 and I need to sort that by the data in row 100. I know this is possible by dragging and selecting from C2 to DD100, but I was wondering if I could lock the first to 2 columns and just have to select the ENTIRE rows 2-100? This would save a lot of time.

I know this can be done with macros, but I am trying to stay away from that, as I would also like to be able to undo this (and with macros it is harder to undo than a regular sort)


Sorry, I have to post this again, I could not find away of deleteing the old post. So apologise for the post.

I have looked at this problem and what details i tried to explain and realised that it made not a lot of sense.

I have attached a example spreadsheet with a unsort sheet and a sorted sheet.

I will now try and explain what im looking for.

If you look at the example, the yellow columns represent lock columns. Take into consideration that the spreadsheet is also protected, but with the sort function enabled. So i can olny sort the date, index, start and end columns and also add info and comments, but they need to be sorted at the same time. I can sort the first 4 columns, but the other two need to be sorted aswell. Is there a way of connecting the last to columns so that when the first four are sorted they follow suite.

How this and my example explains bettre what im trying todo

G


I am trying to create titles for columns. I can do that by just creating them in the cells above the data.
The problem is that the titles sort into the data using the sorting feature. When I lock any cells and protect them, the sorting is locked out.
Can I use some sort of header over the columns that remains in place where I can still sort the data below, by clicking on the column and sorting?

John


I have an excel worksheet (2007) that is in a table format. Several of the columns contain calculations which I have hidden and would like to lock so that users cannot change the formulas. The users need to be able to edit the visible cells, sort the table, and hide and unhide the unlocked columns.

I have figured out how to do most all of this, but the table will not allow sorting. When I click sort in the table filter, an error message pops up, "The cell or chart that you are trying to change is protected and therefore read-only..." (I do enable sorting of protected content). What am I doing wrong?


My steps are as follows: Allow Users to Edit Ranges (I select the range that I would like to allow changes to) Protect Sheet Check: Protect worksheet and contents of locked cells, Select Locked cells, select unlocked cells, SORT, Use AutoFilter, Use PivotTable reports I enter a password and hit ok If users could hide and unhide the non-protected cells, that would also be great!

Thanks!


Hello,

I have a spreadsheet that I plan to use the AutoFilter sorts on. I want to have two totals at the top, one which totals everything in each one of the columns, regardless of sort status and another which only totals the values you see when the sort is being used. I have figured out how to use the Subtotal(9,...) to sum everything I have, but I am wondering if it is possible to do something similar with the CountA function so it counts the number of rows present after using the sort. Any help is greatly appreciated!

Thanks,
Bonni


Hi, I love to use Excel tables to organize data because of the sorting capabilities. However, the table I am currently working with has the data field headings on different rows instead of columns. You can still sort all the data by clicking Data->Sort, etc. but is it possible to make a table in this vertical orientation so that I can easily sort by a row by clicking on the cell in the first column?

I hope my issue is somewhat clear. Please let me know if I need to clarify.

Thanks in advance!


Im in a fix.

I have to do a csv export of our database table which equates to 9000 rows by abou 40 columns.
Some of these columns host full html data.

The problem is that while managing this data while integrating a large amount of rows into the csv we sometimes have to sort by column. We have done this both by using the A/Z sort button short cut, and by clicking the upper left box where all cells get highlighted, clicking Data, then Sort, and choosing appropriate columns to sort by. Sometimes we hide columns in order to manage 40 columns of data. However from time to time in a manner we can not yet duplicate or determine (though we have a suspision that it is hidden data) we find that data that was was not sorted even though we choose to have all fields sorted becomes disassociated with it's respective row and the data ends up being corrupted. Though this is not to say that we are sure that hidding data is even relevent.

Has anyone seen this behavior?
Is there a fix, or switch, or different tool we should be using to do mass sorting, and resorting without fear of loosing data integrity?


My table contains roughly 20 sortable columns. I'd like the users to be able to custom sort, but many of them are so novice that I can't rely on them to be able to find Excel's 'Data Sort' options from the menu.

Is there a way to have VB display the Data, Sort dialog box but not automatically sort the data?

Since many of the probable sorts will involve multipls keys, a simple VB 'Sort table by this column' macro wouldn't work.

Thanks!


They just updated me at work to Office 2007. I'm having an issue with the sort function of excel 2007. This didn't happen in my old 2003 version.

I import a set of data with a header row. I add nother row above the header row to add more data (file date, etc.). I then freeze the panes at A3.

The first time I hit the sort button, all the cells below the header row (A2) are selected and I can sort the data normally. In my case I sort a column, add 0.00s to empty cells in that column to prepare to create a pivot table, then I want to sort the data by another column.

Here's where the problem starts. Again with the cursor at C3, the next time I hit the sort button, all the data cells get selected including the header row and the top row. I hit the "My data has headers" check box and it only removes the top row 1 selection but my header row is row 2, still selected. If I save the file and close it, then reopen. The sort function works fine *once*. The next sort request again chooses too many rows.

This used to work fine in the 2003 version but this one is driving me crazy. I know I can manually select the cells for sorting but I have to do this to numerous columns.

I've looked all over the net for a solution with out finding anyone else having this problem. I cannot go back to Excel 2003 because I have some sheets that are too large for 2003 to handle.

Any help will be greatly appreciated.


I am at a bit of a loss. I would like to protect a sheet in my workbook, lock all the cells in column A, unlock all the cells in Column B, and allow the user to sort the table. I tried tools-->protect sheet and then "Allow All Users of this worksheet to:" --> "Sort" but I'm having problems getting the sort to work presumably because the cells in column A are locked (which is what I want since those cells contain formula).

Any suggestions? Thanks in advance for your help.

Ted


Hello -

I have a challenge for some MVPs or Excel gurus out there (who knows -- maybe this is easy...).

I have an example sheet (which you can download below). The problem involves sorting table data on tab 2.

http://dl.dropbox.com/u/2625864/Foru...ampleData.xlsx

On tab 1 (Data), there are two columns (Name and Completed?). There is also a table where sorting works fine. When I sort any of the columns, the data moves with the appropriate rows as expected.

On tab 2 (Data2), the same table from tab 1 is there, in addition to another sammple table.

When I sort items in either of the two tables, the rows within each of the tables DO NOT move appropriately. For example, if I sort the names, the names sort fine but the data in the other columns does not move. If I sort the columns with the numbers, the numbers don't even sort correctly (an ascending sort sorts the data in a very random order).

I'm decent at Excel (or so I thought) but this has got me baffled.

Any help would be great!!!


Hi, I have a League table, showing the data from Games played, ie No of games played, points scored, goals for & against, games won, lost drawn etc, etc. The Table is populated by information from other cells which up-dates automatically.

Having entered a second set of results, when I try to sort the table using the Sort Function it fails to sort it in a logical fashion. The data on the row stays on the same row, but not under the correct column???

Grateful for any solutions

Cheers

tallchap




Concatenate Table Sort Error:

I am concatenating several values for use as a price lookup to a bloomberg code. The values are all in a excel 2007 table. When I sort the concatenate is not longer considering the values in its original recrord, thus looking up the incorrect price. The table includes totals and the totals change as you sort. Sorting by Table Column "ID" (record ID) realigns the concatenation and provides the original Total. Other individuals use this spreadsheet and I need it to be able to sort. Attached is the problem recreated in a watered down version of the spread sheet. Please let me know if you need more information.

Thanks,


Within Tables when you're concatenating values, you must use the reference syntax of the table, else you will encounter this problem. Replacing the cell referrences with TradeLog_Active[[#This Row],[BBG Code]], etc solved the issue.


I have more pivot table woes... I'm automating a long process which involves multiple pivot tables. All I need to do is sort by the last column which is a dollar total. The Data>Sort option does not seem to be working at all (I can select all of the rows in the pivot table and then use this, but this hasn't worked well for automation). When I click on the column header for the last column (the one I want to sort with), then select Data>Sort I get a message "cannot determine which pivot table field to sort" but I can do this on the other column headers.

Excel "help" talks about different ways to sort, but I can't get any of them to work with the pivot table.

Ugh.

Any suggestions?!?

Thank you!

Ed


I am a newbie for excel, so please excuse if this is a easy question.

I am trying to lock the entire row so that when I sort by a column all data to left and right of the column sorted stays in the same rows as originally was with sorted data.

Example

a 9 8 7 6
c 0 5 5 1
b 8 3 0 5

If sorted on column A new table would look like this

a 9 8 7 6
b 8 3 0 5
c 0 5 5 1



Thanks in advance for any help.


I have a macro that allows me to reorganise a table and format it into a specific way. However, I am trying to get it to sort the table out afterwards, but I cannot work out how to do this. The table does not appear on the same rows each time. I am able to select the areas that I need to sort, but cannot figure out the code to sort my current selection only

basically I am trying to write some code that will sort my table of 6 columns according to which button i press (so we can easily manipulate the table).

I have this code to sort it

Columns("a:f").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

the problem is, I expected this to sort from A3 downwards but what it does is sort from A3 downwards and move the whole lot up so it starts in A2. A2 is a blank row and I want to keep the blank row between the headings and the data. However this sort loses the blank row.

I could just select the data rather than the entire columns, the problem is I want this to work even when the list grows to an undetermined size, hence selecting the entire column is the best approach.

Any ideas whether I can have it sort the entire column but only sort from 3rd row down, and keep the data in 3rd rowdown, with 2nd row still empty? (only thing I can think of is putting something in the 2nd row so that it always comes out at the top of the sort, but with a range starting at A3, it shouldnt be looking at row 2 anyway).

Thanks


I, too, am sorting a workbook by time values and having problems. My user's workbook has a table that is B3:AO33; Row 3 are column headings. We want to sort the entire table by column E, "Depart Time". This formula is copied down each cell:

=IF(ISERROR(VLOOKUP((Dispatch!$N5-VLOOKUP(Dispatch!$M5,'Lookup Table'!$1:$1048576,12,FALSE)),'Lookup Table'!$M:$N,2,FALSE)),"",VLOOKUP((Dispatch!$N5-VLOOKUP(Dispatch!$M5,'Lookup Table'!$1:$1048576,12,FALSE)),'Lookup Table'!$M:$N,2,FALSE))

We only have data in the first 7 rows at this time and the formula results in column E a
12:30
3:30
15:00
10:30
7:00
6:00

The formatting is Custom, h:mm. When I sort the table on this column I get the following results:
10:30
6:00
12:30
15:00
3:30
7:00

If I select only these 7 cells and sort them, they sort correctly. If I copy/paste special, values, the times still appear correctly with the AM or PM attached.

What do I have to do to sort by this column and get the correct result?
__________________
Thanks,
Shirlene


I have been tasked to sort a pivot table according to a specific column / PivotItem in a pivot table. When I sort the field using PivotTable Field Advanced Options it sorts the pivottable by the grand totals of the PivotField containing the PivotItem. Is there a way to specify to sort the table only by particular Pivot Item?
For example. I have weekly numbers. Management would like to have the table show the rows in the pivot table sorted descending by the current's weeks counts.


Hello -

I have a challenge for some MVPs or Excel gurus out there (who knows -- maybe this is easy...).

I have an example sheet (which you can download below). The problem involves sorting table data on tab 2.

http://dl.dropbox.com/u/2625864/Foru...ampleData.xlsx

On tab 1 (Data), there are two columns (Name and Completed?). There is also a table where sorting works fine. When I sort any of the columns, the data moves with the appropriate rows as expected.

On tab 2 (Data2), the same table from tab 1 is there, in addition to another sammple table.

When I sort items in either of the two tables, the rows within each of the tables DO NOT move appropriately. For example, if I sort the names, the names sort fine but the data in the other columns does not move. If I sort the columns with the numbers, the numbers don't even sort correctly (an ascending sort sorts the data in a very random order).

I'm decent at Excel (or so I thought) but this has got me baffled.

Any help would be great!!!




hi i have created a table and there are 4 columns in the table

item,cost,price,earning

however, whenever i sort one column, from smallest to largest, the other columns are not adjusting themselves accordingly.

so my question is, how can i link 4 cells together horizontally, so whenever i sort anyone of the 4 cells, the other 3 cells which are in relation to their rows, will sort too

it would be much appreciated if someone helped me out, thank you