Carying Forward data with specific information

[sonar]

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

 

[Morrigan]

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

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

I've also included a sample.

Hope it helps.

Hi

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

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

and

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


The thing is, I dont want it to leave gaps between the recorde
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
Sona

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

 

[sonar]

Thank you very much for the help, I will play around with the sampl
tonight.

Regards
Sona

 

[sonar]

Hi

Sorry, I had a look at the formulas and tried to reproduce it, but wit
no success. It might be because I dont fully understand what it'
functions do. I have included my file so that you can open it and hav
a look at it,

If you could help me with a formula to just paste the barcodes int
short, that has figures in the V column of 13digit barcode spreadshee
(without leaving the gaps and the #NUM errors when it reaches the las
one), I would appreciate it. I will then use vlookup/sumif to do th
rest. If that ok. I will try and take it from there.

'stocktest.zip' (http://www.epping.co.za/stocktest.zip)

Thanks
Regards
Sona

 

[sonar]

Hi Morrigan

Can you still help?

Regads
Antoinett

 

[Morrigan]

I have downloaded your file but have not had much time to look at it. I
do not have Excel at home and can only look at it at work. However work
is a little busy. I'll see what I can do.

 

[sonar]

Hi Morrigan

Not to worry, I already had help

Thanks

Regards
Sonar

 

[Morrigan]

I changed it, and your file is too big to upload it on here. Thus
deleted your other sheets. You can copy and paste the formula on t
your original workbook to try it.

Hope it helps

+-------------------------------------------------------------------
|Filename: WW STOCK RECIEVE CHECK2-R2.zip
|Download: http://www.excelforum.com/attachment.php?postid=3760
+-------------------------------------------------------------------

 

[sonar]

Thank you Morrigan

Really appreciate the help

Sonar

 

[sonar]

Hi

How does this formula really work?

=IF(ISERROR(INDEX('13DBC'!$A$1:$W$999,SUMPRODUCT(SMALL(ROW('13DBC'!$10:$999)*('13DBC'!$C$10:$C$999<>""),ROW()-9+COUNTA('13DBC'!$C$10:$C$999)-COUNTIF('13DBC'!$C$10:$C$999,">0"))),COLUMN()-1)),"",INDEX('13DBC'!$A$1:$W$999,SUMPRODUCT(SMALL(ROW('13DBC'!$10:$999)*('13DBC'!$C$10:$C$999<>""),ROW()-9+COUNTA('13DBC'!$C$10:$C$999)-COUNTIF('13DBC'!$C$10:$C$999,">0"))),COLUMN()-1))

I need to be able to manipulate the formula should I wish to reduce the
range from say 999 to 500.

I changing all the $999 to $500

and went to 13DBC worksheet, and deleted all the formulas from row 501
(this messed up the formula)

I see there is some kind of blue line that surrounds '13DBC'!
A09:S1001

It seems to be linked to make the INDEX formula work. I also tried
reducing that, and it messed up my formula's

What is the best procedure to reduce the range in the formula above and
in the area range in 13DBC?

 

[Morrigan]

I am not very good in explaining, but I will try.

In the sample that I gave you, I had:

INDEX('Stock Req 13 BarCodes'!$A$1:$W$999,SUMPRODUCT(SMALL(ROW('Stoc
Req 13 BarCodes'!$10:$999)*('Stock Req 1
BarCodes'!$V$10:$V$999<>""),ROW()-8+COUNTA('Stock Req 1
BarCodes'!$V$10:$V$999)-COUNTIF('Stock Req 1
BarCodes'!$V$10:$V$999,">0"))),COLUMN()-1)

SUMPRODUCT() will generate an array that contains 990 values:
{0 0 0 0...0 0 0 10 11 12 13 14 15 25 30}

As you can tell, SUMPRODUCT() returns all the row numbers where 'Stoc
Req 13 BarCodes'!V10:V999 not equal "". In your case it will retur
all the row numbers where V10:V999 are numbers. At position 983, w
have a value of 10 which is the row number that a number first appear
in V10:V999. Position 984 contains the next and so on.

Here I used SMALL() to output these values. It looks something like:

SMALL({0 0 0 0...0 0 0 10 11 12 13 14 15 25 30},ROW()-8+COUNTA('Stoc
Req 13 BarCodes'!$V$10:$V$999)-COUNTIF('Stock Req 1
BarCodes'!$V$10:$V$999,">0"))

COUNTA() will return 990 which is your total array length.
COUNTIF() will return a number(8 in this case) which is number o
values that are greater than 0.

Therefore COUNTA()-COUNTIF() will always point at the position of th
last 0 in the array. However what you want is everything that come
after that. Since the first line in your sheet SHORT-1 is row 9, usin
ROW()-8 will generate 1,2,3,4,etc as you drag the formula down.

Now everything is simple, the functions above return a row number whic
is used in INDEX():

INDEX(range, row number from above functions, column number)


Hope that helps