Dynamically filter data from one worksheet to another

{ 72 Comments }

By Ashish Mathur · · , · Leave a comment

Assume there is data in range A2:BG2185 of a worksheet (Range A1:O1 have headings).  In column A are years and in column B are Countries.  Other columns have some text/numeric data.  In column A are years ranging from 1984 to 2009 for each country mentioned in column B.  Therefore, Albania would appear in range B2:B27 and 1984-2009 would appear in range A2:A27.  Next, Algeria would appear in range B28:C53 and 1984-2010 would appear in range A28:A53 and so on.  For 84 countries, the number of rows occupied will be 2,184.  You may download the workbook from here.

The objective is to show in another worksheet of the same workbook, all data from year 1990 onwards (year 1990 included) for each country.  Furthermore, if data gets added (by rows) or edited in this base data sheet, the result sheet should update.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

You may refer to my solution at this link

Solution for all versions of MS Excel

The following process should get the desired result

1. Select range A1:O82 and press Ctrl+F3 > New > Name.  In the Name box, enter Dummy and click on OK.
2. Select range A1:O82 and press Ctrl+T.  Ensure that the “My data has headers” box is checked and click on OK
3. Save the file (assume on the Desktop for now)
4. Open a blank worksheet and go to Data > From Other Sources > From Microsoft Query
5. Select Excel files and click on OK
6. In the right had side panel, navigate to the folder where you saved the file in step 3 (Desktop in this case).  In the left hand side panel, select the specific Excel file which you saved in step 3 and click on Next
7. With Dummy appearing in the left hand side panel, click on the > symbol to transfer all columns of Dummy to the right hand side panel and click on Next
8. In the column to Filter box, select year and in the right hand side Year box, select Greater than Equal to and then select 1990
9. Click on OK Twice
10. Select “Return Data to Microsoft Excel” and click on Finish
11. In the Import Data box, click on OK

