Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by BobArnett
at 2024-08-03 07:07:18
ModifyPoint:500 Replies:13 POST_ID:828686USER_ID:11576
Topic:
Microsoft Excel Spreadsheet Software;;
I am using Excel 2010 and have a workbook with two tables on separate worksheets. I have made a workspace that views the two worksheets side-by-side (horizontal). What I am trying to do is have Table2 field "ItemID" filtered to match "ItemID" field of the currently selected row of Table1.
Author: BobArnett replied at 2024-08-07 10:18:45
One more thing... please see my new question Q_28206662.
Author: BobArnett replied at 2024-08-07 07:58:17
I was able to figure out keeping Items active in the one window by adding
"Worksheets("Items").Select"
to the code just before the line
"Application.ScreenUpdating = True"
that seems to work perfectly. You definitely earned these point.
"Worksheets("Items").Select"
to the code just before the line
"Application.ScreenUpdating = True"
that seems to work perfectly. You definitely earned these point.
Author: BobArnett replied at 2024-08-07 07:19:58
Hi Bob,
Sorry we missed each other yesterday.
The Visual Basic for Applications code below is included within the attached workbook.
I appreciate the code is quite involved, but I have hopefully catered for most (if not all) selections upon the [Items] worksheet a user may attempt; the selection of multiple rows will filter on each respective [Item ID] value, & the selection of non-contiguous rows is also possible. Additionally, an entire row does not need to be selected; simply a single cell on any row (from row 3 onwards) will be used as part of the filter criteria of the [Purchases] worksheet.
Note that I have also added code into the Worksheet_BeforeDoubleClick(...) event procedure so that if cell [A1] on the [Items] worksheet is double-clicked, the filter applied to column [A] upon the [Purchases] worksheet is removed (& all rows are then visible again). I thought this may be useful as there was no way to see all the rows following the application of at least one filter.
Sorry we missed each other yesterday.
The Visual Basic for Applications code below is included within the attached workbook.
I appreciate the code is quite involved, but I have hopefully catered for most (if not all) selections upon the [Items] worksheet a user may attempt; the selection of multiple rows will filter on each respective [Item ID] value, & the selection of non-contiguous rows is also possible. Additionally, an entire row does not need to be selected; simply a single cell on any row (from row 3 onwards) will be used as part of the filter criteria of the [Purchases] worksheet.
Note that I have also added code into the Worksheet_BeforeDoubleClick(...) event procedure so that if cell [A1] on the [Items] worksheet is double-clicked, the filter applied to column [A] upon the [Purchases] worksheet is removed (& all rows are then visible again). I thought this may be useful as there was no way to see all the rows following the application of at least one filter.
Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)' --------------------------------------------------------------------------------------------------------------' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28203042.html ]'' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel'' ID: 28203042' Question Title: Filtering a table based on current cell in another table' Question Asker: BobArnett [ http://www.experts-exchange.com/M_433431.html ]' Question Dated: 2013-08-03 at 15:07:18'' Expert Comment: fanpages [ http://www.experts-exchange.com/M_258171.html ]' Copyright: (c) 2013 Clearlogic Concepts (UK) Limited [ http://NigelLee.info ]' -------------------------------------------------------------------------------------------------------------- Dim blnFiltered As Boolean Dim blnFirst As Boolean Dim lngArea As Long Dim lngErr_Number As Long Dim lngRow As Long Dim strArray() As String Dim strErr_Description As String On Error GoTo Err_Worksheet_SelectionChange blnFiltered = False blnFirst = False Application.ScreenUpdating = False If Target.Rows.Count = 1& Then If Target.Row > 2& Then Worksheets("Purchases").Select ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1 ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=Worksheets("Items").Cells(Target.Row, 1).Value blnFiltered = True End If ' If Target.Row > 2& Then Else ReDim strArray(0&) As String blnFirst = True For lngArea = 1& To Target.Areas.Count For lngRow = Target.Areas(lngArea).Resize(, 1).Row To Target.Areas(lngArea).Resize(, 1).Row + Target.Areas(lngArea).Resize(, 1).Rows.Count - 1& If lngRow > 2& Then If Not IsEmpty(Worksheets("Items").Cells(lngRow, 1).Value) Then If Not (blnFirst) Then ReDim Preserve strArray(UBound(strArray) + 1&) As String End If ' If Not (blnFirst) Then strArray(UBound(strArray)) = Worksheets("Items").Cells(lngRow, 1).Value blnFirst = False End If ' If Not IsEmpty(Worksheets("Items").Cells(lngRow, 1).Value) Then End If ' If lngRow > 2& Then Next lngRow Next lngArea If Not (blnFirst) Then Worksheets("Purchases").Select ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1 ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=strArray(), Operator:=xlFilterValues blnFiltered = True End If ' If Not (blnFirst) Then End If ' If Target.Rows.Count = 1& ThenExit_Worksheet_SelectionChange: On Error Resume Next Erase strArray ReDim strArray(0&) As String If (blnFiltered) Then Worksheets("Purchases").Select Application.EnableEvents = False Application.Goto Reference:=Worksheets("Purchases").[A1], Scroll:=True Application.EnableEvents = True End If ' If (blnFiltered) Then Application.ScreenUpdating = True Exit SubErr_Worksheet_SelectionChange: lngErr_Number = Err.Number strErr_Description = Err.Description On Error Resume Next MsgBox "ERROR #" & CStr(lngErr_Number) & _ vbCrLf & vbLf & _ strErr_Description, _ vbExclamation Or vbOKOnly, _ ThisWorkbook.Name Resume Exit_Worksheet_SelectionChangeEnd SubPrivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error Resume Next If Target.Address = "$A$1" Then Application.ScreenUpdating = False Worksheets("Purchases").Select ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1 Worksheets("Items").Select Application.ScreenUpdating = True Cancel = True End If ' If Target.Address = "$A$1" ThenEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:42:43:44:45:46:47:48:49:50:51:52:53:54:55:56:57:58:59:60:61:62:63:64:65:66:67:68:69:70:71:72:73:74:75:76:77:78:79:80:81:82:83:84:85:86:87:88:89:90:91:92:93:94:95:96:97:98:99:100:101:102:103:104:105:106:107:108:109:110:111:112:113:114:115:116:117:118:119:120:121:122:123:
Thank you for reviewing this proposal & please let me know if it meets your requirements.
BFN,
fp.
Attachment:Q-28203042.xlsm
Accepted Solution
Expert: [ fanpages ] replied at 2024-08-06 15:17:20
500 points EXCELLENT
Hi Bob,
Sorry we missed each other yesterday.
The Visual Basic for Applications code below is included within the attached workbook.
I appreciate the code is quite involved, but I have hopefully catered for most (if not all) selections upon the [Items] worksheet a user may attempt; the selection of multiple rows will filter on each respective [Item ID] value, & the selection of non-contiguous rows is also possible. Additionally, an entire row does not need to be selected; simply a single cell on any row (from row 3 onwards) will be used as part of the filter criteria of the [Purchases] worksheet.
Note that I have also added code into the Worksheet_BeforeDoubleClick(...) event procedure so that if cell [A1] on the [Items] worksheet is double-clicked, the filter applied to column [A] upon the [Purchases] worksheet is removed (& all rows are then visible again). I thought this may be useful as there was no way to see all the rows following the application of at least one filter.
Sorry we missed each other yesterday.
The Visual Basic for Applications code below is included within the attached workbook.
I appreciate the code is quite involved, but I have hopefully catered for most (if not all) selections upon the [Items] worksheet a user may attempt; the selection of multiple rows will filter on each respective [Item ID] value, & the selection of non-contiguous rows is also possible. Additionally, an entire row does not need to be selected; simply a single cell on any row (from row 3 onwards) will be used as part of the filter criteria of the [Purchases] worksheet.
Note that I have also added code into the Worksheet_BeforeDoubleClick(...) event procedure so that if cell [A1] on the [Items] worksheet is double-clicked, the filter applied to column [A] upon the [Purchases] worksheet is removed (& all rows are then visible again). I thought this may be useful as there was no way to see all the rows following the application of at least one filter.
Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)' --------------------------------------------------------------------------------------------------------------' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28203042.html ]'' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel'' ID: 28203042' Question Title: Filtering a table based on current cell in another table' Question Asker: BobArnett [ http://www.experts-exchange.com/M_433431.html ]' Question Dated: 2013-08-03 at 15:07:18'' Expert Comment: fanpages [ http://www.experts-exchange.com/M_258171.html ]' Copyright: (c) 2013 Clearlogic Concepts (UK) Limited [ http://NigelLee.info ]' -------------------------------------------------------------------------------------------------------------- Dim blnFiltered As Boolean Dim blnFirst As Boolean Dim lngArea As Long Dim lngErr_Number As Long Dim lngRow As Long Dim strArray() As String Dim strErr_Description As String On Error GoTo Err_Worksheet_SelectionChange blnFiltered = False blnFirst = False Application.ScreenUpdating = False If Target.Rows.Count = 1& Then If Target.Row > 2& Then Worksheets("Purchases").Select ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1 ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=Worksheets("Items").Cells(Target.Row, 1).Value blnFiltered = True End If ' If Target.Row > 2& Then Else ReDim strArray(0&) As String blnFirst = True For lngArea = 1& To Target.Areas.Count For lngRow = Target.Areas(lngArea).Resize(, 1).Row To Target.Areas(lngArea).Resize(, 1).Row + Target.Areas(lngArea).Resize(, 1).Rows.Count - 1& If lngRow > 2& Then If Not IsEmpty(Worksheets("Items").Cells(lngRow, 1).Value) Then If Not (blnFirst) Then ReDim Preserve strArray(UBound(strArray) + 1&) As String End If ' If Not (blnFirst) Then strArray(UBound(strArray)) = Worksheets("Items").Cells(lngRow, 1).Value blnFirst = False End If ' If Not IsEmpty(Worksheets("Items").Cells(lngRow, 1).Value) Then End If ' If lngRow > 2& Then Next lngRow Next lngArea If Not (blnFirst) Then Worksheets("Purchases").Select ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1 ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=strArray(), Operator:=xlFilterValues blnFiltered = True End If ' If Not (blnFirst) Then End If ' If Target.Rows.Count = 1& ThenExit_Worksheet_SelectionChange: On Error Resume Next Erase strArray ReDim strArray(0&) As String If (blnFiltered) Then Worksheets("Purchases").Select Application.EnableEvents = False Application.Goto Reference:=Worksheets("Purchases").[A1], Scroll:=True Application.EnableEvents = True End If ' If (blnFiltered) Then Application.ScreenUpdating = True Exit SubErr_Worksheet_SelectionChange: lngErr_Number = Err.Number strErr_Description = Err.Description On Error Resume Next MsgBox "ERROR #" & CStr(lngErr_Number) & _ vbCrLf & vbLf & _ strErr_Description, _ vbExclamation Or vbOKOnly, _ ThisWorkbook.Name Resume Exit_Worksheet_SelectionChangeEnd SubPrivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error Resume Next If Target.Address = "$A$1" Then Application.ScreenUpdating = False Worksheets("Purchases").Select ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1 Worksheets("Items").Select Application.ScreenUpdating = True Cancel = True End If ' If Target.Address = "$A$1" ThenEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:42:43:44:45:46:47:48:49:50:51:52:53:54:55:56:57:58:59:60:61:62:63:64:65:66:67:68:69:70:71:72:73:74:75:76:77:78:79:80:81:82:83:84:85:86:87:88:89:90:91:92:93:94:95:96:97:98:99:100:101:102:103:104:105:106:107:108:109:110:111:112:113:114:115:116:117:118:119:120:121:122:123:
Thank you for reviewing this proposal & please let me know if it meets your requirements.
BFN,
fp.
Author: BobArnett replied at 2024-08-05 07:28:46
Here's the condensed version of the workbook. Sorry for the delay.
Expert: [ fanpages ] replied at 2024-08-03 12:10:04
No problem at all. Thanks for letting me know.
Author: BobArnett replied at 2024-08-03 11:50:34
I think time-wise it would be better off for me to wait until Monday to get you a "real" sample.
Expert: [ fanpages ] replied at 2024-08-03 11:18:33
Hi again BobArnett,
In truth, I don't need the exact workbook but a mock-up so that the same columns are present with the column headings & (some test) data starting on the same rows; sufficient test data so that the select of at least two different values in column [A] on the first worksheet will result in different results when filtering the second worksheet.
Sorry, but the image you uploaded may be clear to some, but my failing eyesight leads me to believe that I will be missing some of the detail.
If you could summarise the columns on each worksheet, & the layout of the data, I would be able to create a similar model.
However, as you are more familiar with the original, perhaps it would be quicker/easier if you could oblige with the preparation of an example file.
If you would prefer to wait until Monday so I can apply the necessary amendments to the actual workbook, rather than a fabricated version, then that's fine.
Please just let me know either way.
Thanks.
In truth, I don't need the exact workbook but a mock-up so that the same columns are present with the column headings & (some test) data starting on the same rows; sufficient test data so that the select of at least two different values in column [A] on the first worksheet will result in different results when filtering the second worksheet.
Sorry, but the image you uploaded may be clear to some, but my failing eyesight leads me to believe that I will be missing some of the detail.
If you could summarise the columns on each worksheet, & the layout of the data, I would be able to create a similar model.
However, as you are more familiar with the original, perhaps it would be quicker/easier if you could oblige with the preparation of an example file.
If you would prefer to wait until Monday so I can apply the necessary amendments to the actual workbook, rather than a fabricated version, then that's fine.
Please just let me know either way.
Thanks.
Expert: duncanb7 replied at 2024-08-03 11:11:43
sorry about that, but I thought that is your need.
Could you write it more clearly ?
I think I will answer you within 15 minutes
Could you write it more clearly ?
I think I will answer you within 15 minutes
Author: BobArnett replied at 2024-08-03 11:09:55
duncanb7: The youtube video did not address my problem.
fanpages: Yes, it's the first column [A] in both tables. I can upload the workbook but I don't have it here at home, I'll have to wait until Monday to get it at work. It's pretty big so I'll make a Reader's Digest version to upload.
fanpages: Yes, it's the first column [A] in both tables. I can upload the workbook but I don't have it here at home, I'll have to wait until Monday to get it at work. It's pretty big so I'll make a Reader's Digest version to upload.
Expert: [ fanpages ] replied at 2024-08-03 10:00:03
Hi BobArnett,
I made this possible for another party a few days ago.
Please could you confirm which column(s) in the first worksheet relate to the "key" column(s) in the second worksheet on which the filtering is to occur?
Is it just column [A] of both worksheets that are the common factor between the two. so that the selection in column [A] of the first worksheet dictates the filtering that will occur in column [A] within the second worksheet?
Also, would it be possible to attach (upload) the workbook you are using or a subset of it that includes some sample data to demonstrate the approach is working as you expected?
If the workbook contains sensitive data, please simply remove (or obfuscate) as required, but leave sufficient test data in the two worksheets.
Thank you.
BFN,
fp.
I made this possible for another party a few days ago.
Please could you confirm which column(s) in the first worksheet relate to the "key" column(s) in the second worksheet on which the filtering is to occur?
Is it just column [A] of both worksheets that are the common factor between the two. so that the selection in column [A] of the first worksheet dictates the filtering that will occur in column [A] within the second worksheet?
Also, would it be possible to attach (upload) the workbook you are using or a subset of it that includes some sample data to demonstrate the approach is working as you expected?
If the workbook contains sensitive data, please simply remove (or obfuscate) as required, but leave sufficient test data in the two worksheets.
Thank you.
BFN,
fp.
Expert: duncanb7 replied at 2024-08-03 07:49:50
This link may help you finally
http://www.youtube.com/watch?v=15Oejw3CLuc
http://www.youtube.com/watch?v=15Oejw3CLuc
Author: BobArnett replied at 2024-08-03 07:09:23
Forgot to load this screenshot.
