+ Reply to Thread
Results 1 to 4 of 4

How to sort worksheet data keeping rows in tact

  1. #1
    Registered User
    Join Date
    05-09-2008
    Posts
    2

    How to sort worksheet data keeping rows in tact

    Hi,
    Here's my problem. Not everyone who uses my spreadsheet knows how to or cares how to properly sort a bunch of cells. I have a worksheet full of text and number data such as this (and you may say to use access, but that is not an option for me):

    Company A address pieces description
    Company B address2 pieces2 description2

    etc.

    So sometimes people highlight say both companies and addresses but not the pieces or description. Then we end up with Company A and address but with Company B's pieces and description and everything becomes unsorted.

    Is there anyway to "lock" the rows so that no matter what you sort by it pulls the rest of the row's data along with it?

    I would realllllly appreciate any help you can give me on this.

    Thank you very much,
    -Leela

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    One way would be to have any sort controlled by a macro. Protect the sheet to prevent the user manually sorting a range, then have a button which runs a sort macro. The first line of the macro would temoprarily unprotect the sheet, and the last line would reset protection.

    HTH

  3. #3
    Registered User
    Join Date
    05-09-2008
    Posts
    2
    Thanks Richard!
    Do you know where I would be able to get some information on how to create such a macro? If I were to use a macro would the sheet still be fully functional otherwise except for sorting?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote Originally Posted by lea147
    Thanks Richard!
    Do you know where I would be able to get some information on how to create such a macro? If I were to use a macro would the sheet still be fully functional otherwise except for sorting?
    Hello Lea,

    The easiest way of creating a macro, and I guess most of us started this way, is just start the macro recorder, Tools-->Macro--> Record new Macro, then do the sort and finish by stopping the recorder. Then all you have to do is run the macro every time you want to do the sort again. There are of course limitations. The macro always uses the same range of cells you selected initially, so that if your list/table grows in size, part of it won't be sorted. You can of course modify the macro to overcome this, but you'll probably want to play around with recording macros first.

    When you protect a sheet, all cells are locked by default. If you want people to modify only certain cells you can unlock them first with Format->Cells-> select the Protection tab and untick the 'locked' box. Now switch sheet protection on and only those cells you've unlocked will be available.

    If you want some help with a sort macro then just upload your workbook to the forum, indicating which ranges you want to sort, and I'll put something together for you. Anonymise the data first if it's in any way sensitive or confidential.

    Rgds

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1