Carying Forward data with specific information
 

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


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

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

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

Regards
Sonar


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

Hi Morrigan

Can you still help?

Regads
Antoinette


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

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 Wrote:
Hi Morrigan

Can you still help?

Regads
Antoinette


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

Hi Morrigan

Not to worry, I already had help

Thanks

Regards
Sonar


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

I changed it, and your file is too big to upload it on here. Thus I
deleted your other sheets. You can copy and paste the formula on to
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 |
+-------------------------------------------------------------------+

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

Thank you Morrigan

Really appreciate the help

Sonar


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

Hi

How does this formula really work?

=IF(ISERROR(INDEX('13DBC'!$A$1:$W$999,SUMPRODUCT(S MALL(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<""),R OW()-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?


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

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('Stock
Req 13 BarCodes'!$10:$999)*('Stock Req 13
BarCodes'!$V$10:$V$999<""),ROW()-8+COUNTA('Stock Req 13
BarCodes'!$V$10:$V$999)-COUNTIF('Stock Req 13
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 'Stock
Req 13 BarCodes'!V10:V999 not equal "". In your case it will return
all the row numbers where V10:V999 are numbers. At position 983, we
have a value of 10 which is the row number that a number first appears
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('Stock
Req 13 BarCodes'!$V$10:$V$999)-COUNTIF('Stock Req 13
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 of
values that are greater than 0.

Therefore COUNTA()-COUNTIF() will always point at the position of the
last 0 in the array. However what you want is everything that comes
after that. Since the first line in your sheet SHORT-1 is row 9, using
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 which
is used in INDEX():

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


Hope that helps.


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