Sorting data, but not the formulae

[Harry123]

I have two tables. Data is entered into the first table, for example 5, 2,
3, 4, 1 in A2-A6. This data is then copied, using formulae, into my second
table. I then have a macro that will sort the data from low to high.
Obviously, the data changes from 5, 2, 3, 4, 1 to 1, 2, 3, 4, 5. However, the
formulae that I used to copy the data from table 1 to table 2 also moves with
the data. I want the formulae to stay in the cell I typed it into when I sort
the column. Is there a way to do that?

 

[Tom Hutchins]

The values you are sorting are the results of the formulas. If you sort the
values returned by the formulas, you are going to sort the formulas. Here are
a couple of workarounds which may work for you:

- Have the retrieval formulas in a column outside your second table. Then
have your macro copy the retrieved data as values to the second table and
sort it there only.

- In an unused column adjoining your second table, add sequential numbers to
the rows containing your formulas. Then you can reset the formulas into their
original order by sorting using the sequence numbers.

Hope this helps,

Hutch