200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I have data in 12 different sheets. Now I want to create a cell where I put number and if the number match with any sheet then give a message "Sheet1 is containg the number" or something else. The message should be different based on which sheet contain the value. Please help me to suggest me a formula. I am new in excel.
Thanks
I would use this code:
Function VLOOKUPWORKBOOK(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
'Vlookup function that will search all worksheets in the workbook - however, the data table that is being searched must be in the same
'location on every worksheet.
Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet
Set Tble_Array = Nothing
VLOOKUPWORKBOOK = "Sheet " & wSheet.Name & " contains the number."
End Function
This is a macro and is modified from our macro (UDF) here: Vlookup across the entire workbook.
To better understand the macro, read through the link. The main thing is that the data has to be in the same location on each sheet.
Also, to learn more about UDF's (user defined functions) read our tutorial on UDF's.