{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Consolidated","START","Sheet (1)","Sheet (2)","Sheet (3)","Sheet (4)","END","Sheet (5)"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":1,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Consolidated","index":0,"n_rows":64,"n_cols":5,"rows_kept":64,"cut":false,"hidden":false,"merged":[]}
[]
[null,"So Many Worksheets "]
[]
[null,"Using Functions Across Multiple Sheets:"]
[]
[null,"Use an Asterisk"]
[null,"Formula Syntax",null,"=SUM('*'!C6)"]
[null,"Formula Output",null,{"$f":"SUM('Sheet (1):Sheet (5)'!C6)","v":140}]
[]
[null,"Use START:END"]
[null,"Formula Syntax",null,"=SUM(START:END!C6)"]
[null,"Formula Output",null,{"$f":"SUM(START:END!C6)","v":40}]
[]
[null,"Name of the Worksheet in a Cell:"]
[]
[null,"=MID + =CELL Functions",null,null," "]
[null,"Formula Syntax",null,"=MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,50)"]
[null,"Formula Output",null,{"$f":"MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,50)","v":"Consolidated"}]
[]
[null,"=CELL Function"]
[null,"Formula Syntax",null,"=CELL(\"filename\",A1)"]
[null,"Formula Output",null,{"$f":"CELL(\"filename\",A1)","v":"C:\\Users\\Peter Lynch\\Desktop\\[Working Across Sheets.xlsm]Consolidated"}]
[]
[null,"Reference Data on a Specific Worksheet:"]
[]
[null,"=INDIRECT Function"]
[null,"Formula Syntax",null,"=INDIRECT(\"'\"&[REF CELL WITH WORKSHEET NAME]&\"'\"&\"!C6\")"]
[]
[null,"Formula Syntax",null,"=INDIRECT(\"'\"&\"Sheet (1)\"&\"'\"&\"!C6\")"]
[null,"Formula Output",null,{"$f":"INDIRECT(\"'\"&\"Sheet (1)\"&\"'\"&\"!C6\")","v":10}]
[]
[null,"Dynamic Version"]
[null,"Formula Syntax",null,"=INDIRECT(\"'\"&\"Sheet (\"&ROW(A1)&\")\"&\"'\"&\"!C6\")"]
[null,"Formula Output",null,{"$f":"INDIRECT(\"'\"&\"Sheet (\"&ROW(A1)&\")\"&\"'\"&\"!C6\")","v":10}]
[]
[null,"Example w Dynamic Version of =INDIRECT"]
[]
[null,1,null,{"$f":"INDIRECT(\"'\"&\"Sheet (\"&ROW(A1)&\")\"&\"'\"&\"!C6\")","v":10}]
[null,{"$f":"+B38+1","v":2},null,{"$f":"INDIRECT(\"'\"&\"Sheet (\"&ROW(A2)&\")\"&\"'\"&\"!C6\")","v":10}]
[null,{"$f":"+B39+1","v":3},null,10]
[null,4,null,10]
[null,5,null,100]
[]
[null,"List all Worksheets in a Workbook"]
[]
[null,"Step 1"]
[null,"Name the following formula \"Sheets\""]
[null,"Formula Syntax",null,"=GET.WORKBOOK(1))&T(NOW())"]
[]
[null,"Note: In the Excel ribbon select Formulas > Name Manager > New"]
[]
[null,"Step 2"]
[null,"Paste the following formula into any cell, and then paste paste down for the list."]
[null,"Formula Syntax",null,"=INDEX(MID(Sheets,FIND(\"]\",Sheets)+1,255),ROW(A1))"]
[]
[null,"Example with \"Sheets\" List and =INDIRECT"]
[]
[null,{"$f":"INDEX(MID(Sheets,FIND(\"]\",Sheets)+1,255),ROW(A2))","v":"START"},null,{"$f":"INDIRECT(\"'\"&B58&\"'!C6\")","v":0}]
[null,{"$f":"INDEX(MID(Sheets,FIND(\"]\",Sheets)+1,255),ROW(A3))","v":"Sheet (1)"},null,10]
[null,{"$f":"INDEX(MID(Sheets,FIND(\"]\",Sheets)+1,255),ROW(A4))","v":"Sheet (2)"},null,10]
[null,{"$f":"INDEX(MID(Sheets,FIND(\"]\",Sheets)+1,255),ROW(A5))","v":"Sheet (3)"},null,10]
[null,{"$f":"INDEX(MID(Sheets,FIND(\"]\",Sheets)+1,255),ROW(A6))","v":"Sheet (4)"},null,10]
[null,{"$f":"INDEX(MID(Sheets,FIND(\"]\",Sheets)+1,255),ROW(A7))","v":"END"},null,0]
[null,{"$f":"INDEX(MID(Sheets,FIND(\"]\",Sheets)+1,255),ROW(A8))","v":"Sheet (5)"},null,100]

{"sheet":"START","index":1,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Sheet (1)","index":2,"n_rows":6,"n_cols":3,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Name of this Sheet:",{"$f":"MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,50)","v":"Sheet (1)"}]
[]
[null,"A Place for Numbers"]
[]
[null,"Here's a number -->",10]

{"sheet":"Sheet (2)","index":3,"n_rows":6,"n_cols":3,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Name of this Sheet:",{"$f":"MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,50)","v":"Sheet (2)"}]
[]
[null,"A Place for Numbers"]
[]
[null,"Here's a number -->",10]

{"sheet":"Sheet (3)","index":4,"n_rows":6,"n_cols":3,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Name of this Sheet:",{"$f":"MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,50)","v":"Sheet (3)"}]
[]
[null,"A Place for Numbers"]
[]
[null,"Here's a number -->",10]

{"sheet":"Sheet (4)","index":5,"n_rows":6,"n_cols":3,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Name of this Sheet:",{"$f":"MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,50)","v":"Sheet (4)"}]
[]
[null,"A Place for Numbers"]
[]
[null,"Here's a number -->",10]

{"sheet":"END","index":6,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Sheet (5)","index":7,"n_rows":6,"n_cols":3,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Name of this Sheet:",{"$f":"MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,50)","v":"Sheet (5)"}]
[]
[null,"A Place for Numbers"]
[]
[null,"Here's a number -->",100]