Selecting a range of data from another using a table in another worksheet

spiderjolly

Board Regular
Joined
Oct 20, 2009
Messages
58
HI guys
I am no expert on VBA and could really do with some help. I have data in a table on one worksheet and want to click on the result in a cell that is the performance of the bucket in the worksheet Overview and then for it to take me to the second worksheet, action tracker and alllow me to only see the cell range that I have asked for . For instance if i click on CH - Bespoke cell, where 1.2% is the intersection of the two, I want to view the range of rows that have issues in the action tracker. I hope this is clear enough for you to help, thanks in advance. Brgds Sean

Overview of performance Sheet name "Overview"
TargetPerformanceDisciplineTime / ResourceBespokeEquipmentUnallocated
Country
AT95.5%93.8%0.0%0.8%0.0%0.4%5.0%
BE86.0%82.0%0.0%0.0%0.0%0.0%18.0%
BG96.2%96.0%2.0%0.0%0.0%0.0%2.0%
CH97.7%96.4%0.0%0.0%1.2%0.5%1.9%
CZ76.8%59.9%0.0%0.0%4.0%10.0%26.1%
DE84.9%79.0%0.0%0.5%0.0%5.4%15.1%

<tbody>
</tbody>


Action Tracker Sheet the same.

IDMeetingIssue DateDescriptionPerformance bucketCountryDepotStatusPriorityDue Date
111/27/2017Direct feeder EPCOSBespoke processesATGRZIn progressH1/15/2018
211/27/2017very short time window between arrival and processing to connect on-time of dedicated truck ex customer PalfingerTime/Resource issuesATSZGIn progressM1/15/2018
311/27/2017issue with data transmissionEquipment issuesATVIEIn progressL1/15/2018
311/27/2017Mettler Toledo non convey measuring arm is brokenEquipment issuesATLNZIn progressL1/15/2018
411/27/2017Several times CWC V Tronic was used on Inbound mode, due to this many export cons leave without RPP. Shipments coming with own printed labels from the sender did not pass any RPP at all before export.DisciplineBGSOFIn progressM1/15/2018
511/27/2017Sender Account Nr. 000212240 (TNT Swiss Post SA) needs to exclude from the report as this
account nr. will only used in case of return shipments for the LGI / Bally / Jimmy Choo customers.
Bespoke processesCHLUGIn progressM1/15/2018

<tbody>
</tbody>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
With the assumption that data in both sheets for the examples shown begin in column A, you might be able to use this worksheet event code. The code would be installed in the Overview sheet.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim fn As Range
    Set fn = Sheets("Action Tracker").Range("F:F").Find(sh1.Cells(Target.Row, 1).Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fAdr = fn.Address
            Do
                If InStr(fn.Offset(, -1).Value, Cells(1, Target.Column).Value) > 0 Then
                    Sheets("Overview").UsedRange.Rows.Hidden = True
                    Sheets("Overview").Rows(fn.Row).Hidden = False
                    Sheets("Overview").Select
                    Exit Do
                End If
                fn = Sheets("Action Tracker").Range("F:F").FindNext(fn)
            Loop While fn.Address <> fAdr
        End If
End Sub

Since I am guessing at where on the sheets your data is located, you might need to modify some of the parameters in the code.
Also, if the code works as is, you will need to unhide the rows on Action Tracker when you have finshed working the issue.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top