# Using text-version of sheet names in formulas?

#### Churchy LaFemme

##### Board Regular
I am updating a worksheet with a weekly data run that is garbage. I have 72 tabs based on the weeks' data and a summary tab showing the hours people worked on a specific type of issue. I need to create a new summary with correct data.

Just found out that the person who did the work was reusing their old, messed up workbooks instead of starting from the corrected version they were sent by the person in charge of the report.

Individual sheets with weekly data are correct. The summary tab is full of mistakes. (Seriously - some of the cells contain a formula like =56.7+18-93 when none of these values appear on the sheet for that week - not as row totals, not as sums. It's just bizarre!)

What I have to do is create a table that sums values from each sheet based on a specific value such as the person's id number (and check totals to make sure no row names changed from tab to tab.)

The formula is pretty simple -
=SUMIF('Sheet1'!\$A:\$A,Reconcile!C\$2,'Sheet1'!\$B:\$B)
=SUMIF('Sheet72'!\$A:\$A,Reconcile!C\$2,'Sheet72'!\$B:\$B)

But typing it over and over and over and over and over is not.

I have all the sheet names in column A of the Reconcile sheet. Is there way to use that sheet name in a formula so that Excel understand this is not a text string but a sheet name?

=SUMIF('Sheet1'!\$A:\$A,Reconcile!C\$2,'Sheet1'!\$B:\$B)

What I want is a formula that says the sumif sheet values come from text in column A, and the criteria is a value in the header column.

Can this be done?

As a further complication, I am probably going to find that some of the sheets are not set up the same way, so I'll be looking at column B for the criteria and column C for the sum. If that wasn't true, I try dumping it all into access and giving that a whirl.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Dermot

##### Board Regular
There is an ancient kludge to do it, but you'll need to make sure all the sheets use the same columns (I would just adjust the sheets that are different to make them the same)

Create a range name (say SheetName) with this formula
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,256+NOW())
this will get the Nth sheet name for a specified number N

Then you can put a sequence of numbers along the top of the columns, eg if your summary sheet is the first sheet and all the 72 data sheets are in sheets 2 to 73, then create a sequence 2,3,4,....73 along the columns.

Then your formula for each column is (using column F as an example, and assuming the number sequence (above) is in row 1)
F2 =SUMIF(INDIRECT("'"&INDEX(SheetName,F1)&"'!\$A:\$A"),Reconcile!C\$2,INDIRECT("'"&INDEX(SheetName,F1)&"'!'Sheet1'!\$B:\$B"))
This formula picks up the number in F1, uses it to get the sheet name, INDIRECT turns this into a range, and then the formula works normally
So if F1 is 3, this will pick up data from the 3rd sheet in your workbook

This will give you a set of columns, one for each of the 72 sheets, and you can add them up

#### Dave Patton

##### Well-known Member
I just prepared a mini example.

1. ensure the layout of each sheet is the same.
2. my example has three data sheets
3. The list of relevant sheets is named Sheets

3d Sumif 2021.xlsm
ABC
1
2dgp1000
3JWW2500
4
Week1

3d Sumif 2021.xlsm
ABCDEF
1NameSheets
2dgp3000Week1
3JWW8500Week2
4Week3
5
Summary
Cell Formulas
RangeFormula
B2:B3B2=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A2:A20"),A2,INDIRECT("'"&Sheets&"'!"&"B2:B20")))
Named Ranges
NameRefers ToCells
Sheets=Summary!\$F\$2:\$F\$4B2:B3

#### Dave Patton

##### Well-known Member
There are VBA subs on this forum that will list all the sheets.
I can post one version if you need it.

#### Dave Patton

##### Well-known Member
If you want to look at each sheet separately, look at the following

3d Sumif 2021.xlsm
NOP
1Sheets
2Sheet112,000.0010,000.00
3Week11,000.003,000.00
4Week21,000.003,000.00
Summary
Cell Formulas
RangeFormula
O2O2=SUMIF(INDIRECT("'"&N2&"'!"&"A2:A200"),\$C\$2,INDIRECT("'"&N2&"'!"&"B2:B200"))
P2P2=SUMIF(INDIRECT("'"&N2&"'!"&"A2:A200"),C3,INDIRECT("'"&N2&"'!"&"B2:B200"))
O3:O4O3=SUMIF(INDIRECT("'"&N4&"'!"&"A2:A20"),\$A\$2,INDIRECT("'"&N4&"'!"&"B2:B20"))
P3:P4P3=SUMIF(INDIRECT("'"&N4&"'!"&"A2:A20"),\$A\$3,INDIRECT("'"&N4&"'!"&"B2:B20"))

Replies
6
Views
552
Replies
2
Views
85
Replies
1
Views
209
Replies
8
Views
153
Replies
11
Views
551

1,195,855
Messages
6,011,973
Members
441,658
Latest member
Carlos O

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