Hi!

Currently a table of data is transfered to a sheet using
(INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$2 0=$C$2,ROW($1:$20)),ROW(1:1))

As written that formula will not work properly. You'll either get errors or
possibly incorrect results.

The size of the array that is indexed: sheet1!$C$2:$C$20

Must be the same size as: ROW($1:$20)

sheet1!$C$2:$C$20 = 19
ROW($1:$20) = 20

The "least" confusing way to make sure these arrays are the same size is to
use the same sized range reference in the ROW function as you do the INDEX
and then subtract the offset:

ROW(C$2:C$20)-ROW(C$2)+1

The purpose of the expression: ROW(C$2:C$20)-ROW(C$2)+1

is to return an array that is the same size as the indexed array

So:

sheet1!$C$2:$C$20 = 19 (1:19)
ROW(C$2:C$20)-ROW(C$2)+1 = 19 (1:19)

Ok, now, as far as your latest question goes, I'm not following you!!!!

I don't understand what you're trying to do!

Biff

"GarToms" wrote in message
...

Biff and all,

I wonder if you could assist me further with something using the
formula you suggested.

Currently a table of data is transfered to a sheet using
(INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$2 0=$C$2,ROW($1:$20)),ROW(1:1))

The rows search the source data in sheet 1 by a key (column A) and
display a name (column B), a value (column C) and a digit in column D
decides under where the value (column C) will go. The column C value
needs to go under column E, F, G, H, I, and to make it more complecated
these column headings are formulas and change when the data is updated.

The table transfers all the data but i cannot make it sort the value in
column C to be in the correct column in my table.

Anyone have any ideas? I was thinking there may be an if statement or
something i could use.

I would be very great full for any assistance with this.


--
GarToms