How to sorting out the data by worksheet function

[Ellis Yu]

Dear all,

I have to 2 worksheets. One of them is used for storing up the data
and the other worksheet is to display specified type of data in worksheet
one. I'd written the worksheet formula for it. But my problem is I have to
sort the data in worksheet two according to one of column. Since I use the
Index formula to pick up the necessary data, I can't use the sort function
to so. Anyone know how to implement sorting by worksheet functions? Please
help. Thanks

Best Rdgs
Ellis

 

[Herbert Seidenberg]

Assuming Sheet1 has a range named Data1 with the formulas
=INDEX(Data2,row_num)
and Sheet2 has a range named Data2
and the aim is to temporarily freeze Data1 while sorting Data2,
do this:
1. Add a helper column with sequencial numbers next to Data2
2. Tools > Options > Calculations > Iteration > Check
3. Name a cell Freeze and set it to FALSE
4. Change formulas for Data1 to
=IF(Freeze=TRUE,Data1,INDEX(Data2,row_num))
5. Set Freeze to TRUE. You can now sort Data2 without
changing Data1.
6. To restore, sort on the helper column and set Freeze to FALSE