Using text-version of sheet names in formulas?

[Churchy LaFemme]

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.

 

[Dermot]

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]

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
	A	B	C
1
2	dgp	1000
3	JWW	2500
4
Week1

3d Sumif 2021.xlsm
	A	B	C	D	E	F
1	Name					Sheets
2	dgp	3000				Week1
3	JWW	8500				Week2
4						Week3
5
Summary

Cell Formulas
Range	Formula
B2:B3	B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A2:A20"),A2,INDIRECT("'"&Sheets&"'!"&"B2:B20")))

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$F$2:$F$4	B2:B3

 

[Dave Patton]

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

 

[Dave Patton]

If you want to look at each sheet separately, look at the following

3d Sumif 2021.xlsm
	N	O	P
1	Sheets
2	Sheet1	12,000.00	10,000.00
3	Week1	1,000.00	3,000.00
4	Week2	1,000.00	3,000.00
Summary

Cell Formulas
Range	Formula
O2	O2	=SUMIF(INDIRECT("'"&N2&"'!"&"A2:A200"),$C$2,INDIRECT("'"&N2&"'!"&"B2:B200"))
P2	P2	=SUMIF(INDIRECT("'"&N2&"'!"&"A2:A200"),C3,INDIRECT("'"&N2&"'!"&"B2:B200"))
O3:O4	O3	=SUMIF(INDIRECT("'"&N4&"'!"&"A2:A20"),$A$2,INDIRECT("'"&N4&"'!"&"B2:B20"))
P3:P4	P3	=SUMIF(INDIRECT("'"&N4&"'!"&"A2:A20"),$A$3,INDIRECT("'"&N4&"'!"&"B2:B20"))