Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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





Good morning,

This is my first post, and I have searched the forum pretty well.

The two sheets in question are ‘Employee Totals’ and ‘PP19’

The column “Name” in PP19 is linked to the column “Name” on Employee Totals via
='Employee Totals'!B5

My question is, if I sort Name on Employee Totals, it sorts Name of PP19. However, the row associated with the sort does not change. So if I have data on PP19 for Test 1, if I sort, the data stays with the row but Test 1 will move to the appropriate sort without the associated data.

Question 1: How can I lock the row to move with the sort?

Question 2: With regards to filters, I understand how to manually select filters, but how can I pass the value of a filter from a drop down box as included in the Search By box on PP19? Would it require VBA?

Thank you in advance.

Side note: I dont want to use a table because I plan on sharing this among multiple users on a shared network to be updated at the same time.

I have a table that has three columns, each getting its data from a COUNTIF function. The three columns are Totals, Positive and Negative. I also have conditional formatting that formats the row depending on the totals.

I am trying to sort these based on the totals or the conditional formatting (Fill colour) but each time I try the sort leaves 3 or 4 cells above the marker (i.e. there should be 4 blue rows on the top and 6 white rows beneath after sorting, but it seems to ignore some of the cells, with 3-4 white cells on top of the blue cells and more white cells below. I thought it was a pretty simple thing and have tried sorting it by both the cell colour and the totals amount seperately to no avail.

Please help!!!


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 table set up in Excel 2007 (see below) where the columns on the left are available for user input (denoted by x) and the columns on the right (denoted by f) are columns with formulas in them.

A B C D E F G
x x x f f f f f
x x x f f f f f

A feature of Excel 2007 tables is that once you start to enter data on a new row (in this case A3 for example), the table automatically expands to cover the new row and the formulas in the above row are copied over (the ones in D-G).

Everythings works ok until I want to lock the cells other than those intended for user input. That is, I want to lock columns C-G to avoid the user messing up the formulas and leave columns A-C open for input. If use format cells -> protect -> lock cells for columns C-G and protect the sheet, the table no longer resizes when I input data in the row below and if I try to copy & insert the last row of the table, I get an error message saying "The cell or chart that you are trying to change is protected and therefore read-only".

Any thoughts on how I could get this setup to work? Many 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)


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!


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 a data table and when I sort it, either with the Sort command on the Ribbon, or using the "A to Z" button I have in my Quick Access Toolbar, Excel assumes that the table is three rows higher than it really is. In order to do the sort, I have to use the Sort function and "redefine" the table area.

Now, I have had a similar thing sometimes in the past, but it has always been because I accidently left text or a formula in a column or row adjacent to the table. In this current case, there are no such problems - all the cells around the table are empty.

Other people use the workbook and they might have done something. Does anyone know how to help. Is there a place where I can see what Excel is assuming for the area and change it permanently?

I'm using Excel 2007.

Thanks,

MikeG


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?


Hello:

I have a workbook (Excel 2003) and I am having trouble with a particular
sorting issue. On page 2, column GL contains specific data that feeds into
formulas used in Page 1. When I sort my data on page 2 it changes the
formula totals on page 1.

What I want to do is have the ability to 'lock' the data on page 2 so that
after I sort it doesn't change the totals in my formulas using the Page 2
cell references.

For example on Page 1, one of my cells reads the following formula:

='Rep Stack Ranking'!GL$12+'Rep Stack Ranking'!GL$13+'Rep Stack
Ranking'!GL$14+'Rep Stack Ranking'!GL$40

Rep Stack Ranking being Page 2.

I have to sort other data on Page 2 to show which sales reps are leading in
% to Quota, but Column GL on that same page has other pertinent information
which feeds into Page 1 and eventually Page 3 of my workbook.

Because I have to sort the Reps based on Stack Ranking (when I enter daily
sales data reps are sorted by Full Time/Part Time then Store Location then
Name) it doesn't dynamically update the cell references I have made in my
formulas on Page 1 - causing part of my overall totals to be incorrect.

One final example - if Rep A is on row 10 on Page 2, but due to sales
performance once I sort to show % to Quota Rep A's information is now on row
15. BUT my formula on Page 1 still references Row 10 instead of having
updated to Row 15.

I hope this makes sense to someone

Have a great day ~ and thanks for any suggestions!




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!


I have a large Table (using the new create function with the drop down alphabet-sort at header) with many columns including; "Product", "Name", "Address", etc.

I need to edit the table on regular bases and re-sort it for different columns each time.

Is any formula I can use to pull out the "Names" column to a seprate table and sort alphabetically (and remove duplicates is possible)?

Ive tried variations of extra column with COUNTIF( X ; "<="& Y) and VLOOKUP()
Ive tried variations of INDEX() etc.

I do get close but as soon as I sort the "Table" to a different column everything gets messed up again.


Btw. this is very hard to research because there are so many site on how to do a regular alphabet sort on a basic table. Even if you don't have the excate answer for me can you give me a an Idea what to search for?


I have a table with customer data I want to sort by Nationality. Name, address, Customer No. etc, everything unsorted.

EXP:

I Name I First Name I Nationality I Cust.NO.

I Smith I John I Canadian I 1338 - 1


Of course I want the data of the customers stick with the name, so if I sort by name the adress, etc next to it is the right adress, not the adress of someone else.

But I don't have a clue how to do that. Selecting the "Nationality" cell only and sort will only sort the Nationalities.
And if I select the whole data and use sort, the stuff gets sorted by Mister/Miss (the first cell/column).

Is there a way to specifiy by which column I want to sort the selected data?
or is there a way to "lock" a (horizontal) cell, so if I sort the Nationalities the stuff that belongs to it, will always stick to it?


I'm sure this is pretty easy but I need your help.

So I have a large table of data. The first 2 rows contain the column headings. I'd like to be able to sort the data below the column headings in ascending and descending order with one click of the mouse.

Currently if I want to sort the data I have to highlight all of the data in the table, then click Data, Sort, and select the column to sort by. I would like a quicker option.

Perhaps this could be done using a pivot table but I'm not familiar with how to use them. I gave it a shot but it's over my head.


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!!!


I have a table comprised of links to data from other tabs within my workbook. Once I "protect" the sheet that contains my summary table, I am not able to sort alphabetically (using the auto-fill/sort feature in the column headings of the table). I am able to check and uncheck from the drop down auto fill box, but not sort...

any suggestions?