Consolidate Values from different Tabs to One Tab

[B5rocksass]

Is it possible to pull values from calculations on different tabs into one table on one tab? I would like to run a calculation (supplied by Fluff and JamesCanale from another thread: Please help with IF index formula) on each of the individual tabs, but have the totals be reported into one table in a single consolidation tab.

If it is also possible to have the table automatically omit the weekends, that would be ideal. Unfortunately, i don't know how to write a formula to carry the dates down that way.

Confirmations Time Frame Averages.xls
	A	B	C	D
1	DATE	Abby	Lenox	Subtotal
2	Fri, Jan 1, 2021			0
3	Sat, Jan 2, 2021			0
4	Sun, Jan 3, 2021			0
5	Mon, Jan 4, 2021			0
6	Tue, Jan 5, 2021			0
7	Wed, Jan 6, 2021			0
8	Thu, Jan 7, 2021			0
9	Fri, Jan 8, 2021			0
10	Sat, Jan 9, 2021			0
11	Sun, Jan 10, 2021			0
12	Mon, Jan 11, 2021			0
13	Tue, Jan 12, 2021			0
14	Wed, Jan 13, 2021			0
15	Thu, Jan 14, 2021			0
16	Fri, Jan 15, 2021			0
17	Sat, Jan 16, 2021			0
18	Sun, Jan 17, 2021			0
19	Mon, Jan 18, 2021			0
20	Tue, Jan 19, 2021			0
21	Wed, Jan 20, 2021			0
22	Thu, Jan 21, 2021			0
23	Fri, Jan 22, 2021			0
24	Sat, Jan 23, 2021			0
25	Sun, Jan 24, 2021			0
26	Mon, Jan 25, 2021			0
27	Tue, Jan 26, 2021			0
28	Wed, Jan 27, 2021			0
29	Thu, Jan 28, 2021			0
30	Fri, Jan 29, 2021			0
31	Sat, Jan 30, 2021			0
32	Sun, Jan 31, 2021			0
33				Total
34				0
JANUARY TOTALS

Cell Formulas
Range	Formula
D2:D32	D2	=SUM(B2:C2)
D34	D34	=SUM(D2:D32)

 

[sandy666]

maybe use Pivot Table for multi range ==> Alt+D+P

 

[JamesCanale]

For the dragging weekdays only, put this in A3 and drag it down:

=WORKDAY(A2,1)

 

[B5rocksass]

That's great James! Thank you for that.

 

[B5rocksass]

I need a bit more help with this. I need to somehow place the values I would get from each Tab into a Compilation Table. This will help to identify a monthly trend. Is it possible to pull that info into this table without a pivot?

Confirmations Time Frame Averages.xls
	A	B	C	D	E	F	G	H
1	COMPILATION TABLE					TAB 1-1 example
2	Date	Abby	Lenox	Subtotal		Date	Name	Yes/No
3	1/1/21			0		11/20/20	Abby	Y
4	1/4/21			0		11/13/20	Lenox	N
5	1/5/21			0		11/13/20	Abby	N
6	1/6/21			0		11/13/20	Lenox	Y
7	1/7/21			0		01/06/21	Abby	Y
8	1/8/21			0		01/13/21	Lenox	N
9	1/11/21			0		10/30/20	Lenox	N
10	1/12/21			0		02/05/21	Abby	N
11
12				Total			Name	Qty (N)
13				0			Abby	1
14							Lenox	2
Formula Example

Cell Formulas
Range	Formula
A4:A10	A4	=WORKDAY(A3,1)
D3:D10	D3	=SUM(B3:C3)
D13	D13	=SUM(D3:D12)
H13:H14	H13	=COUNTIFS(G$3:G$10,G13,H$3:H$10,"N",F$3:F$10,"<="&WORKDAY(TODAY(),-2))

 

[B5rocksass]

I tried to do this myself, but the best i could come up with was the below formula. Copy and pasting the data into the sheets for the succeeding months into the exact same tabs and changing the tab names to line up with the days of that month. Ugh. There's got to be a better way. Please help!

=COUNTIFS('1-1'!F:F,$B$1,'1-1'!G:G,"N",'1-1'!C:C,"<="&WORKDAY(TODAY(),-2))

 

[B5rocksass]

maybe use Pivot Table for multi range ==> Alt+D+P

Can you tell me more about this?

 

[sandy666]

Consolidate multiple worksheets into one PivotTable