Display one list on multiple worksheets

I am creating a project plan with a long list of steps that needs to be viewable in causal sequence and chronologically (not identical).

What I'd like to do is create the list of steps by causal sequence in a list (now called a "table" in excel 2011) on sheet 1. I then need a way to feed that list into a list in sheet 2, so it can be sorted chronologically.

Caveats: the steps will be added to by other teammates, so the list on sheet 2 should update when items are added to the list on sheet 1. Sorting one list should not impact the sorting of the other.

Any ideas?

Someone will probably come up with a more elegant method, but it would be quite easy to add a column to the left of the data on the table on Sheet1, numbering each line from 1 through whatever. Then, on Sheet2, you can reference each cell in the table on Sheet1 using a VLOOKUP to preserve the original order. See the attached example if this isn't clear. Sort the table (in the box) including both Column A and B and you will see that the results (in yellow) don't change. Sorting Columns E and F [together] also doesn't affect what's in Columns A and B.

Originally Posted by bentleybob

Someone will probably come up with a more elegant method, but it would be quite easy to add a column to the left of the data on the table on Sheet1, numbering each line from 1 through whatever. Then, on Sheet2, you can reference each cell in the table on Sheet1 using a VLOOKUP to preserve the original order. See the attached example if this isn't clear. Sort the table (in the box) including both Column A and B and you will see that the results (in yellow) don't change. Sorting Columns E and F [together] also doesn't affect what's in Columns A and B.

Thanks, Bob, that solves the independent sort issue.

The remaining challenge is this: when I add something to the list on sheet one, I need to figure out a way for it to get added to sheet two. Since the reference numbers need to be defined explicitly in list two (otherwise the sort won't work), is there a way to accomplish this?

My guess would be that to make the list on sheet 2 update "live", one would have to make list 2 a dynamic range based on the length of list 1 (no idea how to do this), and then automate the reference numbering of each new row.

Yes, you might be able to use a dynamic range name. But would the attached work instead? You do need to pre-define the refernce numbers, but you can use LOTS of them (and hide the extra columns if you want, as long as they're included in your sorts).

If you want to explore the dynamic range name approach, please let me know.

See the following link for information on dynamic range names:

http://www.ozgrid.com/Excel/DynamicRanges.htm

Originally Posted by bentleybob

See the following link for information on dynamic range names:

http://www.ozgrid.com/Excel/DynamicRanges.htm

Thanks for your help, Bob!