Sort Contiguous Columns - Danger

I am working on an Excel 2000 VBA project.  The users of the main sheet in 
this workbook complain that sometimes users will inadvertently sort only the 
first few columns of the sheet so that the data in the sheet gets 
"discombobulated" - the data in the sorted columns no longer relates properly 
to the data in the other, non-sorted columns.  For example, if the sheet 
contains two columns - firstName and lastName, and the user sorts only the 
firstName column in alpha order, the names are no longer properly associated. 

Excel does not always warn the user that he is sorting only some of the 
columns in the sheet.  

Question: Is there a Best Practice for ensuring that users do not sort by 
only some (not all) columns in a sheet?  Or is there a best method for 
ensuring that should the bad sort happen, the records can be returned to 
their original state, even after a save?  

Thanks in advance for your help ...

Bill  
0
Utf
5/1/2010 4:54:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
466 Views

Similar Articles

[PageSpeed] 46

Always keep a spare copy and protect the Sheet from sorting.



-- 
Regards
Dave Hawley
www.ozgrid.com
"WCM" <WCM@discussions.microsoft.com> wrote in message 
news:69CD52ED-F10C-49F2-92AB-C46A7A68E1E0@microsoft.com...
>I am working on an Excel 2000 VBA project.  The users of the main sheet in
> this workbook complain that sometimes users will inadvertently sort only 
> the
> first few columns of the sheet so that the data in the sheet gets
> "discombobulated" - the data in the sorted columns no longer relates 
> properly
> to the data in the other, non-sorted columns.  For example, if the sheet
> contains two columns - firstName and lastName, and the user sorts only the
> firstName column in alpha order, the names are no longer properly 
> associated.
>
> Excel does not always warn the user that he is sorting only some of the
> columns in the sheet.
>
> Question: Is there a Best Practice for ensuring that users do not sort by
> only some (not all) columns in a sheet?  Or is there a best method for
> ensuring that should the bad sort happen, the records can be returned to
> their original state, even after a save?
>
> Thanks in advance for your help ...
>
> Bill 

0
ozgrid
5/1/2010 6:27:22 AM
Thank you, Dave.  I beieve you are right about that.  Seems the only remedy 
would be to unlock all the cells in the sheet, then protect the sheet.  But 
this removes the ability to sort altogether, which is not ideal.  Keeping 
regular backup copies is probably the only practical answer.

Bill

"ozgrid.com" wrote:

> Always keep a spare copy and protect the Sheet from sorting.
> 
> 
> 
> -- 
> Regards
> Dave Hawley
> www.ozgrid.com
> "WCM" <WCM@discussions.microsoft.com> wrote in message 
> news:69CD52ED-F10C-49F2-92AB-C46A7A68E1E0@microsoft.com...
> >I am working on an Excel 2000 VBA project.  The users of the main sheet in
> > this workbook complain that sometimes users will inadvertently sort only 
> > the
> > first few columns of the sheet so that the data in the sheet gets
> > "discombobulated" - the data in the sorted columns no longer relates 
> > properly
> > to the data in the other, non-sorted columns.  For example, if the sheet
> > contains two columns - firstName and lastName, and the user sorts only the
> > firstName column in alpha order, the names are no longer properly 
> > associated.
> >
> > Excel does not always warn the user that he is sorting only some of the
> > columns in the sheet.
> >
> > Question: Is there a Best Practice for ensuring that users do not sort by
> > only some (not all) columns in a sheet?  Or is there a best method for
> > ensuring that should the bad sort happen, the records can be returned to
> > their original state, even after a save?
> >
> > Thanks in advance for your help ...
> >
> > Bill 
> 
0
Utf
5/1/2010 12:04:01 PM
You could give the users a macro to sort the data.  Your macro would do the
unprotecting, sorting, and reprotecting.

I like this technique from Debra Dalgleish's site:
http://contextures.com/xlSort02.html#Rectangles
Sort With Invisible Rectangles



WCM wrote:
> 
> I am working on an Excel 2000 VBA project.  The users of the main sheet in
> this workbook complain that sometimes users will inadvertently sort only the
> first few columns of the sheet so that the data in the sheet gets
> "discombobulated" - the data in the sorted columns no longer relates properly
> to the data in the other, non-sorted columns.  For example, if the sheet
> contains two columns - firstName and lastName, and the user sorts only the
> firstName column in alpha order, the names are no longer properly associated.
> 
> Excel does not always warn the user that he is sorting only some of the
> columns in the sheet.
> 
> Question: Is there a Best Practice for ensuring that users do not sort by
> only some (not all) columns in a sheet?  Or is there a best method for
> ensuring that should the bad sort happen, the records can be returned to
> their original state, even after a save?
> 
> Thanks in advance for your help ...
> 
> Bill

