SORT data containing formula

[Abrar]

How to sort data containing formula For example

Column A Column B Column C
1 10 =B1-B5=4
2 9 =B2-B5=3
3 8 =B3-B5=2
4 7 =B4-B5=1
5 6 =B5-B5=0

Now if I sort in descending order of Column A, the Data I expect is

Column A Column B Column C
5 6 =B1-B1=0
4 7 =B2-B1=1
3 8 =B3-B1=2
2 9 =B4-B1=3
1 10 =B5-B1=4

However the results of sorting are


Column A Column B Column C
5 6 =B1-B1=0
4 7 =B2-B3=-1
3 8 =B3-B5=-2
2 9 =B4-B7=9
1 10 =B5-B9=10

 

[Joel]

You have to convert the formula data to values to do the sorting. Copy the
column with the formula into a new column and PasteSpecial using Values. Then
sort on new column.

 

[Shane Devenshire]

Hi,

If you can modify the formula to read =B1-6 you can copy that down and sort
on it.

 

[Ashish Mathur]

Hi,

You may download and install the following add in
(http://www.download.com/Morefunc/3000-2077_4-10423159.html) and then use
the VSORT() array function

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Abrar]

I have downloaded the add in----How to use the Vsort function

 

[Ashish Mathur]

Once you download and install the addin, the help file would also get
downloaded. IN Excel 2003, navigate to Tools > More func > Help. There
read up on the VORT() function

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com