Help needed with Adapting complex INDEX formula
 

Hi, I was given this formula,

=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$50),ROW()-1+COUNTBLANK('Stock Req 3-8 Digit
BarCodes'!$W$2:$W$5))),COLUMN())

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

Regards
Antoinette


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399608

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" wrote in
message ...

Hi, I was given this formula,

=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$50),ROW()-1+COUNTBLANK('Stock Req 3-8 Digit
BarCodes'!$W$2:$W$5))),COLUMN())

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

Regards
Antoinette


--
sonar
------------------------------------------------------------------------
sonar's Profile:
http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399608

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$50),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$40),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


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399608