VSTACK data from multiple sheets and filter results for specific dates

[newxl]

Creating an excel spreadsheet that will have multiple users inputting data in specific sheets. I’m then planning on using VSTACK to combine the data from all their sheets to one sheet and have a "from" and "to" date search box to allow a user to view rows that fall in between those specific dates and also a search box to search all the rows.

Once I learned about VSTACK it seemed to be faster than Power Query as there is no need for the users to hit refresh. I’m trying to make it as simple as possible for the end-user.

I thought I could have VSTACK in one sheet with all the data combined and then use the FILTER function in another sheet where I'll have the search boxes. I can't seem to make the functions work. Getting VALUE error. CALC error.

One sheet (DATA) where I'm calling all the various sheet/table data where Column A are Dates in the format, 14-APR-24:

=VSTACK(Table1,Table2,Table3,Table4,Table5,Table6)

Then use FILTER in another sheet using that VSTACK data.

=FILTER(Data,(Data!A2:A20000>=N1)*(Data!A2:A20000<=O1))

I haven't been able to get it to work.

Thanks.

 

[Alex Blakenburg]

Do you mean something like this:

20240415 Vstack Filter newxl.xlsx
	A	B
1	Date From	19/04/2024
2	Date To	11/05/2024
3
4
5
6	Date	Value
7	20/04/2024	150
8	30/04/2024	160
9	10/05/2024	170
10	20/04/2024	250
11	30/04/2024	260
12	10/05/2024	270
13	20/04/2024	350
14	30/04/2024	360
15	10/05/2024	370
Summary

Cell Formulas
Range	Formula
A7:B15	A7	=FILTER(DATA!A2#,(CHOOSECOLS(DATA!A2#,1)>=$B$1)*(CHOOSECOLS(DATA!A2#,1)<=$B$2),"")
Dynamic array formulas.

Where the Data VStack sheet looks like this:

20240415 Vstack Filter newxl.xlsx
	A	B
1	Date	Value
2	10/04/2024	140
3	20/04/2024	150
4	30/04/2024	160
5	10/05/2024	170
6	20/05/2024	180
7	10/04/2024	240
8	20/04/2024	250
9	30/04/2024	260
10	10/05/2024	270
11	20/05/2024	280
12	10/04/2024	340
13	20/04/2024	350
14	30/04/2024	360
15	10/05/2024	370
16	20/05/2024	380
DATA

Cell Formulas
Range	Formula
A2:B16	A2	=VSTACK(Table1,Table2,Table3)
Dynamic array formulas.

 

[newxl]

Perfect, thanks!

I’m running into a problem. How can I allow users to add info.(in the next column) that will be saved and will stay locked with that filtered or VSTACK row? I have users that need to add data to rows based on the filtered results. How can this be done?