Ask Your Question

Vlookup with Validity cell

asked 2016-09-10 19:32:47 +0200

Ogitec gravatar image

updated 2016-09-12 05:21:53 +0200

Hello everyone. I have been working at this issue for a while. I currently have a workorder book for my small business that I use for billing. It is made up with a sheet titled 'workorder' and a second sheet titled 'materials'. The focus of my question has to do specifically with the material section of my 'workorder'. This section is divided into four columns: Item, Serial Number, Quantity, Price. Item column is a validity list from the 'materials' sheet, allowing me to select from the list of materials I use (about 1200 entries). Serial Number is broken and is what I am trying to solve. Quantity is obvious for the quantity of materials used. Price has the formula

=IF(ISNA(VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)),"",VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)*F21)

This allows me to select an item from the drop down menu, add a quantity and a price will auto fill into the cell. My problem is that I have such a long list of materials at this point, it is miserable trying to scroll through all of them to find the one I want. My goal is to have both the ability to use the drop down menu or input a serial number and have the cells in the row fill out (material name and price based on qty). My dilemma is that when using validity in a cell, you are not able to add any formulas to it. I am thinking I will need to find a macro that will auto-choose from the drop down based on input from the Serial Number cell.

edit retag flag offensive close merge delete


I edited the question to place the formula into a code block.

mark_t gravatar imagemark_t ( 2016-09-10 20:58:28 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-09-10 20:55:20 +0200

mark_t gravatar image

updated 2016-09-13 03:17:00 +0200

Your price formula can be simplified to :

=IFNA(VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0),"")

Instead of selecting an item number, which could be difficult to remember without scrolling down your list, it might be better to select the serial number using the Validity cell range, or add a more descriptive column to your materials list for the purpose of selection.

If the column for selection is not the first column of your table you would need something like the following instead of VLOOKUP. This searches column B and chooses value from column A.

=IFNA(INDEX($'Material Stock'.A$3:A$1197,MATCH(A21,$'Material Stock'.B$3:B$1197,0)),"")

If you have experience with macros you might create a dialog to perform the selection but If you have a large list of material it might be better to start using a database.

Edit: I think to allow either item number or Serial number to be entered and search for the other from the list would require a macro.

Attached example. Untitled 21.ods

I tried to base this on your description but it may need some modifications. The macro included is as follows and needs to be assigned to the sheet event "Content changed" on the workorder sheet.

If column A is changed then the formula to search is entered in column B. If column B is changed the formula is entered in column A.

REM  *****  BASIC  *****

Option Explicit

Sub ContentChangedEvent(oEvent As Object)
'   Assign this macro to the Sheet event "Content changed" or workorder sheet'

    Dim oRngAd As Object

    oRngAd = oEvent.RangeAddress

    '   Only if single cell was modified'
    If oRngAd.StartRow <> oRngAd.EndRow Then Exit Sub
    If oRngAd.StartColumn <> oRngAd.EndColumn Then Exit Sub

    '   If row of cell changed was greater than 20 and less than 26'
    If oRngAd.StartRow > 19 And oRngAd.StartRow < 25 Then
        If Len(oEvent.Formula) > 0 Then
            '   Ignore formula to avoid recursion of event'
            If Left(oEvent.Formula, 1) <> "=" Then
                Select Case oRngAd.StartColumn
                    Case 0  '   Column A'
                        oEvent.Spreadsheet.getCellByPosition(1, oRngAd.StartRow).Formula = _
                            "=IFNA(INDEX($'Material Stock'.B$3:B$1197;MATCH(A" & Format(oRngAd.StartRow + 1,"0") & ";$'Material Stock'.A$3:A$1197;0));"")"
                    Case 1  '   Column B'
                        oEvent.Spreadsheet.getCellByPosition(0, oRngAd.StartRow).Formula = _
                            "=IFNA(INDEX($'Material Stock'.A$3:A$1197;MATCH(B" & Format(oRngAd.StartRow + 1,"0") & ";$'Material Stock'.B$3:B$1197;0));"")"
                    Case Else
                            '   Do Nothing'
                End Select
            End If
        End If
    End If
End Sub
edit flag offensive delete link more


Thank you for the edit for the formula and the simplified version, and I appreciate your time to answering my question. I have revised my question slightly before to be more focused on trying to figure out how to use both the S/N and Validity cell together, so that one will fill out the other. Again thank you for your time.

Ogitec gravatar imageOgitec ( 2016-09-12 05:25:08 +0200 )edit

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2016-09-12 10:24:26 +0200 )edit

Updated the attached example to verify the macro works ok when using data validity to select item or serial number.

mark_t gravatar imagemark_t ( 2016-09-13 03:18:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-10 19:32:47 +0200

Seen: 277 times

Last updated: Sep 13 '16