Advanced filter is messing with data

[Pauline V.]

Help!

I have two workbooks. One contains a list of data (values), the other one does the calculations. I copy data from the list to the other workbook, using an advanced filter. However, the advanced filter somehow changes my data! It changes the numbers, eventhough there is no formula or anything in the list! Is this just a funny Excel thing???

 

[Norie]

Changes it how?

 

[Pauline V.]

It removes data from one column, and changes numbers in an other column!

 

[Norie]

Pauline

That's a bit vague.

I think we need more information to help you.

 

[Pauline V.]

And that is my entire problem! I have never experienced something as vague. When I use the advanced filter to filter the list in place everything is fine. However when I use the copy to other location option is when this strange thing occurs.

I can not post the entire spreadsheet as it contains 103 columns and over 30000 lines.

The sheet is part of a forecasting tool. The columns where data appears or disappears when I use the filter are input columns, not all cells contain a value. The columns in which data is being changed when I apply the filter are next to these input columns.
There are no formulas in this workbook but all the numbers are based on formulas, and calculations which are done in another workbook.

It is so strange, I don't even know how to proparly explain what happens!

Is there perhaps a limit to the number of columns and lines you can succesfully apply an advanced filter to? As far as I can see now, these changes only happen in the last columns of my list.

Thanks

 

[Datsmart]

Advanced Filter to another location should not alter the original data at all.

This link shows steps for advanced filtering:
http://www.contextures.com/xladvfilter01.html

Filtering data with more than 1,000 unique items could be your propblem. AutoFilter only references the first 1,000 unique items.

 

[Pauline V.]

Yes must be the over 1,000 rule then. Will have to think of another way of extracting the data.

Thanks,
Pauline