Share via

Excel Advanced Filter

Anonymous
2020-06-15T17:31:08+00:00

I am having a problem getting this to work. 

In a simple example I copied from a help item in it is fine, so I know how to use the function.

I have a large spreadsheet in which I want to extract all the rows in which the column "Code" equals a given value.

I want to store the result in a separate sheet in the spreadsheet from the source sheet.

I highlight the data in the source file, click first on Data then Advanced Filter, and put in the Criteria Range.

Tick “Copy to another location”, then put in the location as the separate sheet.

You get the useful message “You can only copy filtered data to the active sheet”

So what is the point of an option to copy to another location.

I then thought I would copy into the source sheet, replacing the existing data, so avoiding the above problem.

I get the useful message “The extract range has a missing or invalid field name”

The source sheet, the criteria, and the destination sheet all have the same colun names in the same order.

Changing the range names to Col1 Col2 etc makes no difference (just in case the names I use clash with some system name).

Any advice please?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-16T11:31:43+00:00

    I have discovered the answerto Advance Filter not working.

    To work, the Input range, criteria range and output range must all be on the same spreadsheet.

    Also the column headings for all 3 must be the same.

    And the ranges for input and output must all start with the column headings

    Then it works.

    I had the criteria range and the output range on a different sheet in the spreadsheet to the input file.

    The strange thing is that I have older spreadsheets which used to work fine with separate sheets.

    Now they do not.

    However I had saved the workings of the Advanced Find in a macro.

    The macro works.

    But  if I try to run the Advanced FInd manually, with its different source and output sheets, I get the message "You can only copy data to the active sheet."

    I suspect Microsoft have changed the function. It has caused me hours of work trying to make my spreadsheet work, and help from Microsoft was no use.

    Typical action - I have found lots of changes after upgrades where things that worked fine stop working and in particular when saving Excel spreadsheets I now often get the message that they are corrupt and will be recovered, but then the Excel program exits.  This on several different PCs, all with up to date Windows 10 and Office 365 Professional.

    10+ people found this answer helpful.
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2020-06-16T00:14:52+00:00

    Hi,

    Click on any source in the destination worksheet and then go to Data > Advanced.

    Hope this helps.

    8 people found this answer helpful.
    0 comments
  3. Anonymous
    2020-06-17T03:07:16+00:00

    Hi Barry,

    Sorry for the inconvenience caused at your end.

    After reading the complete description provided by you, I’ve referred the official article: Filter by using advanced criteria, where Copy to another location stated “To filter the list range by copying rows that match your criteria toanother area of the worksheet”. But I do believe your statement about it used to work previously and, in this situation,, we request you add your feedback to related developing team via Excel application as it’s the best way to report it.

    Refer to this article: How do I give feedback on Microsoft Office?

    Thanks for understanding!!

    Chitrahaas

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2020-06-15T18:20:01+00:00

    To:  BWP

    re:  copying filtered data

    Have you tried copying just the filtered data (Ctrl+C) and pasting it in the other sheet?

    '---

    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    2 people found this answer helpful.
    0 comments
  5. Anonymous
    2020-06-16T08:27:08+00:00

    This does not work.

    If I go to the destination sheet first, then Data Advanced, then change to the source sheet to get the input range, then return to the destination sheet to finish the Advace Filter command by clicking on Copy to another location in the destination sheet, I get the message "The extract range has a missing or invalid field name"

    1 person found this answer helpful.
    0 comments