Hi,

Okay, this is a strange one. I have a worksheet that uses an array
formula to sort values in another sheet and retrieve them in their
sorted order. So far, so good.

The weird thing is, this works fine on computers in our offices
everywhere but in Germany, where it breaks. No one can seem to figure
this out.

Does anyone have any idea how this might be happening, aside from
individual computers being screwed up?

Thanks...

....Jay

Post the formula?


--


Regards,


Peo Sjoblom




"Jay Weiss" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Okay, this is a strange one. I have a worksheet that uses an array
> formula to sort values in another sheet and retrieve them in their
> sorted order. So far, so good.
>
> The weird thing is, this works fine on computers in our offices
> everywhere but in Germany, where it breaks. No one can seem to figure
> this out.
>
> Does anyone have any idea how this might be happening, aside from
> individual computers being screwed up?
>
> Thanks...
>
> ...Jay
>

Hi Peo,

The contents of the array formula are as follows:
=IF(VALUE(MID(TRANSPOSE(LARGE(VALUE(Exec_Code),COLUMN(INDIRECT("1:"&COLUMNS(Exec_Code))))),
2,2))>0,
VALUE(TRANSPOSE(LARGE(VALUE(Exec_Code),COLUMN(INDIRECT("1:"&COLUMNS(Exec_Code)))))),"")

Exec_Code is a named range for a row in which every cell contains a
five-digit value.

Thanks for your (and anyone else's) help!

....Jay

That formula in German is

=WENN(WERT(TEIL(MTRANS(KGRÖSSTE(WERT(Exec_Code);SPALTE(INDIREKT("1:"&SPALTEN(Exec_Code)))));2;2))>0;WERT(MTRANS(KGRÖSSTE(WERT(Exec_Code);SPALTE(INDIREKT("1:"&SPALTEN(Exec_Code))))));"")


Tell them to check if that works and also when you say breaks what does it
mean?


Here is an alternative where you array enter the formula in a single cell
and copy it down until you get blank cells

=IF(ROWS(A$1:A1)<=SUM(N(Exec_Code<>"")),INDEX(Exec_Code,MATCH(LARGE(COUNTIF(Exec_Code,"<="&Exec_Code),
SUM(N(Exec_Code=""))+ROWS($A$1:A1)),COUNTIF(Exec_Code,"<="&Exec_Code),0)),"")

will sort NUMERIC values in descending order so if your values in Exce_Code
are text numbers it will fail



--


Regards,


Peo Sjoblom






"Jay Weiss" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Peo,
>
> The contents of the array formula are as follows:
> =IF(VALUE(MID(TRANSPOSE(LARGE(VALUE(Exec_Code),COLUMN(INDIRECT("1:"&COLUMNS(Exec_Code))))),
> 2,2))>0,
> VALUE(TRANSPOSE(LARGE(VALUE(Exec_Code),COLUMN(INDIRECT("1:"&COLUMNS(Exec_Code)))))),"")
>
> Exec_Code is a named range for a row in which every cell contains a
> five-digit value.
>
> Thanks for your (and anyone else's) help!
>
> ...Jay
>

> =IF(ROWS(A$1:A1)<=SUM(N(Exec_Code<>"")),INDEX(Exec_Code,MATCH(LARGE(COUNTIF(Exec_Code,"<="&Exec_Code),
> SUM(N(Exec_Code=""))+ROWS($A$1:A1)),COUNTIF(Exec_Code,"<="&Exec_Code),0)),"")
>
> will sort NUMERIC values in descending order so if your values in
> Exce_Code are text numbers it will fail


Actually it will sort text as well (abc, gft etc) unless they are text
numbers


Peo