Multi-tab excel, transporting data across tabs to a summary tab

[usnapoleon]

Hi everyone
I have a monthly spreadsheet, with a tab for each day of the month. Every tab has this:

00 Destination Credit Tracking Log (Blank).xlsx
	N	O	P
6	Owner Name	Reservation #	Voucher Amount ($)
7
8
9
10
11
12
13
04.01.24

What I'm looking to do is have a summary sheet, that copies the data (if any) from each tab and creates a visual list.

So maybe on tab 1, we have this:

00 Destination Credit Tracking Log (Blank).xlsx
	N	O	P
6	Owner Name	Reservation #	Voucher Amount ($)
7	example tab 1	2222	$50.00
8
9
04.01.24

And on tab 2 we have this:

00 Destination Credit Tracking Log (Blank).xlsx
	N	O	P
6	Owner Name	Reservation #	Voucher Amount ($)
7	example tab 2	33333	$6.00
8	example tab 2	44444	$8.00
9
10
04.02.24

The summary would display this:

00 Destination Credit Tracking Log (Blank).xlsx
	N	O	P
6	Owner Name	Reservation #	Voucher Amount ($)
7	example tab 1	2222	$50.00
8	example tab 2	33333	$6.00
9	example tab 2	44444	$8.00
10
11
Summary

How can I formulate this for 31 tabs for a month's data?

 

[awoohaw]

this requires all the daily worksheets to be uninterrupted by any other worksheet. This also only does 3 months, as the workbook I built it with I didn't want to add 28 more worksheets. You can adjust as needed.

Book2
	N	O	P
1
2
3
4
5
6	Owner	Reservation	Voucher
7	Example 1	Example 1 reservation number	example 1 Voucher
8	Example 2.1	Example 2.1 reservation number	Example 2.1 Voucher
9	Example 2.2	Example 2.2 reservation number	Example 2.2 Voucher
10	Example 3.1	Example 3.1 reservation number	Example 3.1 Voucher
11	Example 3.3	Example 3.2 reservation number	Example 3.2 Voucher
12	Example 3.3	Example 3.3 reservation number	Example 3.3 Voucher
Summary

Cell Formulas
Range	Formula
N7:P12	N7	=LET(Data,VSTACK('04.01.24:04.03.24'!$N$7:$P$20),column1,TAKE(Data,,1),output,FILTER(Data,column1<>0,""),output)
Dynamic array formulas.

 

[usnapoleon]

this requires all the daily worksheets to be uninterrupted by any other worksheet. This also only does 3 months, as the workbook I built it with I didn't want to add 28 more worksheets. You can adjust as needed.

Book2
	N	O	P
1
2
3
4
5
6	Owner	Reservation	Voucher
7	Example 1	Example 1 reservation number	example 1 Voucher
8	Example 2.1	Example 2.1 reservation number	Example 2.1 Voucher
9	Example 2.2	Example 2.2 reservation number	Example 2.2 Voucher
10	Example 3.1	Example 3.1 reservation number	Example 3.1 Voucher
11	Example 3.3	Example 3.2 reservation number	Example 3.2 Voucher
12	Example 3.3	Example 3.3 reservation number	Example 3.3 Voucher
Summary

Cell Formulas
Range	Formula
N7:P12	N7	=LET(Data,VSTACK('04.01.24:04.03.24'!$N$7:$P$20),column1,TAKE(Data,,1),output,FILTER(Data,column1<>0,""),output)
Dynamic array formulas.

That is REALLY interesting. Normally I'll dissect a formula and try to backwards engineer it so I can learn from it, but this has waaay too many new things I have not encountered before, haha. What was also cool is I just put it in the single cell N7 on the summary and it automatically populates the next 2 columns with data, and continues the trend down to the next row. Mind... blown... wow.

Thank you!!!

 

[awoohaw]

My pleasure. Happy to help. Best Wishes!