Forum Discussion
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...
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.
Am unclear how to define the sheets and what formula to type? Thanks!
