Forum Discussion

heatherlimyirong97_'s avatar
heatherlimyirong97_
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
sally365's avatar
sally365
Brass Contributor
Feb 21, 2019

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-workbook/

 

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.

  • heatherlimyirong97_'s avatar
    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's avatar
      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

      • heatherlimyirong97_'s avatar
        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

Resources