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!

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
===============================

Thanks a lot. This is what I need. However, I've made a few modifications to my 2013 workbook and the cells are different. I've tried making the changes in the code but I'm not getting something right.
2013 Workbook changes
Still unique values is Column B and want the data returned to Column C of summary page.
Individual Client pages now will have the unique number in cell F2.
The value I need returned is in cell K22 (but K - O) is merged together.

Any MORE help would be greatly appreciated.

On Fri, 25 Jan 2013 20:40:05 +0000, pmterp wrote:
[color=blue][i]

'Ron Rosenfeld[_2_ Wrote:
;1608881']On Fri, 25 Jan 2013 00:37:25 +0000, pmterp
wrote:

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
===============================

Thanks a lot. This is what I need. However, I've made a few
modifications to my 2013 workbook and the cells are different. I've
tried making the changes in the code but I'm not getting something
right.
2013 Workbook changes
Still unique values is Column B and want the data returned to Column C
of summary page.
Individual Client pages now will have the unique number in cell F2.
The value I need returned is in cell K22 (but K - O) is merged
together.

Any MORE help would be greatly appreciated.

Well, if the Client Number on the Client page will always be in F2; and the value you want returned will always be in K22, you could use a routine that merely looks at all the F2's on sheets that are not named "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
If .Range("F2") = ClientNum Then
MatchClientAmt = .Range("K22")
Exit Function
End If
End With
End If
Next ws

MsgBox ("Client Number Not Found")

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

Be careful with merged cells, however. They can become confusing to use.

Ron, I can't say thank you enough. This works great and will save me a ton of time.

On Sun, 27 Jan 2013 20:10:38 +0000, pmterp wrote:

Ron, I can't say thank you enough. This works great and will save me a
ton of time.

Glad to help. Thanks for the feedback.