The data on this sheet will now show data for all countries from year 1990 onwards (year 1990 included) and this will be linked to the source data sheet.  If you add data by rows to the source data sheet or edit data in existing rows, just right click on any cell in the result sheet and select Refresh.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • I wish to express my gratitude for your kind-heartedness supporting those who need help with in this content. Your real dedication to getting the solution all around was surprisingly important and have continually permitted individuals just like me to arrive at their ambitions. Your entire invaluable instruction denotes a whole lot to me and especially to my office colleagues. Best wishes; from each one of us.

    Reply

  • Thankyou very much for the post… it has really helped me solving the biggest problem for which i was facing.

    Just a little question, is there a way to do it on the same workbook?
    Or do we have to go for a new workbook to do this.

    Reply

  • Hi!! Can you help in this!!
    I have a sheet named “Sheet1” with many columns. i will upload the details through it. If error occurs in any record i need to move particular error records to another sheet with a conditional loop. could you suggest on it.?

    Reply

  • Hi there
    I want to filter data using excel,which have different 10 worksheet in one work book.I want to use date of Arrival which is the date range criteria and want to copy which have the same date in one worksheet called extract data.and the same date will be copied in one extract worksheet along with the name of the worktsheet.

    Reply

  • When I follow the steps I get the error “Too few parameters, expect 1”. I don’t know When I follow the steps I get the error “Too few parameters, expect 1”. I don’t know what I am doing wrong, but I need to get this done! Please help! :)what I am doing wrong, but I need to get this done! Please help!

    Reply

  • easy and well explained!

    I’m wondering is similar functionality exists if the data source is within the same Workbook? I have 2 worksheets with data sources and I want to use these as the sources for a number of other summary tables on another worksheet, taking advantage of the filter criteria that your worked through example allows. I need this to be dynamic as the user will not be very tech savy

    Appreciate any advice

    Reply

    • Hi,

      The reason you face the problem is that the Table and Named range that you have defined on each worksheet include blank rows. I suggest that you start with a clean slate and remove all named range and Tables. On each worksheet, restrict your named range and Table creation only till the range where your data is (which is row 2 on all worksheets in your case). now follow all steps.

      I tried this for two worksheets and it worked just fine.

      Reply

  • Will this work if I keep the spreadsheet on a common SharePoint or Intranet webpage so all users can access and update the sheet?

    Reply

    • Hi,

      This technique should work as long as you can specify a path to where the file is stored. That location could either be a local machine or a network drive. If the file is stored on a SharePoint library, then you can synchronise that online folder to your local machine/network drive and then give the path to the file.

      Reply

  • Hello Ashish,

    I can complete the entire process until the end when I press finish and the dialog box doesn’t close. Please note I am using my own data set. Thank you.
    ~ C Wilkins

    Reply

  • Hi, I am hoping you may be able to help, I seem to be hitting a brick wall with no way to turn. I want an excel document to hold many sheets, the first sheet would be the master document and then information added here be filtered to the other sheets depending on the data inputted, is this possible?

    Reply

  • Hello,
    This was great for creating a workbook with data from another. For some reason, it isn’t updating the data I’ve changed on the Master to the other workbook. Any trouble shooting help you could give me?

    Connection String:
    DSN=Excel Files;DBQ=C:\Users\Employee\Desktop\Fert Dummy.xlsx;DefaultDir=C:\Users\Employee\Desktop;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

    Command Text:
    SELECT FertDummy.Name, FertDummy.Address, FertDummy.City, FertDummy.`Route Direction`, FertDummy.`Coordinate – North`, FertDummy.`Fert# – Spring`, FertDummy.`Fert – Spring – Bags Used`
    FROM `C:\Users\Employee\Desktop\Fert Dummy.xlsx`.FertDummy FertDummy
    WHERE (FertDummy.`Fert# – Spring`=’X’)
    ORDER BY FertDummy.City, FertDummy.`Coordinate – North` DESC

    Reply

  • For all rows in Workbook 1, if the cell in Column L has an “X” in it, I’d like to copy Columns A, B, C, E, L & S of that Row to Rows sorted First by Column C (Descending) and then Column E (Descending) in Workbook 2, Sheet 1. There are other connections that are variations of that on different Sheets in Workbook 2.

    The Master:
    https://www.dropbox.com/s/xojjintdlxfnhnk/Master%20Maintenance.xlsx?dl=0

    The Dummy:
    https://www.dropbox.com/s/pz09whyx5b4834m/Fert%20Dummy.xlsx?dl=0

    The Final:
    https://www.dropbox.com/s/7kuv4hhvggis979/2017%20Fertilization%20List.xlsx?dl=0

    Reply

  • I have Excel 2016. I’ve gone through the steps in this page. It worked great to create a new workbook from another, but when I change data on the master, it isn’t updating on the final workbook, when I press ‘Refresh All’ or refresh an individual cell.

    Reply

    • Hi,

      I cannot say why it is not working. Share the workbook with in which you have already tried the Power Query steps a.k.a. Get & Transform. I should be able to identify the reason once I see the M code.

      Reply

          • In your original post, I followed the steps under the “Solution for all versions of MS Excel”, but instead of having the base data dynamically filter to another sheet in the same workbook, I’ve had the data filter to another workbook. So, to answer your question, I think “Final Workbook” has the solution you are looking for, but it may be both “Master Workbook” and “Final Workbook”. “Master Workbook” has the base data, “Dummy Workbook” is the dummy (created in Steps 1-3) and “Final Workbook” is where the data is being dynamically filtered to (steps 4-11).

            Is it possible to do this, or do I have to filter to the same workbook?

          • I was under impression that the original post used Power Query.

            Ok, then. Thank you for your help!

          • Hi,

            The solution shared under the heading of “Solution for MS Excel 2010 and higher versions” is the Power Query solution and this can be tweaked to transfer the data to another workbook. The solution shared under the heading of “Solution for all versions of MS Excel” is the MS Query solution.

            If you are using the MS Query solution, then follow all steps mentioned in my original Blog article. At the very end, right click on the tab which carries the filtered dataset and Move it to another workbook. See if that solution works.

  • Hi Ashish,

    Thanks very much for the help! I was wondering if there is a way to do this exact same thing but instead of calling for a single value, call for a range between two values. For example, in your sheet, to automatically sort for quantity values between 10 and 20. Thank you!

    Reply

  • Can you point me to download link to the workbook which you used in your video/exercise?

    Also, power query link no longer is working (though google helped me on that).

    Thank you for suggesting this new way of doing things.

    Reply

      • 1st I couldn’t find the exercise file download link and ‘Power Query’ download link wasn’t working on MS site (though google helped me find PQ, but that didn’t work on my Office 2016).

        Then after your reply, I looked carefully and found the exercise/companion file. And this file helped me a lot, a lot. Because, when I opened the file in 2K16, I found that in this version, there was no need of PQ separately and its commands were clubbed in the ‘Data’ ribbon menu itself. And when I explored a bit, I was happily surprised that how much more useful PQ commands were.

        In order to help somebody else here, earlier (without PQ), whenever I added a new column to my source range/table, the query no way updated the same/target and I had to recreate a new query deleting the old sheet. But now, thru this command (Data> From Table) is wonderful and updates any structural changes in source data into target table. Thank you gentlemen. Thanks a lot.

        Reply

  • Hi Ashish, great post.

    Hi.

    Awesome Post!

    Each day is more and more common, to have the need to connect filtered data, to avoid all the extra information in your sheets, and the work to find just the ones that are really useful for you.
    So, with that in mind, I may have an Add-on, that you are going to like.
    Sheetgo does this filtered connection between different sheets. Can be between two, or multiple, and you can make the updates automatically, it really help you to save some valuable time. You can filter by a predefined filter or use SQL. Among other features that can be very helpful.

    would like to invite you to test it, and have your feedback on our add-on.
    http://www.sheetgo.com

    Regards, Mariana

    Reply

  • Hi, i’m getting error after step 9. Error is “Too few parameters. Expected 2.” please guide me

    Reply

  • Pfff why you have to save to a file location and get your query from there import it and blablabla, while you work in the same document. When you change the file location document name or whatever you can start over again. I am simply looking for a dynamic query to fill in and filter data from worksheet 1 to worksheet 2. Not with all complicated ways that if i want to transfer my document to another peros, another computer or want to rename it, i get troubles because the query is based on a document with a specific name in a specif folder and blablabla while the only thing it has tot do is refer from worksheet 1 to worksheet 2, no matter how i call the document or no matter on what device i use it or to whom i send it.

    Reply

  • Hi. If you maybe so kind to assist me with a challenge I am currently facing.
    I am trying to figure out how to automatically filter data coming from a drop down list(with vlookup) into anoter sheet and maybe automatically add the value of same items.

    Reply