MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by JM on January 03, 2002 4:23 PM

I'm a low-level user so I may be missing something obvious.

Here's the deal - I have an enormous spreadsheet that needs to be sorted, by rows, in two different ways on different sheets in the same workbook. (ie - Each row will have the same info but on each sheet the rows will be in a different order) I want the second sheet to accurately reflect changes made on the original sheet so I don't have to input the data twice.

The closest I've found during my RTFM sessions & searching your archive is Linked Objects but I must not be doing it right if that's the answer. Also, the affected area is something like 40 columns by 500 rows so it's not something I want to type in each cell. Any ideas or (hopefully) easy fixes?

Posted by Chiron on January 03, 2002 4:35 PM

Why not have just one sheet and a macro attached to a button that toggles between the two sorts.

Or two macros (one for each sort) attached to two buttons.
You could create these two macros easily with the macro recorder.

Posted by Scott on January 03, 2002 7:07 PM

There are a lot of different ways to do this using code, or Scenerios, but probably the easiest way is to just copy the info from one sheet, and paste links to the other sheet. Then you could sort the original sheet, and then sort the 2nd sheet another way. Any changes made to the data on the 1st sheet will carry over to the second sheet.

Be advised though, when you sort the first sheet, you will change the sort on the linked sheet as well (not always the same though, depending on how it was last sorted). So when ever you sort the original, you will have to sort the 2nd sheet as well to keep the order correct.