Click here for important information about Computing.net.
I am trying to create an "automated" excel inventory system out of an outdated manual entry inventory spreadsheet. This is for a low budget collegiate lab, so I am trying to see if it is possible to get the features we need in excel without buying a program. We test chemicals so as of now we have an excel file that has the product codes with reaction comments and a drop-down list that marks the location or if an action needs to be taken. I see inventory videos for conditional formatting, barcode inventory, dependent variables, and Vlook, but not how to combine them effectively.
There are about 1000 miss marked chemicals currently. I am trying to make dropdown menus functions of what-if statements. The [location] box is a function of [quantity].
1 - If the [quantity] box is 1 or greater it needs to produce one drop-down menu.
2 - If the [quantity] box is 0 it needs to say reorder or high light red.
3 - If the [quantity] box reads N/a it needs to produce a different drop-down list of options.The [quantity] box is a function of bar codes scanned in and out. This is where it is especially tricky because the inventory can only have each product code appear once and dates matter for expiration.
So I do not know if checking in and out must be done on a separate spreadsheet where duplicate inputs from barcode from the scanner are allowed. Preferably once a checked-in bar code with the date (x) is checked out the row deletes its self unless the quantity is zero.
If the quantity is zero the pair of codes do not delete until the same bar code is checked in again on date (y). The deleting of old codes can be done manually worst case. The important part is that the code checked out matches to the oldest matching code int the [check in box] with an empty [check out] cell.
Any help or guidance would be greatly appreciated! Thanks!
I hope you don't mind but I edited your post to improve it's readability. Putting all that information and criteria into one long paragraph makes it difficult (at least for me) to understand what you are trying to do. That said, I think the Dependent Drop Downs can be addressed fairly easily. However, when you use the terms "checking in" and "checking out", you lose me.
Please keep in mind that we can't see your workbook from where we are sitting, nor do we know anything about your work processes. You need to explain to us what "checking in" and "checking out" means, or more specifically, what it means as far as how you use the workbook. What "operation" do you perform to check something in/out? Any terms that are not specific to Excel are not going to make much sense to us.
I think we need some more information related to everything after #3 above. For example, you mentioned deleting rows, but you didn't say which sheet they should be deleted from. Before we can proceed, we'll need more information about your workbook layout and how you use the workbook.
How To Post Data or Code ---> Click Here Before Posting Data or VBA Code
I figure out most of my requests had to be done with VBA. After the VBA portion is complete the rest will be simple. I want two macro buttons on one sheet. "check-in" and "check-out" A barcode scanner will input product codes one by one into a table on the "inventory" Worksheet. This worksheet at the moment is named"check_in" in my code. When I click either the check-in button or the check-out button that clicked function will apply to the list of product codes on the inventory sheet then be cleared.
We can not have duplicates of the same product code, but we must track quantity and date.
Right now I have the check-in code running but it does not track multiple dates, which is needed for expiration.How I set up this function=== once the product code list is finished being scanned in, on the "Check-in" worksheet and the "check-in' button is pressed, the first cell in the table is read and "today's" date is recorded a few columns over. After that, the matching product code is found in the "Library" worksheet. If there is no match then the product code is added to the bottom of the "Library" worksheet. In the same row, it adds one to the "quantity", and the day's date in the "date received" column next to that.
(here's where it messes up)
I need to keep multiple dates in one cell so I used three columns to make a string of dates. [complete list], [todays date], [last recorded date] in that orderif the product code from the "Check_ in" worksheet is found on the "library" worksheet then the "date received" column is copied from the "library worksheet" to the Check_in" worksheet. Then the Complete list is set to equal today's date & " ," & date last received. I put multiple spaces and a comma so The older dates do not appear in the cell. Now that the string is made the date received column in the "Library" is set to equal the "complete list" string. The placement with these lines is tricky because of the range. I am also having problems making it skip black cells. After that one is added to the "library" worksheets quantity. Then the index goes to the next product code on the "Check_in" worksheet and the loop repeats until the list is finished.
Then all the entries and calculations on the check-in sheet are cleared. I also want to note that I plan to hide the columns that are used to do the calculations.
when I create the "Check-Out" button I want it to do the same thing, except subtract one from the quanity and delete the left most portion of the date last received up to the comma.
If anyone can fix this code that would be appreciated.
Both worksheets start at row 4
Columns on library worksheet are in order as follows
[house] [Product Code ] [chemical] [Location] [Date Received] [Quantity]
Columns on "check_in" worksheet are in order as follows
[Product #] [Empty] [Sample list to play] [empty] [Quanity] [Date List] [date today] [Date Received] [check out date]Sub Button1_lol_Click() Dim x As Long Dim today As String Dim last As String Dim check As String Dim Library As Worksheet Dim Check_in As Worksheet Dim productn As String Dim erow As Long Dim rng As Range Dim rownumber As Long Dim row As Long ' erow = Worksheets("Library").Cells(erow, 6) = 1 x = 4 Do While Cells(x, 1) <> "" ' go through each item on list productn = Cells(x, 1) 'date time Cells(x, 7).Value = Date Cells(x, 7).NumberFormat = "yyyy/m/d " Range("G:G").EntireColumn.AutoFit '******I hyphened out this location as a possibility because it was not working 'Cells(x, 8).Select 'today = Cells(x, 7).Value 'last = Cells(x, 8).Value 'check = Cells(x, 6).Value 'If Cells(x, 8) <> "" Then 'Cells(x, 6).Select 'check = today 'Else 'check = today & " ," & last 'End If Cells(x, 1).Select ' if item is not new then add quantity to total Library With Worksheets("Library").Range("B:B") Set rng = .Find(What:=productn, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) "4 is the for 'if item is new add item to the bottom of library list If rng Is Nothing Then erow = Worksheets("Library").Cells(4, 2).CurrentRegion.Rows.Count + 1 Worksheets("Library").Cells(erow, 2) = Worksheets("Check_in").Cells(x, 1) Worksheets("Library").Cells(erow, 5) = Worksheets("Check_in").Cells(x, 7) Worksheets("Library").Cells(erow, 6) = 1 GoTo ende Else rownumber = rng.row End If If Worksheets("Library").Cells(rownumber, 5) <> "" Then Worksheets("Library").Cells(rownumber, 5) = Worksheets("Check_in").Cells(x, 7).Value Else Worksheets("Library").Cells(rownumber, 5).Copy Worksheets("Check_in").Cells(x, 8) Worksheets("Library").Cells(rownumber, 5) = Worksheets("Check_in").Cells(x, 7).Value & _ " ," & Worksheets("Check_in").Cells(x, 8).Value End If End With Worksheets("Library").Cells(rownumber, 6).Value = _ Worksheets("Library").Cells(rownumber, 6).Value + 1 Worksheets("Library").Cells(rownumber, 6).Copy _ Worksheets("Check_in").Cells(x, 5) ende: x = x + 1 Loop 'delete entry Worksheets("Check_in").Select row = 4 Do While Cells(row, 1) <> "" Range(Cells(row, 1), Cells(row, 8)).Select Selection.Clear Loop End SubLEt me know if this makes sense and how to fix this code.
I've tried to work on this a few times but I'm having problems, mainly because I do not have a workbook to test your code against. I've tried to build one based on your words, but I don't think I've been successful, therefore I can't really test the code. It would help if you would provide some example data, laid out as it is on both sheets, so that I know what I am starting with and can concentrate on the code. The following link will provide the instructions on how to post example data in this forum. Please be sure to include Row numbers and Column letters so that we know exactly where the data resides.
How To Post Data or Code ---> Click Here Before Posting Data or VBA Code
Ad Choices