Consolidate Values from different Tabs to One Tab

B5rocksass

Board Regular
Joined
Jan 10, 2017
Messages
56
Office Version
  1. 2016
Platform
  1. Windows
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.

1610638442633.png


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
ABCD
1DATEAbbyLenoxSubtotal
2Fri, Jan 1, 20210
3Sat, Jan 2, 20210
4Sun, Jan 3, 20210
5Mon, Jan 4, 20210
6Tue, Jan 5, 20210
7Wed, Jan 6, 20210
8Thu, Jan 7, 20210
9Fri, Jan 8, 20210
10Sat, Jan 9, 20210
11Sun, Jan 10, 20210
12Mon, Jan 11, 20210
13Tue, Jan 12, 20210
14Wed, Jan 13, 20210
15Thu, Jan 14, 20210
16Fri, Jan 15, 20210
17Sat, Jan 16, 20210
18Sun, Jan 17, 20210
19Mon, Jan 18, 20210
20Tue, Jan 19, 20210
21Wed, Jan 20, 20210
22Thu, Jan 21, 20210
23Fri, Jan 22, 20210
24Sat, Jan 23, 20210
25Sun, Jan 24, 20210
26Mon, Jan 25, 20210
27Tue, Jan 26, 20210
28Wed, Jan 27, 20210
29Thu, Jan 28, 20210
30Fri, Jan 29, 20210
31Sat, Jan 30, 20210
32Sun, Jan 31, 20210
33Total
340
JANUARY TOTALS
Cell Formulas
RangeFormula
D2:D32D2=SUM(B2:C2)
D34D34=SUM(D2:D32)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
maybe use Pivot Table for multi range ==> Alt+D+P
 

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
449
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
For the dragging weekdays only, put this in A3 and drag it down:
Excel Formula:
=WORKDAY(A2,1)
 

B5rocksass

Board Regular
Joined
Jan 10, 2017
Messages
56
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGH
1COMPILATION TABLETAB 1-1 example
2DateAbbyLenoxSubtotalDateNameYes/No
31/1/21011/20/20AbbyY
41/4/21011/13/20LenoxN
51/5/21011/13/20AbbyN
61/6/21011/13/20LenoxY
71/7/21001/06/21AbbyY
81/8/21001/13/21LenoxN
91/11/21010/30/20LenoxN
101/12/21002/05/21AbbyN
11
12TotalNameQty (N)
130Abby1
14Lenox2
Formula Example
Cell Formulas
RangeFormula
A4:A10A4=WORKDAY(A3,1)
D3:D10D3=SUM(B3:C3)
D13D13=SUM(D3:D12)
H13:H14H13=COUNTIFS(G$3:G$10,G13,H$3:H$10,"N",F$3:F$10,"<="&WORKDAY(TODAY(),-2))
 

B5rocksass

Board Regular
Joined
Jan 10, 2017
Messages
56
Office Version
  1. 2016
Platform
  1. Windows
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))

1610719042390.png
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,609
Messages
5,765,408
Members
425,284
Latest member
fishymuffin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top