Using text-version of sheet names in formulas?

Churchy LaFemme

Board Regular
Joined
Sep 22, 2010
Messages
129
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
190
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
5,063
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
5,063
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
5,063
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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"))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,957
Messages
5,767,308
Members
425,403
Latest member
MellieD

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