Sorting results from the Index command

[Anderson]

Have you ever
seen a problem where the column generated by the index command will not sort
as
expected.
=IF(INDEX(PAT_Owner, ROW($B11)) ="Mfg", INDEX(Brickchart_Name,ROW($B11)), "")

Column A Column B
Engineering (Header) Manufacturing (Header)
3DCS Analyst
3DCSi
AA
Acrobat 3D
ADVISE
AutoSMP
Bargain
Basement
BSML
CAD
CATIAV5
CATIAV5

When i try to sort on only column A, it does not sort out the empty
cells. It is as if it is sorting the column from which this data was
generated. I want the sort to remove to the empty cells in each column so
the results look like this:

Column A Column B
Engineering (Header) Manufacturing (Header)
3DCS Analyst AA
3DCSi Bargain
Acrobat 3D Basement
ADVISE CAD
AutoSMP
BSML
CATIAV5
CATIAV5

Any thoughts on why this would be happening?
Anderson

 

[vezerid]

Anderson,

I am not sure what you want to do, but this way of accessing a cell is
more complicated than necessary. Assume that your MyName range is in
G11:G30. Then the following formula is accessing G21:

=INDEX(MyName,ROW($B11))

But the way you are using your formula you could have avoided INDEX by
using directly the cell reference, as in:

=IF(Sheet1!G23="Mfg",Sheet2!K33,"")

Here we are assuming that Brickchart_Name is in column Sheet2!K:K and
starts 10 rows below PAT_Owner, which is in column Sheet1!G:G. The
formula will do exactly what your INDEX does. Furthermore, it will
sort. The problem here has to do with the ROW you are using to get the
number.

HTH
Kostis Vezerides