Results 1 to 6 of 6

Thread: Need help with my stock reports

  1. #1

    Need help with my stock reports



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

    Hi,


    My name is Emile and I need some help with a problem I have with a stock report.

    I receive a stock report every week from a customer, I would like to make an overview on a weekly basis of the stock changes.

    It's a list of more than 100 products every week. I tried to do a If function, vlookup and Index but it either doesn't work or it's not what I'm looking for.

    If someone could help me and check out the attached file, the problem will become much clearer to you and might help me out with this obstacle.

    I really appreciate the help

    Many Thanks!!
    Attached Files Attached Files

  2. #2
    Conjurer Beamer's Avatar
    Join Date
    Nov 2014
    Posts
    109
    Articles
    0
    Excel Version
    365
    Hi Emile, I'm not sure this is the best way to do this, I think VBA might be better but that's not my forte
    But does this sheet show the results you are looking for?
    The formula in the Helper column A would need to be copied down for the same number of rows that are in the data sheet, even though column B will be shorter (it only lists a product number once).
    The formulas in columns B to G would need to be copied down according to the number of products (each new product will need a new line).
    Then each week just copy the previous column and change the date. e.g. copy all of column G to H and change the date to 201505.

    Actually, if you had a complete product list you could just put that into column B and forget column A altogether, but if not then column A will help column B build a product list on the fly.
    Attached Files Attached Files

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    Just throw it at a pivot table, it couldn't be easier.

  4. #4
    Conjurer Beamer's Avatar
    Join Date
    Nov 2014
    Posts
    109
    Articles
    0
    Excel Version
    365
    Wow Bob, I've seen people mention pivot tables before but I've never used one (or had a clue how).
    After your post I decided to give it a try, 2 minutes later I had a sortable table with totals showing for the weeks and the products.
    They are amazingly easy. Thank you for the suggestion, I'll be sure to use them again in the future.

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    They could be better Beamer, but they are pretty good, and as you have found, very easy to setup. Let's hope EmilEA has the same success

  6. #6
    Quote Originally Posted by Bob Phillips View Post
    They could be better Beamer, but they are pretty good, and as you have found, very easy to setup. Let's hope EmilEA has the same success

    Indeed I later found out I could use a pivot table, I was thinking way to hard on this once, anyway, thanks guys!

Tags for this Thread

Posting Permissions

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