help with searching

[mnut1975]

Hi

I have 12 sheets named after each month in column A there is a client number and in column B a client name. Each sheet holding upto 100 clients.

what i want to do is if I type any client number into sheet number 13 colum A (named status) I want it to copy the clients name into column B on the sheet named status.

Would prefer to use a formula if possible please

thanks in advance

 

[VoG]

Perhaps VlookAllSheets http://www.ozgrid.com/VBA/VlookupAllSheets.htm

 

[mnut1975]

cant seem to get it working the i used =vlookallsheets(a2,a4:b1000,2,false) in cell b2

Which I though would match the client number i typed into a2 and return the name in b2 but doesnt work

 

[VoG]

What do you get - nothing, wrong result, error...

 

[mnut1975]

#NAME? error

It doesnt recognise the vlookallsheets

 

[VoG]

Did you put the code in a regular module?

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste in the code.

 

[mnut1975]

sorry i was pasting it into a cell as a formula i know nothing about VB

 

[VoG]

OK, once you've pasted in the code, press ALT + Q to close the code window then try re-entering the formula,

 

[mnut1975]

no joy I dont have any understanding of VB but thanks for the help anyway

 

[VoG]

Maybe the code didn't paste in properly. It should be

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com
''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com
'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
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
    VLOOKAllSheets = vFound
End Function