-- 

Dave Peterson
0
Dave
5/1/2010 12:15:20 PM
Reply:

Similar Artilces:

adding an horizontal scrollbar to columns
Hi, I've got a simple excel spreasheet with 10 columns. I'd like to be able to add a scrollbar from the third one to the eighth one so that when I move the scrollbar, columns that do not belong to this group will not move. It's pretty hard to explain, hope this was clear enough. Can someone helps ? Thx If you use Windows > Freeze Panes with your cursor in cell C3 then you can stop columns A and B from moving off the screen when the user scrolls right. I am not sure how you would freeze the ninth and tenth columns though. Hope this helps Rowan "Sam" wrote: > ...

Print Column Headings on multiple sheets
I have a budget that when printed is one page wide and three tall. How can I add the column headings to the second and third pages? click file/page setup and click the sheet tab click the icon next to rows to repeat at top select the rows click the icon again and then ok -- Gary Keramidas Excel 2003 "wx4usa" <wx4usa@gmail.com> wrote in message news:19421c6b-0ea5-4524-8574-8dea96d44bde@22g2000yqr.googlegroups.com... >I have a budget that when printed is one page wide and three tall. How > can I add the column headings to the second and third pages?...

Simultaneously sort 2 worksheets using linked column
I have 1 worksheet (Attendance) that keeps track of attendance. Rows 10 - 37 (27 students) with columns J - DQ for recording daily attendance. I'm designing a 2nd worksheet (Progress) in the same workbook to keep track of progress. Currently, the student names on Progress are linked to those on Attendance. This works fine. However, when I sort Attendance, only the student names on Progress move causing information on this second sheet to no longer correspond to the correct student. Is there a way to sort Attendance and have the data I enter on Progress stay with the names they're...

Removed content in right columns and limited size of spreadsheet
I was trying to get rid of the contents in the columns to the right of my spreadsheet and instead I accidently deleted everything to the right (area is now grey). Now I can't insert new columns because I have limited the size of the spreadsheet. How can I recover columns to the right? I can't just click undo because I have saved and gone out of the spreadsheet. The data in deleted columns is lost I do not understand how you think you have limited the size of the worksheet That is not possible tell us more best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/...

Merge cells in two columns
Hi I have a worksheet with 300 rows Is it possible to merge 2 adjacent columns in one or two steps without having to do each one individually I've just learnt how to merge 2 adjacent cells to include all data in the range in the merged cell column C has the word 'dist' in ever row column D has numbers from 1 to 14 in random order using Excel 2000 Martin ���� >>Is it possible to merge 2 adjacent columns in one or two steps without having to do each one individually Easy, but you have to use a menu choice not shown on the menus by default. Do a View, Toolbars, Custom...

sorting(?) problem
Hi! I have problems generating a rutine that will automatically sort my sheet. The sheet is in this format (always 2 columns only the rows may vary) a 1 a 2 a 3 b 2 b 1 c 7 d 1 d 3 d 4 d 6 d 7 e 7 I want it to look like this: a b c d e 1 2 7 1 7 2 1 3 3 4 6 7 Anyone know what vba code to use to get this done? /Colargol Never mind. It's solved On Thu, 17 Feb 2005 20:48:09 +0100, Colargol <test@com.com> wrote: >Hi! > >I have problems generating a rutine that will automatically sort my >sheet. >The sheet is in this format (always 2 columns only the ro...

