I've used the below VBA for creating the New VLOOKPUP workbook function to search & collet data to a master worksheet from every single worksheets within the workbook, it works fine for the first entry but the issue i am facing that the reurn values colleted never updated when change or update the values witthin the workbook unless re-type the lookup value again or at least delete one letter and re-type it again and hit enter to force the VLOOKUP function to work and update the return value with the new values. i am looking for any suggestions to solve this issue. thanks
VBA used
Function VLOOKUPWORKBOOK( _
lookup_value As Variant, _
table_array As Range, _
col_index_num As Integer, _
Optional range_lookup As Boolean, _
Optional sheets_to_exclude_1 As String, _
Optional sheets_to_exclude_2 As String, _
Optional sheets_to_exclude_3 As String, _
Optional sheets_to_exclude_4 As String, _
Optional sheets_to_exclude_5 As String _
)
Dim mySheet As Worksheet
Dim value_to_return
Dim sheets_to_exclude
On Error Resume Next
'Put sheets to exclude into an array
sheets_to_exclude = Array(sheets_to_exclude_1, sheets_to_exclude_2, sheets_to_exclude_3, sheets_to_exclude_4, sheets_to_exclude_5)
'Cycle through all sheets in the workbook
For Each mySheet In ActiveWorkbook.Worksheets
'Check if the sheet needs to be excluded or not.
If Not (UBound(Filter(sheets_to_exclude, mySheet.Name)) > -1) Then
With mySheet
Set table_array = .Range(table_array.Address)
'Run the actual vlookup function on the current sheet that is being looped though.
value_to_return = WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, range_lookup)
End With
'Exit the loop once have a value to return.
If Not IsEmpty(value_to_return) Then
Exit For
End If
End If
Next mySheet
'Send the result back to the cell that contains the function.
VLOOKUPWORKBOOK = value_to_return
End Function