Summarising the difference of 2 cells from 100 sheets to one sheet

Highlighted
Occasional Contributor

1) I have 100 sheets with exact same format, just different data

2) I need a summary page with 100 rows representing the 100 sheets,

column 1 - names of the 100 sheets

column 2 - will be the difference of the same 2 cells (F9-E9) from each sheet

column 3 - F10-E10

column 4 - F11-E11

 

How do i get it done with the simplest and fastest formula? Instead of manually clicking each sheet open? Thanks!

8 Replies
Highlighted

Using "Define Name", define "sheets" with the following  =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

 

In your first column use the  following formula   =INDEX(sheets,ROWS($A$1:$A1))  Copy this formula down the column until you get the last sheet name. 

 

In your second column use the following formula to get the answers you want from F9-E9 =INDIRECT($A1&"!F9")-INDIRECT($A1&"!E9")  Copy across to your other columns and change F9-E9 reference as required, then drag down the rows for all spreadsheets.

Highlighted

Assuming the sheets are all in the same workbook, there's a pretty simple method for getting a list of the sheet names. Personally, I thought method 2 from this article was easiest for this scenario: https://www.datanumen.com/blogs/3-quick-ways-to-get-a-list-of-all-worksheet-names-in-an-excel-workbo...

 

For performing your calculations, I'd initially used CONCATENATE to build the formulas which created the formulas correctly, but they didn't paste as expected. I think @Rich99's method using INDIRECT is far more efficient, so if your data started on row 2, the formulas would look like:

  •  F9-E9: =INDIRECT($A2&"!F9")-INDIRECT($A2&"!E9")
  • F10-E10: =INDIRECT($A2&"!F10")-INDIRECT($A2&"!E10")
  • F11-E11: =INDIRECT($A2&"!F11")-INDIRECT($A2&"!E11")

Just copy/paste or drag the formulas down & you're all set.

Highlighted

Am unclear how to define the sheets and what formula to type? Thanks!

Highlighted

Thanks so much, i managed to define the sheets!

 

But i have an issue with the index formula now... 

 

In the summary page cell

1. C4(accton) - i would need the accton sheet S10-T10

2. D4(accton) - i would need the accton sheet S11-T11

3. E4(accton) - accton sheet S12-T12

4. F4 (accton) - accton sheet S14-T14 (skip in one row)

.....

5. N4 (accton) - accton sheet S24-T24

 

5. C5 (airtac RMB) - airtac sheet S10-T10

.... 

till N5.... 

 

So I am a little confused how to use the Index function for this!

 

Thank you @sally365 @Rich99 

Summary PageSummary PageOne of the sheetsOne of the sheets

Highlighted

In C4 you may use formula

=INDEX(INDIRECT($B4 & "!S10:$S$24"),MATCH(C$3,Sheet2!$Q$10:$Q$24,0)) -
 INDEX(INDIRECT($B4 & "!T10:$T$24"),MATCH(C$3,Sheet2!$Q$10:$Q$24,0))

and drag it down and to the right

image.png

Sample file is attached

Highlighted

Great thank you all so much for your help!

Highlighted

@Sergei Baklan @sally365 @Rich99 Hey thanks for your help!

 

However, now i have a problem when I delete a sheet, then the whole summary page messes up. 

 

Where the name manager loses the formula saved of list sheets. 

 

Unsure what happened

Highlighted

In my variant to be more safe you may use

=IFERROR(INDEX(INDIRECT($B4 & "!S10:$S$24"),MATCH(C$3,INDIRECT($B4 & "!$Q$10:$Q$24"),0)) -
  INDEX(INDIRECT($B4 & "!T10:$T$24"),MATCH(C$3,INDIRECT($B4 & "!$Q$10:$Q$24"),0)), "no sheet")

image.png