Results 1 to 6 of 6

Thread: Inventory

  1. #1

    Inventory



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Its an Inventory Sheet

    Sheet:Opening --> Manual Entry Done by the user
    Sheet:Purchase --> Manual Entry Done by the user
    Sheet:Sales --> Manual Entry Done by the user
    Sheet:Order --> Manual Entry Done by the user
    Sheet:Master --> Updates all sheets by SUMIF formula
    Now in,
    Sheet:Report -->
    1. I need a report to display those Stock items,if Total Stock <>0
    or
    2. I need a report to display those Sold items,if Total Sales <>0
    or
    3. I need a report to display those pending items,if Total Pending <>0 etc
    when selected Purchase/Sales/Order/Pending/Stock respectively at "I2"



    Sheet: Report2 -->
    1. I need a report to display the entries between two dates (for Purchase, Sales, Order) as a single report

    {When array formula copied from a first row to endrow, the excel sheet becomes very slow.
    Thus if it is in macro, it would be good}
    Note: All Sheets are locked without password (Alt+TPP)
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    It can be done without array formulas, applying some helper formulas to the reference sheets.

    E.g. In Master1 sheet, K5 add formula:

    =IF(INDEX($E5:$J5,MATCH(Report!$I$2,$E$4:$J$4,0))>0,COUNT(K$4:K4)+1,"")

    copied down as far as you want. You can hide column if desired.

    In Purchase1, Sales1, Order1 sheets, G6 use formula:

    =IF(AND($D$3=Report2!$I$2,Report2!$H$2=B6),COUNT(G$5:G5)+1,"")

    copied down as far as you want. Also hide column if desired.

    Now in Report sheet, C6 use formula:
    =IFERROR(INDEX(Master1!B:B,MATCH(ROWS(A$6:A6),Master1!$K:$K,0)),"")
    copied down and across as far as necessary.
    Note: You may need to format columns to match formats of original report(s).

    In Report2 sheet, B6 enter formula:
    =IFERROR(INDEX(INDIRECT("'"&$I$2&"1'!b:b"),MATCH(ROWS(B$6:B6),INDIRECT("'"&$I$2&"1'!G:G"),0)),"")
    copied across the columns. Then change the b:b part to match the column of interest in the Sales, Purchasing, Order sheets.... Then copy all down.

    Again, you may need to format to match reference sheets.

    Sample attached.
    Attached Files Attached Files
    Last edited by NBVC; 2014-12-18 at 12:37 PM.


  3. #3
    Quote Originally Posted by NBVC View Post
    It can be done without array formulas, applying some helper formulas to the reference sheets.

    ....

    Sample attached.
    No sample mate!

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Oops. Thanks. I will attach when I am back at the office (in about 10 hours)

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    as promised, I have attached it now. (I hadn't noticed it originally exceeded the forum limit for size)... had to zip it.
    Last edited by NBVC; 2014-12-18 at 12:37 PM.


  6. #6
    Thank you NBVC
    due to busy schedule couldn't EXCELGURU
    yeah it was a great a job but,
    in "REPORT" sheet
    if i select at I2,
    the QTY column displays "0"
    Actually,
    if i select REPORT!I2 as "Sales" --> QTY should display from MASTER!G:G, for only those values of MASTER!G:G>0
    if i select REPORT!I2 as "Purchase" --> Qty should display from MASTER!F:F, for only those values of MASTER!F:F>0
    if i select REPORT!I2 as "Order" --> Qty should display from MASTER!I:I, for only those values of MASTER!I:I>0
    if i select REPORT!I2 as "Pending" --> Qty should display from MASTER!J:J, for only those values of MASTER!J:J>0
    if i select REPORT!I2 as "Stock" --> Qty should display from MASTER!H:H, for only those values of MASTER!H:H>0

    and their correspong "code" "Desc", "MRP" and "AMt"
    Thank you once again for your contribution

Posting Permissions

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