Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting the DATA but NOT the Formulae! | Excel Discussion (Misc queries) | |||
Formulae: Paste value formulae after doing an average operation | Excel Discussion (Misc queries) | |||
Excel - how do I delete data but not formulae? | Excel Discussion (Misc queries) | |||
Formulae extracting data from other spreadsheets | Excel Discussion (Misc queries) | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions |