On Fri, 25 Jan 2013 00:37:25 +0000, pmterp wrote:


I have a workbook with 200 sheets. I have a summary page with a list
(in column B) of unique values that correspond with cell A4 on my other
sheets. I would like column C of my summary page to find the sheet that
has the same value as column B in cell A4 of a sheet and then return the
value of A16 from that sheet.

Example: Sheet "Summary" B2 = "1343345". Sheet "Client5" A4 also =
"1343345" so it returns Client5 A16 (which is $57,467.13) to C2 on
Summary Sheet.

Thanks is advance!

One way would be to write a User Defined Function in VBA

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MatchClientAmt(B2)

in some cell on Sheet: "Summary"

===================================
Option Explicit
Function MatchClientAmt(ClientNum)
Dim ws As Worksheet
Dim c As Range
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Summary" Then
With ws.Range("A:A")
Set c = .Find(what:=ClientNum, LookIn:=xlValues, _
lookat:=xlWhole)
If Not c Is Nothing Then
MatchClientAmt = .Item(RowIndex:=16)
Exit Function
End If
End With
End If
Next ws

MsgBox ("Client Number Not Found")

End Function
===============================