If, sort, filter issue

[cardak]

I am trying to create a workbook/spreadsheet to capture our mileage for tax purposes. I know there are several "apps" out there that accomplish this as well as several "Excel Templates", but they don't really capture what I am looking for. I've attached a simple workbook that captures data on one spreadsheet and I am attempting to create a "report" on another spreadsheet that will produce a printable report based on certain criteria (vehicle and purpose). I was trying to write a "IF(SORT(FILTER) formula to capture the necessary data but keep getting an error that I have too many variables. The formula that I have been trying is: IF($B3$='Vehicle Mileage Log'!A6:A250,SORT(FILTER('Vehicle Mileage Log'!B:H,'Vehicle Mileage Log'!A:A=$B$3),4,False,5,False,6,False),SORT(FILTER('Vehicle Mileage Log'!B:H,'Vehicle Mileage Log'!A:A=$B$3),4,True,5,True,6,True))"),"")

Any help would be greatly appreciated, for information I am using Excel 2021.

[BadlySpelledBuoy]

Hi and welcome

This is a guess as you've not shown us what your expected results are.

In A8

Please Login or Register  to view this content.

BSB

[TMS]

Does this do what you want:

=IFERROR(SORT(CHOOSECOLS(FILTER('Vehicle Mileage Log'!A6:K10,('Vehicle Mileage Log'!A6:A10=B3)*('Vehicle Mileage Log'!J6:J10=B4),"no data"),1,3,4,5,6,7,8,10,11),{4,5,6},{1,1,1}),"no data") 

[cardak]

Thanks for the quick reply! This formula does not appear to fix. What I am trying to obtain are all of the "log entries" in "Vehicle Mileage Log" that would meet the criteria selected in "B3 and B4" of the "Assignment History Report".


cardak

[BadlySpelledBuoy]

That's exactly what it does do, in your sample file. You'd need to adjust the ranges for your real file.

But if you disagree, then show us what your expected results are so we're not just guessing.


BSB

[TMS]

Ditto. Though mine needs a slight tweak.

Please Login or Register  to view this content.

Instead of:

Please Login or Register  to view this content.

[cardak]

All, thanks for the input, I was able to resolve the issue (I had neglected to format the data as a table)!

[TMS]

You're welcome.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.