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!

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.

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?

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

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.

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.

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.

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.

Great! Glad I could help.