How to imbed different data validation w/ conditional format

Microsoft Excel 2013 32/64-bit - license...
April 21, 2020 at 20:14:30
Specs: Windows
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!


See More: How to imbed different data validation w/ conditional format

Reply ↓  Report •

#1
April 22, 2020 at 06:44:52
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


Reply ↓  Report •

#2
April 26, 2020 at 13:34:20
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 order

if 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 Sub

LEt me know if this makes sense and how to fix this code.


Reply ↓  Report •

#3
April 28, 2020 at 17:54:18
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


Reply ↓  Report •
Related Solutions


Ask Question