CUSTOM SORTING will not work on table with INDEX formulas

[InterstateRentals]

Hello. We'd like help on a problem we are having with sorting a table.

The table (A1:E10) was created by pulling data from another worksheet using INDEX and ARRAY formulas.

We inserted 6 columns to move the table over to G1:K10 (used column F to space the two tables).

We then duplicated the table: cell A1=G1, B1=H1, ...., E1=K1 and dragged the formulas down. We now have a duplicate table that we want to sort.

We choose CUSTOM SORT and choose 2 levels of sorting ... column A and column C.

PROBLEM: The ARRAY formulation evidently overrides the sort function because the table remains as it was when the sorting command is executed.

Is there a way to bring the data from table G1:K10 over to table A1:E10 in a manner that will allow sorting??

Thanks for help with this issue!

[rcm]

I have had the same problem and had to use indirect references . send a sample in excel and I will post my way around it

[InterstateRentals]

OK, thanks. I'm not sure how useful this worksheet will be since I had to delete all the linked customer data worksheets ...

Thank you for your help!

[rcm]

Could you include a sample of those links (make up some)

[FDibbins]

Instead of a complete copy of the table, including formulas, why not create the 2nd table and then just reference the 1st 1 and then try the sort? (this may be what rcm did, i have not looked at their WB)

[InterstateRentals]

OK, I made up data and the table is filled.

FORD, I'm unclear on what the difference is between what we did and using a "reference" to link the tables. Thanks!

[FDibbins]

Sorry, noyt that wont work, I was thinking of something else

[Winon]

Hello InterstateRentals,

...formulation evidently overrides the sort function because the table remains as it was when the sorting command is executed

Correct.

Copy values only. Click on the "Up-Date" Button in Column B of the attached sample Workbook, and then apply your Sort.

Regards

[InterstateRentals]

Winon, that is awesome. Thank you for helping us!

[Winon]

Hello InterstateRentals,


Thank you for the feedback, and for adding to my Reputation.

You are welcome, glad I could help.

Please remember to mark this Thread as "Solved".

Regards.