Find matching cell and return value of a different cell

[pmterp]

I have a workbook with 200 sheets. I have a summary page with a lis
(in column B) of unique values that correspond with cell A4 on my othe
sheets. I would like column C of my summary page to find the sheet tha
has the same value as column B in cell A4 of a sheet and then return th
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 o
Summary Sheet.

Thanks is advance

 

[Ron Rosenfeld]

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

 

[pmterp]

;1608881']On Fri, 25 Jan 2013 00:37:25 +0000, pmter

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 fe
modifications to my 2013 workbook and the cells are different. I'v
tried making the changes in the code but I'm not getting somethin
right.
2013 Workbook changes
Still unique values is Column B and want the data returned to Column
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 merge
together.

Any MORE help would be greatly appreciated

 

[Ron Rosenfeld]

;1608881']On Fri, 25 Jan 2013 00:37:25 +0000, pmterp

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.

 

[pmterp]

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

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

 

[Ron Rosenfeld]

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.