Help needed on sorting formula

[stuckagain22]

I am trying to get the following formula to alphabetically sort data:

=INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0))

Cell A1: pear
Cell A2: apple
Cell A3: orange

cells A1:A3 are named as: data

The formula above results in: #N/A

Could someone please help me out with this.

Thanks.


Thanks.

 

[venkat1926]

why not just sort in the worksheet or in vba.

see help in worksheet and vbeditor.

the macro in vba will be something like this

Sub test()
Range("data").Sort key1:=Range("data"), Order1:=xlAscending

End Sub

 

[Richard Schollar]

Hi

See this linke:

http://www.dailydoseofexcel.com/archives/2006/10/11/dynamic-text-sorting/

 

[stuckagain22]

why not just sort in the worksheet or in vba.

It has to be auto sorted -- this means the user of the sheet does not have to do anything... including using a macro.

See this linke:

http://www.dailydoseofexcel.com/archives/2006/10/11/dynamic-text-sorting/

Thanks, but that is where the formula is from... and it doesn't work.

 

[pgc01]

Hi stuckagain22

Thanks, but that is where the formula is from... and it doesn't work.

Yes it does. As it's said in the link this is a multi-cell array formula.
You have to follow the instructions exactly:

- Select the a number of cells (in the example C2:C21)
- type (or paste) the formula in the formula bar
- confirm with Ctrl+Shift+Enter (and not just Enter).

Hope this helps
PGC

 

[Peter_SSs]

edit: Woops, way too slow - didn't see your post pgc01

Thanks, but that is where the formula is from... and it doesn't work.

Try again, noting the first part of this instruction from the page:

Select C2:C21 and type (or paste) the formula below. Then press Ctrl+Shift+Enter (rather than Enter).