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

Yay, it worked.

Thank you again.

Kind Regards

Paul

Answer 3

Hi HansV

sorry, my error - it doesn't work :(

It creates additional rows, but all with a zero and hasn't copied any of them over.

What did I do wrong?

=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 2024'!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}))

Thank you

Kind Regards

Paul