Forum Discussion
Damian_Dell
Jan 04, 2023Copper Contributor
Filter not working (monetary values)
Hi, y'all! Tis weird, but the most basic function of Excel (filtering values) is not working as it should.
I'm building a table with a colum for costs, and the other columns for descriptions, locations, hyperlinks, etc.
I just want to order my table by costs, smallest to biggest, and the filter should do this. However, it isn't working, it leaves everything in roller-coster/zigzag mode, alternating cheap and expensive.
All my costs values are in monetary values. Tries removing that and jsut use the number, same old S.
Wondering what the f I must be doing wrong. Any ideas?
Cheers,
D
- InsigAdminCopper Contributor
I am filtering on a value of 400 formatted as currency : R400.00. If I use the 'Equals' auto filter set to 400 it does not find the values. If I use the 'Between' 400 and 400 it finds it ! If I use the Equals again but set the value to R400.00 it finds it ? This must be an Excel thing.
- mathetesSilver Contributor
Well, one thought for starters: Filtering and Sorting are two different things. So I'm struck by your use of the word "filtering" when what you're describing as desired is clearly sorting. Are you, in other words, inadvertently using the wrong button or menu command?
If some of the values are text masquerading as values, that can cause difficulties. You seem to address that, and to know the difference. But depending on how the entries in your Cost column are sourced, is it possible that they're not all consistent?
Final question/suggestion: if the workbook in question doesn't contain confidential information, would it be possible for you to post a copy of it on OneDrive or GoogleDrive, and then paste a link here that grants edit access to it. That way we could have a look at the actual workbook.
- Damian_DellCopper ContributorHi, Mathetes, and thanks a lot for your interest!
Let's see if my link would work here:
https://greenworking-my.sharepoint.com/:x:/r/personal/damian_dellamico_greenworking_fr/Documents/Documents/01%20Office%20Management/Evenements/2023.01.26%20Petit-d%C3%A9jeuner%20de%20pr%C3%A9sentation.xlsx?d=wf370098120c44e2599a6d68675e0d844&csf=1&web=1&e=XaEPNH
Maybe it won't work. I'm based in France, Europe, and my table is in French (including my costs in Euros).
Yes, I'm aware of the nuance between sorting and filtering. While indeed I just want to sort it through my Costs column only (column A, aka "PRIX TTC", in Euros), my managers may want to sort the data by name of the venues (column C) or their location, and that's why I thought of simply adding a filter and let each one sort it out (or filter it out) as they like best. Perhaps this may result in confusion to you?
I also tried with both ways (only sorting, and only filtering) alternatively. All my values are monetary values, there are no hidden apostrophes, text, or whatever. It must be something very stupid, but so far I'm failing (miserably) to see it. Hence the idea that someone else might be able to see at once what I've been failing to see since a couple of hours ago.- mathetesSilver Contributor
I'm not authorized to use that sharepoint resource. (I suspect it's specific to your company.)
Is it possible to put a copy, so long as there's no confidential data in it, in a more "public" space like GoogleDrive or OneDrive?