+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

Inventory Formula in 3 columns

  1. #1
    Registered User
    Join Date
    11-05-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Question Inventory Formula in 3 columns

    Hey guys. I keep a list of inventory that comes through my area and track it. Everyday in column C, i put the date. Column A gets the part numbers and column B gets a value of the parts received. For example, Column A Can say "Bucket #2233" column B will say "200" and column C in the first row of the day will get the date, "11-5-19". After the first row of the day, i'll put all the daily values. After the last value for the next day, will start a new row with a new date. This list has been running and we're into the 2000's row by now.

    So what i need is to have a separate list (would it need to be in the excel sheet?) of incoming parts i'm waiting on. When i log those parts in the excel sheet to see what we brought in for the day, i'd like the "match" parts to be highlighted so i can then move the parts to the proper department. I have a separate area with bins that these parts go into but the problem is if i receive in the parts and don't check the bins daily, the parts get put away on a shelf and the bins will stay until i get lucky and remember i received them in.

    So how do i make a list to match column A (column B is irrelevant for my needs) and have it reset to the next day's inventory. I will remove parts from the list of "incoming" parts as i fill the bins so they don't ding again. Does any of this make sense... hopefully. Lol. It's way too complicated for my excel knowledge. Thanks!
    Last edited by NotGoodAtExcel2019; 11-08-2019 at 02:34 PM.

  2. #2
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    8,650

    Re: Inventory Formula in 3 columns

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    You really don't have to give me rep points for moderating. It's part of the job. Keep rep points for those who find solutions

  3. #3
    Registered User
    Join Date
    11-05-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Inventory Formula in 3 columns

    I'd like Column "E" to contain a list of items that i'm waiting to come in and cross reference between Column A. But i need the formula to start new everyday (or if someone has something better please say). I'd like it to somehow alert me that the items in incoming match the items in column A. But i'd also like column A to freeze if that's possible so i can keep the list where i'm working. It's a january 2019 to december 2019, etc. list so the rows are always growing.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-05-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Inventory Formula in 3 columns

    Is this not possible?

  5. #5
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,009

    Re: Inventory Formula in 3 columns

    Hi -

    I'm not sure I fully understand what you're doing. In your example, you have Pin 001 that came in on 11/05 and Markers 055 that came in on 11/06. But you want the list to restart every day? Those items came in on two different days, so I don't understand what you mean by restart the list each day.

    How are you doing this? Columns A, B, and C are what you recevied each day. Column E is what you're waiting on, but how do you know what you're waiting on? Is there a list of orders?
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  6. #6
    Registered User
    Join Date
    11-05-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9
    Quote Originally Posted by loginjmor View Post
    Hi -

    I'm not sure I fully understand what you're doing. In your example, you have Pin 001 that came in on 11/05 and Markers 055 that came in on 11/06. But you want the list to restart every day? Those items came in on two different days, so I don't understand what you mean by restart the list each day.

    How are you doing this? Columns A, B, and C are what you recevied each day. Column E is what you're waiting on, but how do you know what you're waiting on? Is there a list of orders?
    So the way my job works is this.

    My receiving department gets in the product which i then get and log in in the excel sheet. I have bins i has bins i have to fill with the product whether it be hardware, e.t.c but sometimes we run out. The parts we're out of, the bins go on a shelf and wait for more to come in. If i forget about these bins they may go unfilled because when i receive products, i log them in and put them away. Where what i want to do would come into play is when i log in the parts for the day, i want a list of the empty bins on the shelf so when logged in, the excel sheet will let me know whether it be with colored words, check marks, something. Then ill delete the product out of the list. The reason i want it to restart everyday or even every week is because we get the same (say 80) products in monthly and there may be the same product numbers which will "ding" as in. If you follow lol

  7. #7
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    8,650

    Re: Inventory Formula in 3 columns

    Administrative Note:

    Please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.
    Use the "Quick reply" instead
    Thanks

  8. #8
    Registered User
    Join Date
    11-05-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Inventory Formula in 3 columns

    What i'd ultimately like the function to do is to let me know (whether it be highlighted check mark, etc) when a value (the part number) i entered for the day (whatever day that may be) comes up in a list that i keep of parts i'm waiting on. But I'm currently in row 1853 of my excel sheet so i'd like to either have it reference another sheet or preferably on the same sheet but somehow pinned so it can move with where i'm working instead of having to scroll up and find it. The date is irrelevant if the formula can search for the "month and year" or something? What do you guys think?

  9. #9
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,009

    Re: Inventory Formula in 3 columns

    OK - I have a couple of thoughts on how to do this. I'm a little swamped today, so I'm not sure I will get it finished, but I should have something back tomorrow.

  10. #10
    Registered User
    Join Date
    11-05-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Inventory Formula in 3 columns

    awesome! I'll keep checking back. I appreciate all the help.

  11. #11
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,009

    Re: Inventory Formula in 3 columns

    Hi -

    Here is a solution that would be how I would do it. You may have a different opinion since you work with this inventory list every day. But, here goes:

    First, I created two new sheets in your workbook: One sheet has your list of all the parts you normally receive every month. A Master Parts list. I made it 80 entries long. You can adjust it to your actual list. I also set it up on your main page that you can either type in the part name OR select the part name from a drop down list that is populated from your Master Parts List using a Data Validation rule. This will help you enter part names the same way every time. If you misspell a part, you will get an error message that says that part isn't on the list, and to check spelling.

    The next sheet I added is just a worksheet for Excel. You won't every enter data here or edit anything here. This sheet simply consolidates only the parts in the Master List that are out of stock (in other words, the bin is empty and you put the bin wherever you put them when they're empty). This spreadsheet just makes searches for Out of Stock items much simpler on your main sheet.

    Finally, I made some changes to the main sheet: Based on the format you provided in your original spreadsheet, you provide one date and then have a line for each part that comes in on that day. I added Column D that copies the date for each part, so each part has the corresponding receipt date next to it. Again, this is to simplify searching. I also added Column E that compares your Received Parts List to the Out of Stock list. If a received part is out of stock AND the date you received it is AFTER the date it went out of stock, then the text for that part, on your Main Sheet will turn red. That's your signal to refill that bin.

    So here's how you would use this workbook:

    When a bin runs out, and you don't have inventory to replace it, go to your Master Parts List, and put an "X" in the Out of Stock column for that part. ALSO make SURE you enter the date that the bin ran out. When you finally enter the receipt of the Out of Stock item, the text for that part will turn red, as I said before. That is your signal to do 2 things: Refill the bin AND delete the "X" and the date for that part from the Master Parts List. Notice, you never do anything on the Worksheet. That's just for Excel to use. However, you can look at it and get a very quick picture of all the items that are out of stock and when they went out of stock.

    Last, this is just kind of a bonus, I set a filter on all the data on the main "RECEIVED INVENTORY" sheet. You can tell it's filtered because there are little down arrows on the headings. If you click the down arrow on say Column D (Part Rec'd Date), uncheck the "Select All" check box, and then click "November 7" check box, you can get a list of just the parts that came in on November 7. Just go back and check "Select All" to restore the whole list. Likewise, you can click the down arrow on Column A (Part), uncheck Select All, and then check "Part 9" it will show you that Part 9 was received twice and you can see the dates. This is just a handy feature using Filters.

    The way I got the text to turn red is I used Conditional Formatting with the Rule comparing Column E (Matches Criteria) to 1 or 0. So column E checks to see IF a part is out of stock AND the date received is AFTER it went out of stock then the text will turn red.

    There are several array formulas (you can tell the array formulas because they are enclosed in curly braces { } ) in this spreadsheet to make all of this work.

    Try it out. Play with adding parts, putting different dates in, deleting the X and date from the Master Parts List. Just don't do any editing on the Worksheet.

    I have attached the modified spreadsheet.

    If you're not familiar with these techniques, such as Data Validation, Conditional Formatting, Array formulas, you should google them. The first two are pretty easy to learn. Array formulas....they are easy to use, but can be tricky to design and write. Mostly, all you need to know about them is if you ever edit one, you can't just hit enter. You have to simultaneously press ctrl-shift-enter to engage the array functionality. If you did it right, Excel will automatically enclose the formula in the curly braces I mentioned before.

    Hope this helps.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-05-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Inventory Formula in 3 columns

    Is there a simple way to reference another worksheet (out of stock parts) to column A of my current inventory worksheet (named 2019) so that when i list items in the "out of stock parts" tab it will turn them red in the "2019" worksheet when i list an item that came in. For example, in my out of stock parts worksheet i can list pins004, pins005 and pins006. When i list pins006 on my 2019 worksheet it will turn red letting me know we've been out of stock in it and i can erase it from my out of stock parts tab? I think what you made is well beyond my comprehension of excel lol! That's pretty amazing that you whipped that up though, i will continue to play with it for future use. My column A Is "part", B is "amount", C is "date" and D is just notes. I'm only worried about column A. You showed me that with a separate worksheet you can still have it checking another worksheet, so my original idea of needing a list to be on that current 2019 worksheet isn't needed. nor is the date function anymore (i guess i was trying to explain it too complex). So do you think that the way i explain now with seeing your example is easy? I'm now down to the 1867 row in my column A, and it will be growing until the end of december, when i will start a new worksheet for "2020". Is there a way to make "out of stock" worksheet reference the entire worksheet of "2019" for the words in it? I don't mind if the previous months are red as i'll only be looking at current days. Thanks again for your help!

  13. #13
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,009

    Re: Inventory Formula in 3 columns

    Hi -

    OK - attached is a much simpler version that just checks the list of Out of Stock items and uses conditional formatting to turn that text red. Understand since we are not looking at dates, that all instances of that item in your 2019 list will turn red. For example, let's say you received Pins 001 in January, March, July, and again in November. Let's say you ran out of those in October. Once you put them on the Out of Stock List, since we're not checking dates, all of those received instances of Pins 001 (January, March, July, and November will be red). If you're OK with that, then we're good. Also, you have to type in the part exactly correct. If you misspell a part, then it won't turn red because Excel is checking the list for, say Pins 001. If you accidentally type in Pins001, that won't match, and won't turn red.

    Switching to a new year shouldn't be a problem. On December 31, 2019, Enter your last items for the year and save the file. If any out of stock items came in that day, then refill the bins and take them off the Out Of Stock list. Then do a SaveAs and create a copy to use for 2020. Whaterver is on your Out of Stock list will still be there, so we don't need to check 2019 because if you closed out the year and those parts weren't received then you still need them in 2020. So don't worry about that.

    Attached is the updated file.

    Hope this helps.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    11-05-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Inventory Formula in 3 columns

    That works perfect, but how do i integrrate the "out of stock" sheet into my current inventory list?

  15. #15
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,009

    Re: Inventory Formula in 3 columns

    The simplest way would be to copy the contents of your inventory spreadsheet (columns A through D) and then do a Paste Values into my spreadsheet on the tab called Current Inventory. Make sure you use Paste Values and not just paste, or you will overwrite the Conditional formatting. Then simply enter your Out of Stock items on that tab. Save the file as 2019 Inventory and you're done. When 2020 comes around, just follow the procedure I listed in my last post.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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