Forum Discussion

Copper Contributor

Feb 21, 2019

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 diffe...

excel

Macros and VBA

heatherlimyirong97_

Copper Contributor

Feb 23, 2019

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

SergeiBaklan

Diamond Contributor

Feb 23, 2019

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

100sheets.xlsx14 KB

heatherlimyirong97_
Copper Contributor
Mar 06, 2019
SergeiBaklan 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
- SergeiBaklan
  Diamond Contributor
  Mar 06, 2019
  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")
  
  100sheets.xlsx14 KB
heatherlimyirong97_
Copper Contributor
Feb 25, 2019
Great thank you all so much for your help!