# Revision history [back]

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. 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'

'   Only if single cell was modified'

'   If row of cell changed was greater than 20 and less than 26'
If Len(oEvent.Formula) > 0 Then
'   Ignore formula to avoid recursion of event'
If Left(oEvent.Formula, 1) <> "=" Then
Case 0  '   Column A'
"=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'
"=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


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