Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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'

    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

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