Feb 21 2019 07:27 AM
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!
Feb 21 2019 08:36 AM
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.
Feb 21 2019 09:36 AM
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:
Just copy/paste or drag the formulas down & you're all set.
Feb 22 2019 01:17 AM
Am unclear how to define the sheets and what formula to type? Thanks!
Feb 23 2019 12:05 AM
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!
Feb 23 2019 02:19 AM
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
Feb 25 2019 12:58 AM
Great thank you all so much for your help!
Mar 06 2019 03:16 AM
@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
Mar 06 2019 03:43 AM
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")