Help needed with Adapting complex INDEX formula

[sonar]

Hi, I was given this formula,

=INDEX('Stock Req 3-8 Digi
BarCodes'!$B$1:$X$5,SUMPRODUCT(SMALL(ROW('Stock Req 3-8 Digi
BarCodes'!$2:$5)*('Stock Req 3-8 Digi
BarCodes'!$W$2:$W$5>0),ROW()-1+COUNTBLANK('Stock Req 3-8 Digi
BarCodes'!$W$2:$W$5))),COLUMN())

I need to know what part of this formula makes it impossible to jus
use this in any cell. For instance, I can use it in A2, but if I us
it in say B9 of my worksheet "short", it gives me the #NUM / #REF
error. How should I go about modifying it?

Regards
Antoinett

 

[Biff]

Hi!

The errors are being generated by the ROW() and COLUMN() functions.

When these functions don't contain any arguments they become row and column
specific as to the row and/or column that the formula is entered in. So,
when you copy or enter this formula in other cells that changes the argument
to those functions and as a result those references are probably outside of
your table range Stock Req 3-8 Digit BarCodes!$B$1:$X$5.

Man, I hate long sheet names! <g>

Biff

 

[sonar]

Hi, I have played around with this code, but I just cant seem to make
this thing work, I really feel dof, even after I have gone to look at
what row, column and small function means to get an idea,

If I take this code:

=INDEX('Stock Req 3-8 Digit
BarCodes'!$B$1:$X$5,SUMPRODUCT(SMALL(ROW('Stock Req 3-8 Digit
BarCodes'!$2:$5)*('Stock Req 3-8 Digit
BarCodes'!$W$2:$W$5>0),ROW()-1+COUNTBLANK('Stock Req 3-8 Digit
BarCodes'!$W$2:$W$5))),COLUMN())

I changed it to:

=INDEX('8DBC'!$A$1:$W$4,SUMPRODUCT(SMALL(ROW('8DBC'!$1:$4)*('8DBC'!$V$1:$V$4>0),ROW()-1+COUNTBLANK('8DBC'!$V$1:$V$4))),COLUMN())


If my information is in
'8DBC', A10:A999

and I want this information to display in:
'SHORT-1' A10:A999

What will this code look like then?

Regards
Sonar