+ Reply to Thread
Results 1 to 11 of 11

Carying Forward data with specific information

  1. #1
    Registered User
    Join Date
    04-17-2004
    Posts
    39

    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

  2. #2
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    329
    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$5>0),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.



    Quote Originally Posted by 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
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-17-2004
    Posts
    39
    Thank you very much for the help, I will play around with the sample tonight.

    Regards
    Sonar

  4. #4
    Registered User
    Join Date
    04-17-2004
    Posts
    39
    Hi

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

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

    stocktest.zip

    Thanks
    Regards
    Sonar

  5. #5
    Registered User
    Join Date
    04-17-2004
    Posts
    39
    Hi Morrigan

    Can you still help?

    Regads
    Antoinette

  6. #6
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    329
    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.


    Quote Originally Posted by sonar
    Hi Morrigan

    Can you still help?

    Regads
    Antoinette

  7. #7
    Registered User
    Join Date
    04-17-2004
    Posts
    39
    Hi Morrigan

    Not to worry, I already had help

    Thanks

    Regards
    Sonar

  8. #8
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    329
    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.

    I also changed the formula in column V in sheet "Stock Req 13 BarCodes" slightly.

    From
    =IF($A10<>"",IF($T10<$S10,0,$T10-$S10),"")

    To
    =IF($A10<>"",IF($T10<$S10,"",$T10-$S10),"")


    Hope it helps.
    Attached Files Attached Files
    Last edited by Morrigan; 08-29-2005 at 06:37 PM.

  9. #9
    Registered User
    Join Date
    04-17-2004
    Posts
    39
    Thank you Morrigan

    Really appreciate the help

    Sonar

  10. #10
    Registered User
    Join Date
    04-17-2004
    Posts
    39
    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?

  11. #11
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    329
    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}

    The array is not sorted as what I have shown above, but it will contain 892 zeros.

    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. In ascending order, 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.
    Last edited by Morrigan; 09-09-2005 at 10:15 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1