Filtering in Excel

Filtering in Excel

Didn't find your answer?

 

Could you please help?

I have a created a large table in Excel 2007. If I try to filter the data by different criteria (Columns) ,it does work not in all cases, i.e. it works on some criteria (Columns) filtering, but not in other criteria (Columns). I know it works in all cases if any cell does not have a formula on it (Only absolute figures).

For instance:

I have column A (customer name), Column B (Sales men Name), Column C (Region), Column D (Product Type), Column E, (Exchange Rate), Column F (Value in Foreign Currency), Column G, (Value in GBP).

Column F, Value in Foreign Currency has a formula, e.g. Product type times % age. Column G has also a formula and so on.

Filtering seems to work by columns, A and B, but as soon as I filter it by value, it does not cope. Some cells pick up the wrong rows. For e.g., if I have A1, B1 C1, and if C1 is a formula linked to B1, and A1, when I filer the data, I expected C1 always linked to B1 and A1, even if C1 is now in row 99. Or is it the case that to filter in excel, cell values should only be fixed, not linked to formula? Excel does not handle filtering if a cell is linked by a formula

I have also tried not to use a table, but normal excel range. This handles the filtering better than a table, i.e. few errors when I filter the data, but still has some errors. I do not know where I am going wrong.

Any suggestion and comment would be much appreciated.

Replies (1)

Please login or register to join the discussion.

avatar
By gsgordon
31st May 2010 13:22

Should work!

As far as I know, filtering works fine regardless of whether cells contain formulae.

I suggest you run tests with only one column containing formulae. Try it with each different column that contains formulae. You may find that one column is a problem and can investigate further. If not, try it with pairs of columns, and then ....

Although I believe it should not matter whether you use a Table, I would initially try the above with the table converted to a range.

There can be issues if a column contains cells that are formatted differently, e.g. some as text and others as numbers. You could check that quickly by selecting all the data and formatting it as (say) Number with 2 decimal places.

Thanks (0)