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 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!

excel
Macros and VBA

8 Replies

  • 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

  • Rich99's avatar
    Rich99
    Iron Contributor
    Feb 21, 2019

    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.

Resources