Sorting data, but not the formulae
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? |
Sorting data, but not the formulae
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 "Harry123" wrote: 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? |
All times are GMT +1. The time now is 10:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com