Using Excel Filter Query

Question

Hi

I am trying to prepopulate a spreadsheet with experience dates, based on certain criteria in another spreadsheet. As an example, I want all people who are having an experience on the 17th May 2024 to go to a spreadsheet page on 17 May 2024. I have set up an "=FILTER" function in the new spreadsheet and whilst this works if I choose a column which includes a name, it fails with the warning #VALUE when I choose the date column. I have tried various forms of the setting for date such as 17/5/24, 17 May 2024 an even the data code 45029. All fail.

Can someone please help and either let me know what I need to do to solve the problem or what I need to do to create a workaround?

Thank you in advance of your help.

Kind Regards

Paul

Answer 1

Shouldn't that be

=VSTACK(FILTER(FILTER('[Orders and Payments Main.xlsx]Orders 2024'!A:AW,'[Orders and Payments Main.xlsx]Orders 2024'!AM:AM=DATE(2024,4,10),""),{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}),FILTER(FILTER('[Orders and Payments Main.xlsx]Orders 2023'!A:AW,'[Orders and Payments Main.xlsx]Orders 2023'!AA:AA=DATE(2024,4,10),""),{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}))

Answer 2

Hi HansV

Thankyou for your reply and support, it worked. Superstar.

Kind Regards

Paul

Answer 3

If you have Microsoft 365, you can use the VSTACK function to combine the FILTER expressions for the two sheets:

=VSTACK(FILTER(... for 2023 ...), FILTER(... for 2024 ...))

Answer 4

Hi HansV

thats brilliant, thank you.

I have dates on 2 spreadsheets - 2024 and 2023. Is there any way of incorporating both onto the same spreadsheet?

Thanks again

Kind Regards

Paul

Answer 5

Instead of "10 April 2024", use DATE(2024, 4, 1)