Lookups

[Bruce D.]

I am using excel 2007 and I am working with a workbook that has 2 sheets. One
is called Monthly and the other is called History. Both contain the same
fields and columns which includes Account Numbers. The Monthly has a small
amount of records for the month and the History has all records for the year
2010. What I would like to do is hide the History tab. Which is no problem.
And when the user is looking at the monthly records they can also select a
account number on that sheet and a dropdown, combo box, or lookup will pop up
and they would be able to then view all the history records for that account
number. I am not sure how to approach or attempt this.

Thanks to all who respond!!

 

[Don Guillett]

Why not just use data>filter>autofilter>>>>

 

[Bruce D.]

I could. But the user needs the ability to see all history records for that
account number while on the monthly sheet for analysis.
Bruce

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[JLatham]

Rather than thinking about dropdowns/combo boxes and such you might think
about this:
when a user double-clicks on an account number on the monthly sheet, then
you'd apply data filtering to the History sheet and bring it into view and
activate it. Then when they go and click on the monthly sheet again, you
simply hide the history sheet. Most of what you need to know could be found
out by recording macros while doing it, with some modification to the
recorded code.

You'd probably want to work with the Monthly sheet's Before_DoubleClick()
event and it's Activate() event.

 

[JLatham]

I believe the code below would do as I described previously. It needs to be
in the Monthly sheet's code module: open the workbook and choose that sheet
and right-click on the monthly sheet's name tab and choose [View Code]. Then
copy the code and paste it into the module and change the Const values in
each routine as needed.

Private Sub Worksheet_Activate()
'this will take place when you select
'this sheet after some other sheet has
'been selected
'this is the name of the history sheet
'change as required
Const historyName = "Sheet2"

ThisWorkbook.Worksheets(historyName).Visible = _
xlSheetHidden
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'change these Const values as required
'this should refer to the
'column that the account numbers are in
Const accountColumn = "A"
'this is the first row that an
'account # is entered into
Const firstAcctRow = 2
'this is the name of the history sheet
Const historyName = "Sheet2"

Dim historySheet As Worksheet

If Target.Cells.Count = 1 _
And Not IsEmpty(Target) _
And Target.Row >= firstAcctRow Then
Set historySheet = _
ThisWorkbook.Worksheets(historyName)
historySheet.Range(accountColumn & 1). _
AutoFilter field:=1, Criteria1:=Target.Value
historySheet.Visible = xlSheetVisible
historySheet.Activate
Set historySheet = Nothing
End If
End Sub

 

[Bruce D.]

Thanks a million JL!!!!
--
Bruce

I believe the code below would do as I described previously. It needs to be
in the Monthly sheet's code module: open the workbook and choose that sheet
and right-click on the monthly sheet's name tab and choose [View Code]. Then
copy the code and paste it into the module and change the Const values in
each routine as needed.

Private Sub Worksheet_Activate()
'this will take place when you select
'this sheet after some other sheet has
'been selected
'this is the name of the history sheet
'change as required
Const historyName = "Sheet2"

ThisWorkbook.Worksheets(historyName).Visible = _
xlSheetHidden
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'change these Const values as required
'this should refer to the
'column that the account numbers are in
Const accountColumn = "A"
'this is the first row that an
'account # is entered into
Const firstAcctRow = 2
'this is the name of the history sheet
Const historyName = "Sheet2"

Dim historySheet As Worksheet

If Target.Cells.Count = 1 _
And Not IsEmpty(Target) _
And Target.Row >= firstAcctRow Then
Set historySheet = _
ThisWorkbook.Worksheets(historyName)
historySheet.Range(accountColumn & 1). _
AutoFilter field:=1, Criteria1:=Target.Value
historySheet.Visible = xlSheetVisible
historySheet.Activate
Set historySheet = Nothing
End If
End Sub

Rather than thinking about dropdowns/combo boxes and such you might think
about this:
when a user double-clicks on an account number on the monthly sheet, then
you'd apply data filtering to the History sheet and bring it into view and
activate it. Then when they go and click on the monthly sheet again, you
simply hide the history sheet. Most of what you need to know could be found
out by recording macros while doing it, with some modification to the
recorded code.

You'd probably want to work with the Monthly sheet's Before_DoubleClick()
event and it's Activate() event.