Assuming row 1 is header, in sheet "Short":

A2 = INDEX('Stock Req 3-8 Digit
BarCodes'!$A$1:$W$5,SUMPRODUCT(SMALL(ROW('Stock Req 3-8 Digit
BarCodes'!$2:$5)*('Stock Req 3-8 Digit
BarCodes'!$V$2:$V$50),ROW()-1+COUNTBLANK('Stock Req 3-8 Digit
BarCodes'!$V$2:$V$5))),COLUMN()) (Copy across and down)

I've also included a sample.

Hope it helps.



sonar Wrote:
Hi

I need to carry stock over from one sheet to another sheet in the same
file.

eg.
s/sheet "Stock Req 3-8 Digit BarCodes" , column A, that has quantities
in column V

and

s/sheet "Stock Req 3-8 Digit BarCodes" , column A, that has quantities
in column W


The thing is, I dont want it to leave gaps between the recorded
information. e.g

http://www.epping.co.za/excelhelpneeded.html

I am sure this must be a simple thing for you guys,
Help on this will be much appreciated.

Regards
Sonar


+-------------------------------------------------------------------+
|Filename: Stocks.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3738 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=398757