Vlookup with Validity cell

[Ogitec]

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.

[mark_t]

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

[mark_t]

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

[Ogitec]

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.

[mariosv]

If the answer solves your question please tick the .

[mark_t]

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