Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookups
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!! -- Bruce |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookups
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" wrote: Why not just use datafilterautofilter -- Don Guillett Microsoft MVP Excel SalesAid Software "Bruce D." wrote in message ... 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!! -- Bruce . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookups
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Bruce D." wrote in message ... 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" wrote: Why not just use datafilterautofilter -- Don Guillett Microsoft MVP Excel SalesAid Software "Bruce D." wrote in message ... 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!! -- Bruce . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookups
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. "Bruce D." wrote: 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!! -- Bruce |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookups
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 "JLatham" wrote: 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. "Bruce D." wrote: 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!! -- Bruce |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookups
Thanks a million JL!!!!
-- Bruce "JLatham" wrote: 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 "JLatham" wrote: 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. "Bruce D." wrote: 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!! -- Bruce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookups | Excel Discussion (Misc queries) | |||
Lookups | Excel Discussion (Misc queries) | |||
Lookups... | Excel Worksheet Functions | |||
lookups | Excel Discussion (Misc queries) | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions |