A Sort Question for Excel Gurus

[Abraxas]

Two simple questions (I think), but I can't find answers right now.

I have a spread sheet in excel made up of a dozen or so worksheets. The first sheet has a column of data that is repeated on the other 11 or so. I want to run a sort on this column on the first sheet and have the others also be sorted with all the other columns of data on those other sheets sorted at the same time. How do I do this? I haven't been able to find a way to link the cells in the other columns on the other pages to the one column that will be sorted.

Is there a way to change the default sort conditions? Right now I am sorting a column of text, and sorting using the sort button on the tool bar always treats the first entry as a column header.

Thanks for any help

[Ayrk]

I'm not sure how you could do the first one, sorting across multiple sheets. Can't be done as far as I know.

For you second question, you can choose Data-Sort... and sort it that way. Make sure to state that your selection does not have a header row, that is why the first row is not sorting.

Originally Posted by Abraxas

Two simple questions (I think), but I can't find answers right now.

I have a spread sheet in excel made up of a dozen or so worksheets. The first sheet has a column of data that is repeated on the other 11 or so. I want to run a sort on this column on the first sheet and have the others also be sorted with all the other columns of data on those other sheets sorted at the same time. How do I do this? I haven't been able to find a way to link the cells in the other columns on the other pages to the one column that will be sorted.

Is there a way to change the default sort conditions? Right now I am sorting a column of text, and sorting using the sort button on the tool bar always treats the first entry as a column header.

Thanks for any help

[Sir Whiskers]

I can see a couple ways to approach this:

1. Link each cell in the extra pages to the same cell in the first page. The easiest way is to click on a cell, e.g., A1, type the = sign, then click on the same cell reference on the first sheet. Copy this cell down as far as you need to. Then comes the tedious part: change each of these cells to an absolute reference, i.e., A1 become $A$1, B1 become $B$1, and so forth. Then copy the entire column to your other sheets.

Once this is done, each of your sheets will reflect the value in the first sheet, by cell, regardless of how you change the values within each individual cell (that's a result of the absolute reference).

2. Create a simple macro whereby you sort each column in turn. Save the macro. Run it whenever you want to sort all the columns. You can even attach the macro to a command button if you want.

Hope this helps.

[Sir Whiskers]

BTW, I did a test at work today and it doesn't appear that you have to use absolute references to make this work. I still recommend using them, however, as that will allow you to add/delete individual cells (cells, not cell contents) in the primary column without messing up your other sheets.

[dvvega]

Another way you may do it is as follows - rough ideas:

1. Created Named Regions for all the sortable data on all the sheets.
2. You have some sort of SORT button attached to the first sheet to sort the column.
3. Record a macro for the button that sorts the column.
4. Edit the macro and add sorting for each of the named Regions in your spreadsheet.