Cells displaced in excel spreadsheets

[jnbspace]

I have a couple of large spreadsheets which I use for tracking schools contacts and outreach visits from our Observatory. Those I have set up so that they have filters and selectable views. So for example in the same spreadsheet I have an enquiry view or an invoice view to reduce the data shown. I also have filters so that I can restrict the view to a selection of data; for example only future enquiries from primary schools etc.

So far so good and in principle it works except that some of the content gets displaced every so often. From a bit of googling it seems that it might be something to do with the filters. Is this a known issue? I have seen other people refer to to but not often and not yet seen a solution. In the example spreadsheet I have attached the travel information (distance and time) is regularly displaced from the customer information, as this feeds into the invoiced amount it makes the spreadsheet less than useful.

Help please?

[AliGW]

I have had a look at your spreadsheet. Where should I look and what should I do to see this phenomenon, please?

In the example spreadsheet I have attached the travel information (distance and time) is regularly displaced from the customer information, as this feeds into the invoiced amount it makes the spreadsheet less than useful.

[jnbspace]

The problem seems to occur when I sort by using any of the filters. So for example if I sort the spreadsheet by invoice number then not all columns are sorted. Instead it sorts columns A - AN but leaves AO and AP in their original order. Of course this then breaks the data.

[AliGW]

That's because those columns are not included in the filter. You need to add the filter to all columns you wish to sort, so remove the filer, then reselect the cells for the filter INCLUDING those two columns, and apply it again. Now when you sort all the necessary columns will be included.

[jnbspace]

Thankyou - That's really not how I would have expected it to work. Is there any way to make the filters apply to all columns? otherwise every time someone adds a column it is at risk of break the data

[JeteMc]

It appears that you can apply the filters to all columns.
I tested this by placing a series of numbers (1:998) in column XFD based on the sort order applied to the file attached to post #1,
removed the filter buttons (using filter on Data tab)
selected A12:XFD12,
applyed filters (again using filter on Data tab),
then resorted 'Newest to Oldest'.
It took a few seconds, however when it finished the numbers in column XFD had sorted as had the values in columns A:AP.
You may want to copy your data to a new spreadsheet before attempting, just to be safe.
Let us know if you have any questions.

[ChemistB]

If you set up your data as a table (Insert Table), then Excel will automatically include filters to any columns added (unless you separate the columns with one or more blank columns)