find location max value in column
I need to find the cell location of the max value in a column. If paste the following [from =(cell onwards] into a cell I get the righ result. Having a hgard time getting the right VBA code to make thi work in an unattended macro. Probably some bonehead mistake on my part but I cannot find it. Suggestions? GOAL is to make this formula work. Range("z2").Formula "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))" TIA, Ro -- rroac ----------------------------------------------------------------------- rroach's Profile: http://www.excelforum.com/member.php?act...

Column Sum in Footer
Hi, I have created a Tabular report that lists names and weights for different people. I want to create a stand-alone field in the page footer that will display the SUM of all the weights on the report. Can somebody please help me? (I'm using Access 2000 btw) I have tried creating a text box with the Control Source property set as "=Sum(weight)". However, this just produces an error. Thanks in advance. Julie Smith wrote: >Hi, >I have created a Tabular report that lists names and weights for different >people. I want to create a stand-alone field in the page f...

Adding column data based on match in 2nd column
Hi, Simple problem, but got me stumped. The setup: I have 2 columns. The first (column A starting in row 10) has members' names. The second (column B starting in row 10) has the additions made by the members over time. Column B has a 'header' cell that adds up the values entered by a particular member. The header cell for column B is B9. Cell A1 contains the member's name that cell B9 has to add the values for. I need a formula for Cell B9. For example: [Cell A1] Member A [Row 9] Formula needed for cell B9 COLUMN A_______|| COLUMN B____ [Row 10] Member...

Sum cells using criteria from a row and a column
I am trying to sum cells in a sheet based on matching criteria in a row and matching criteria in a column. The data that I am working with is represented as: Resource: Resource1 JAN JAN JAN JAN FEB Project Project Description 12/19 12/26 1/2 1/9 1/16 Admin Administration 10 10 10 10 10 Holiday Holiday 8 8 Other Other time Off Training Training 20 Vacation Vacation 22 30 10 30 22 I ...

2003 Filter and Sort Ascending
I have a database with over 2000 entries. I generated the database in excel 2007, but my clients only have 2003. When they sort the zipcode field in ascending order in 2003 (using the filter drop-down), the database only sorts some of the data and not all of it. How do I solve the problem of the filter sort ascending to capture all of the values in the database? Rob If full column is selected the sort should be OK. Don't let Excel guess your sort range. You may have a blank row. In addition................... Excel 2003 will show only the first 1000 unique items in t...

Sort name from specified interval table
Hiiii I am facing a silly problem i think u guys are rescue me from that... My prob like I have two cloum one colum contain Name of person and another coloum contain their salary.I prepared a interval table contain 4 row according to salary range....I have find the name whos salary has on the specified range on ist row of interval table.... Can any one giv the idea about that..Perhaps I tried it VLook up Function...But i faced proble to sort the name from ist colum -- mun04 ------------------------------------------------------------------------ mun04's Profile: http://www.excelforum....

Help with Column Lables
I am trying to use Filtering in Excel 2003. I highlight the columns I wish to include in my filter. I then select advanced filtering and click ok. When I do I am told that "Microsoft Office Excel can not determine which row in your list or selection contains column labels, which are required for this command. " I select OK and it, by default selects the first row of the selection or list to be used as labels and not data. This is ok but � Just how do I create column labels so that I do not constantly get this prompt. I have looked in help under Creating Labels, Creating Data L...

Can't ungroup so that I can sort, etc.
I've accidentally grouped something in Excel (XP) How can I know what I grouped so that I can ungroup it? Thanks. Lydia Hi simply click on one single tab name -- Regards Frank Kabel Frankfurt, Germany Lydia D wrote: > I've accidentally grouped something in Excel (XP) How can I know > what I grouped so that I can ungroup it? Thanks. Lydia ...

split a column
Hello! I have a worksheet with only two columns but 1800 records. I would like to print this sheet and use the least paper possible. The solution of course is to divide the two rows in multiple rows and thus filling up the paper. I would howerver like to have the records in alphabetical order on every sheet, so just cutting the rows at row 600, 1200 and 18000 doesn't do. I would very much appreciate any good idea! Thanks in advance! Pete Hi Tokyogroove, See David McRitchie's Snake Columns page at: http://www.mvps.org/dmcritchie/excel/snakecol.htm --- Regards, Norman ...

Excel: printing with hiddent columns
My worksheet with hidden columns in the middle should fit easily on one sheet, but print preview shows a page break after the hidden columns. Apparently the width of the hidden columns was included. How do I get the print setup to ignore the hidden columns when determining page breaks? ...

Adding an apostrophe to a column of numbers
Hello, I need to add an apostrophe to each cell in a long column of numbers. I do not want to format them as text or format them as custom - our system requires the apostrophe at the beginning of the cell. My question is - is there a way to format one cell with the apostrophe and then copy that apostrophe down to the other cells?? or can someone write me something that will allow me to do it quickly? I want to avoid having to go into each cell and put the apostrophe in! Thank you in advance.... One way. Change 9 to your column Sub addapostophe() mc = 9 'column I For i = ...

From VBA: How to select a column by column number instead of letter? #2
I'm in MS Access and don't know from column letters - all my constants are in terms of column number. I'd like to select, say, column number 7 and hide it.... ? -- PeteCresswell Gord and I sent you a macro solution for EXCEL as this is an Excel ng. -- Don Guillett SalesAid Software donaldb@281.com "(Pete Cresswell)" <x@y.z> wrote in message news:uch701tveuot081n0q0h9rpq1r0c24v1sc@4ax.com... > I'm in MS Access and don't know from column letters - all my constants are in > terms of column number. > > I'd like to select, say, column number...

Excel 2007 Sort-How to Retain Sort Order
In earlier versions of Excel, if you had a worksheet that you sorted, then the sort columns and order (ascending/descending) was retained, so that if you added data and wanted to sort it, you simply told Excel to re-do the same sort. But in Excel 2007, the sort instructions are not retained, so that I have to recreate the entire sort instructions from scratch every time that I need to sort the worksheet. How do I get Excel to retain the sort instructions for a worksheet so that I can re-sort the worksheet without having to laboriously re-create the sort instructions every time that I ...

The most occurence of a value in a column
I have 1 to 5 as values in a column. How can I find the number with the most occurrence in that column? Thanks, cpliu Hi =MODE(A1:A100) -- Regards Frank Kabel Frankfurt, Germany "cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag news:Xns959C8C47C309BchanciusliuDeleteThi@130.133.1.4... > I have 1 to 5 as values in a column. How can I find the number with the > most occurrence in that column? > > Thanks, > > cpliu Try: =INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,A1:A5)),COUNTIF (A1:A5,A1:A5),0)) Array-entered, meaning press ctrl/shift/enter. H...

How attach data in a row so it can be sorted by the date column?
I have a four spread sheets all the same that I have copied and pasted into one. I need to know now how to sort the rows by the date column. When I select the date column and hit the tool bar a-z button only the dates sort and not other information in the adjacent columns. What can I do? Hi! Select the entire range that you want to sort. Goto Data>Sort. Sort by the column that contain the dates. Biff >-----Original Message----- >I have a four spread sheets all the same that I have copied and pasted into >one. I need to know now how to sort the rows by the date column. W...

Way to put multiple rows into the same column.
I have data that is in the format of 3 columns wide by about 3000 rows. I want to rearrange each row to become a single column. Eg. Cells A1,B1,C1,A2,B2,C2 to become in cells A1,A2,A3,A4,A5,A6 respectively. I have transposed the data but this only puts each row in it's own column. I still need to put all the new columns into one column. Thankyou for any help given. Hi, Try my EastyText_Rev1.xls at http://www.geocities.com/excelmarksway It might work, er, maybe, hmmm... - Mark >-----Original Message----- >I have data that is in the format of 3 columns wide by about 3000 r...

alphabetize a column of words in MS word
can i alphabetize a column of words in MS word In Word 2003 or earlier, use Table > Sort. In Word 2007, click the Sort button on the Home ribbon (the one with an A and Z and an arrow). -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. On Thu, 20 May 2010 15:36:01 -0700, Dale <Dale@discussions.microsoft.com> wrote: >can i alphabetize a column of words in MS word ...

Access/Excel: Create multiple Lines/Column out of one mulit-line Column?
Hallo everyone! I spent a lot of time today searching for a response to my question but I had unfortunately no success. The problem is the following: I have got an Access-table called Employee with an ID and one corresponding Field/Column called comment. These comments are stupidly multi-line (separated by a Return). It's my aim to have access (he he, wordplay) to these two fields in Excel whereas the field shouldn't be longer that 255 characters. I considered three solutions: The table could be changed after being exported to excel in the following two ways: - Multiple Comment Column...

Multiple Column Conditional Formatting
Hi, I'm needing some help with performing a multiple column condition formatting. I have tried many "forumula is" in the conditional formatting with no success. I'm missing something. What I would like to have happen is, if the 2 conditions are true then I want the field to be color filled. Example: if the numeric value in k2>10 and the numeric value F2>100 then I would like cell K2 to be filled in red. Many thanks for your help Colleen one way: Select K2: CF1: Formula is =AND(K2>10,F2>100) Format1: <patterns>/Red In article &...