Click cell to filter based on another workbook automatically
Kutools for Excel
Supposing, you have two workbooks, one is the main workbook which contains the product profile, and another workbook contains the detail information of the product. Now, you want to create a real time filtering between these two workbooks and view the two workbooks side by side. It means when clicking one cell in the key column of the primary workbook, all corresponding data in the secondary workbook will be filtered immediately as below demo shown.
If you want to achieve this filtering effect, Kutools for Excel supports a powerful feature – Data Association, with this feature, you can correlate the data in two workbooks and view the two sheets side by side in real-time filtering.
Please do with the following steps to achieve this operation:
1. Open the main workbook that you want to correlate with another one.
2. Then, click Kutools > Content > Data Association > Enable Data Association to enable this feature, see screenshot:
3. Go on clicking Kutools > Content > Data Association > Create Data Association to create the data association between the two workbooks, see screenshot:
4. In the popped out Data Association dialog box, please do the following operations:
(1.) The opened main workbook has been displayed into the Primary Workbook section, you just need to select the primary worksheet and key column that you want to filter the data in another workbook based on;
(2.) In the Secondary Workbook section, choose the secondary workbook which you want to associate. Then the secondary workbook is opening, and the two workbooks are displayed side by side;
(3.) At last, select the worksheet and key column from the secondary workbook that you want to filter.
5. After finishing the settings, click Ok button. Now, you just need to click one cell of the key column in the main workbook, all corresponding data in the secondary workbook will be filtered out at once based on the clicked cell, see below demo:
1. In the Data Association dialog box:
Example button: Click this Example button will open two sample workbooks which help you to demonstrate the effect of the feature.
Save scenario button: Click this Save scenario button will help to save the current workbooks settings for future using. In the popped out dialog box, please enter a scenario name as you need, see screenshot:
2. If you have created multiple scenarios, when you want to apply one scenario, just need to click Kutools > Content > Data Association > Apply Data Associations to go the Apply/Manage Data dialog box. In the dialog box, please click the scenario that you want to use, and then click Apply button to activate this scenario. See screenshots:
Tips: In the Apply/Manage Data dialog box:
1). You can also click Create button to create new scenario you want to use;
2). You can click the button to remove the unwanted scenario.
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.
The functionality described above is just one of 300 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.