Sorting with underlying formulas.

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 *** 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 <at>yahoo<dot>com
----
"Jay Fincannon" <[email protected]> wrote in message
news:[email protected]...
> 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 *** 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 <at>yahoo<dot>com
----
"Jay Fincannon" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
----

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" <[email protected]> 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 <at>yahoo<dot>com
----

Almost; Kennesaw, GA

>"Jay Fincannon" <[email protected]> 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 <at>yahoo<dot>com
----