• 411K Members
• 5,647 Online
• 466K Conversations

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

Occasional Contributor

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

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

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

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.

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

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.

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

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

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

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 PageOne of the sheets

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

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

Sample file is attached

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

Great thank you all so much for your help!

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

@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

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

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")```

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies