# Consolidate Values from different Tabs to One Tab

#### B5rocksass

##### Board Regular
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
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
maybe use Pivot Table for multi range ==> Alt+D+P

#### JamesCanale

##### Active Member
For the dragging weekdays only, put this in A3 and drag it down:
Excel Formula:
``=WORKDAY(A2,1)``

#### B5rocksass

##### Board Regular
That's great James! Thank you for that.

#### B5rocksass

##### Board Regular

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
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

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

#### sandy666

##### Banned - Rules violations

Replies
1
Views
413
Replies
11
Views
234
Replies
1
Views
262
Replies
6
Views
242
Replies
2
Views
249

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

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.

### Which adblocker are you using?

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

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