I want to sort a column based on the displayed data not the underlying
formula. Can this be done?

cell formula =VLOOKUP(A1,RteTable,2,False)
The resulting cell display would be either North, South, East, West
or OSA

Jay

Think it's not possible unless you kill the formulas in the col
with an in-place: copy paste special values OK

Perhaps try a workaround to get the sorted list up
dynamically in another sheet(s) ?

Assume a sample table below
in Sheet1, cols A and B,
data from row1 down

1 North
2 South
5 OSA
4 West
3 East

where col B contains your formula, i.e.:
In B1 is : =VLOOKUP(A1,RteTable,2,FALSE), copied down

Use an empty column to the right, say col D?

Put in D1: =IF(A1="","",CODE(LEFT(B1,1))+ROW()/10^10)

Copy D1 down to say D100, to cover the max expected
number of rows of data in cols A and B
(can copy down ahead of expected data input in col A)

Col D will function as an arbitrary "alpha-numbering cum tie-breaker"
criteria column to enable extraction of the sort by col B in the other
sheet(s)

In Sheet2
-------------

Put in A1:

=IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)))

Copy A1 across to B1, fill down to B100, i.e. by the same number of rows
that was catered for in Sheet1

For the sample data in Sheet1, you'll get the table below,
sorted in ascending order by col B in Sheet1:

3 East
1 North
5 OSA
2 South
4 West

And if you want to sort in descending order, just change the SMALL in the
formula to LARGE, viz., put instead in A1:
(you could try this in another Sheet3, for example)

=IF(ISERROR(LARGE(Sheet1!$D:$D,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)))

Then just copy across and fill down as before

You'll get (in Sheet3):

4 West
2 South
5 OSA
1 North
3 East
< rest are blanks: ""

The sorted lists in Sheets 2 and 3 will change accordingly depending on the
results returned in col B in the source table in Sheet1
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jay Fincannon" wrote in message
...
I want to sort a column based on the displayed data not the underlying
formula. Can this be done?

cell formula =VLOOKUP(A1,RteTable,2,False)
The resulting cell display would be either North, South, East, West
or OSA

Jay

Thanks Max. Where's that Jambi?

Think it's not possible unless you kill the formulas in the col
with an in-place: copy paste special values OK

Perhaps try a workaround to get the sorted list up
dynamically in another sheet(s) ?

Assume a sample table below
in Sheet1, cols A and B,
data from row1 down

1 North
2 South
5 OSA
4 West
3 East

where col B contains your formula, i.e.:
In B1 is : =VLOOKUP(A1,RteTable,2,FALSE), copied down

Use an empty column to the right, say col D?

Put in D1: =IF(A1="","",CODE(LEFT(B1,1))+ROW()/10^10)

Copy D1 down to say D100, to cover the max expected
number of rows of data in cols A and B
(can copy down ahead of expected data input in col A)

Col D will function as an arbitrary "alpha-numbering cum tie-breaker"
criteria column to enable extraction of the sort by col B in the other
sheet(s)

In Sheet2
-------------

Put in A1:

=IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"" ,INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)) )

Copy A1 across to B1, fill down to B100, i.e. by the same number of rows
that was catered for in Sheet1

For the sample data in Sheet1, you'll get the table below,
sorted in ascending order by col B in Sheet1:

3 East
1 North
5 OSA
2 South
4 West

And if you want to sort in descending order, just change the SMALL in the
formula to LARGE, viz., put instead in A1:
(you could try this in another Sheet3, for example)

=IF(ISERROR(LARGE(Sheet1!$D:$D,ROWS($A$1:A1))),"" ,INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)) )

Then just copy across and fill down as before

You'll get (in Sheet3):

4 West
2 South
5 OSA
1 North
3 East
< rest are blanks: ""

The sorted lists in Sheets 2 and 3 will change accordingly depending on the
results returned in col B in the source table in Sheet1

You're welcome, Jay !

btw, what's Where's that Jambi?

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jay Fincannon" wrote in message
...
Thanks Max. Where's that Jambi?

btw, what's Where's that Jambi?

one guess .. :
1° 22' N 103° 45' E = Singapore

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

I mistakenly looked at 1*22' S instead of N

Jay
GMT -5 34°0N 84°38'W


btw, what's Where's that Jambi?

one guess .. :
1° 22' N 103° 45' E = Singapore

"Jay Fincannon" wrote :
....
GMT -5 34°0N 84°38'W

Near Dalton, Georgia, USA ?

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Almost; Kennesaw, GA

"Jay Fincannon" wrote :
...
GMT -5 34°0N 84°38'W

Near Dalton, Georgia, USA ?

Almost; Kennesaw, GA
GMT -5 34°0N 84°38'W

Thanks, the guess wasn't that far off then <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----