{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Macro4","1355"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":1,"comments":1,"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":"Macro4","index":0,"n_rows":1,"n_cols":2,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["Name",{"$f":"GET.CELL(20,A1)","v":false}]

{"sheet":"1355","index":1,"n_rows":49,"n_cols":11,"rows_kept":49,"cut":false,"hidden":false,"merged":[]}
["Unstack Data that is Separated by Bold Text: Challenge w/Kevin Lehrbass - Formulas, VBA, Power Query"]
["https://www.youtube.com/watch?v=JsErvkFvYf8&t=12s"]
["OZ YouTube Channel:",null,"https://www.youtube.com/user/WalrusCandy"]
["Kevin YouTube Channel:",null,"https://www.youtube.com/user/MySpreadsheetLab"]
["brianxyz at YouTube"]
[]
["Excel Magic Trick 1355 Extract Records Based On Bold Font From a Column, Display Horizontally"]
[]
["Many Fun Topics:"]
[1,"GET.CELL Excel 4 Macro Function to determine when a cell has Bold Font."]
[2,"Create Defined Name that can \"read\" Excel 4 Macro Function."]
[3,"See how to create a Universal Relative Cell Reference in a Defined Name"]
[4,"Helper Column to identify Records for each parent that uses SUM function"]
[5,"Count number of Parents using the MAX function"]
[6,"Extract Parent Names and list vertically using the INDEX, MATCH, IF and ROWS function."]
[7,"Count Number of Children for each Parent with the COUNTIFS, ROWS and IF functions."]
[8,"Extract Children Names and list Horizontally using the INDEX, MATCH, IF, OR and COLUMNS functions"]
[9,"Add Custom Number Formatting to show Numbers as Children Numbers, like 1 displayed as \"Child 1\""]
[10,"Add new records and test formula solution"]
[11,"Learn that the GET.CELL Excel 4 Macro Function requires a complete recalculation using Ctrl + Alt + F9 to get the function to update."]
[]
["People","Bold?",null,"Total Parents",{"$f":"MAX(TPeople[Bold?])","v":8}]
["Chastity",{"$f":"SUM(IsBold,B22)","v":1}]
["Francisco",{"$f":"SUM(IsBold,B23)","v":1},null,"Parent","Number Childeren",1,2,3,4,5,6]
["Rama",{"$f":"SUM(IsBold,B24)","v":1},null,{"$f":"IF(ROWS(D$25:D25)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D25),TPeople[Bold?],0)))","v":"Chastity"},{"$f":"IF(D25=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D25))-1)","v":2},{"$f":"IF(OR(F$24>$E25,$E25=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F25),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":"Francisco"},{"$f":"IF(OR(G$24>$E25,$E25=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G25),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":"Rama"},{"$f":"IF(OR(H$24>$E25,$E25=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H25),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":""},{"$f":"IF(OR(I$24>$E25,$E25=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I25),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E25,$E25=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J25),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E25,$E25=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K25),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Dylan",{"$f":"SUM(IsBold,B25)","v":2},null,{"$f":"IF(ROWS(D$25:D26)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D26),TPeople[Bold?],0)))","v":"Dylan"},{"$f":"IF(D26=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D26))-1)","v":4},{"$f":"IF(OR(F$24>$E26,$E26=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F26),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":"Mike"},{"$f":"IF(OR(G$24>$E26,$E26=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G26),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":"Jonah"},{"$f":"IF(OR(H$24>$E26,$E26=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H26),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":"Jean-Michel"},{"$f":"IF(OR(I$24>$E26,$E26=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I26),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":"Howard"},{"$f":"IF(OR(J$24>$E26,$E26=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J26),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E26,$E26=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K26),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Mike",{"$f":"SUM(IsBold,B26)","v":2},null,{"$f":"IF(ROWS(D$25:D27)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D27),TPeople[Bold?],0)))","v":"Belle"},{"$f":"IF(D27=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D27))-1)","v":2},{"$f":"IF(OR(F$24>$E27,$E27=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F27),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":"John"},{"$f":"IF(OR(G$24>$E27,$E27=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G27),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":"Veronica"},{"$f":"IF(OR(H$24>$E27,$E27=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H27),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":""},{"$f":"IF(OR(I$24>$E27,$E27=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I27),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E27,$E27=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J27),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E27,$E27=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K27),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Jonah",{"$f":"SUM(IsBold,B27)","v":2},null,{"$f":"IF(ROWS(D$25:D28)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D28),TPeople[Bold?],0)))","v":"Imani"},{"$f":"IF(D28=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D28))-1)","v":1},{"$f":"IF(OR(F$24>$E28,$E28=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F28),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":"Lucy"},{"$f":"IF(OR(G$24>$E28,$E28=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G28),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":""},{"$f":"IF(OR(H$24>$E28,$E28=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H28),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":""},{"$f":"IF(OR(I$24>$E28,$E28=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I28),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E28,$E28=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J28),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E28,$E28=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K28),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Jean-Michel",{"$f":"SUM(IsBold,B28)","v":2},null,{"$f":"IF(ROWS(D$25:D29)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D29),TPeople[Bold?],0)))","v":"Desiree"},{"$f":"IF(D29=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D29))-1)","v":1},{"$f":"IF(OR(F$24>$E29,$E29=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F29),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":"Xena"},{"$f":"IF(OR(G$24>$E29,$E29=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G29),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":""},{"$f":"IF(OR(H$24>$E29,$E29=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H29),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":""},{"$f":"IF(OR(I$24>$E29,$E29=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I29),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E29,$E29=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J29),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E29,$E29=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K29),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Howard",{"$f":"SUM(IsBold,B29)","v":2},null,{"$f":"IF(ROWS(D$25:D30)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D30),TPeople[Bold?],0)))","v":"John"},{"$f":"IF(D30=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D30))-1)","v":4},{"$f":"IF(OR(F$24>$E30,$E30=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F30),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":"Mike"},{"$f":"IF(OR(G$24>$E30,$E30=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G30),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":"Selma"},{"$f":"IF(OR(H$24>$E30,$E30=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H30),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":"Emerson"},{"$f":"IF(OR(I$24>$E30,$E30=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I30),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":"Dave Therese"},{"$f":"IF(OR(J$24>$E30,$E30=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J30),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E30,$E30=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K30),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Belle",{"$f":"SUM(IsBold,B30)","v":3},null,{"$f":"IF(ROWS(D$25:D31)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D31),TPeople[Bold?],0)))","v":"Marsden"},{"$f":"IF(D31=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D31))-1)","v":2},{"$f":"IF(OR(F$24>$E31,$E31=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F31),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":"Kimberley"},{"$f":"IF(OR(G$24>$E31,$E31=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G31),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":"Gene"},{"$f":"IF(OR(H$24>$E31,$E31=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H31),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":""},{"$f":"IF(OR(I$24>$E31,$E31=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I31),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E31,$E31=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J31),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E31,$E31=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K31),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["John",{"$f":"SUM(IsBold,B31)","v":3},null,{"$f":"IF(ROWS(D$25:D32)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D32),TPeople[Bold?],0)))","v":"Sioux"},{"$f":"IF(D32=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D32))-1)","v":3},{"$f":"IF(OR(F$24>$E32,$E32=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F32),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":"Billy"},{"$f":"IF(OR(G$24>$E32,$E32=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G32),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":"Rina"},{"$f":"IF(OR(H$24>$E32,$E32=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H32),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":"Shelia"},{"$f":"IF(OR(I$24>$E32,$E32=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I32),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E32,$E32=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J32),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E32,$E32=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K32),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Veronica",{"$f":"SUM(IsBold,B32)","v":3},null,{"$f":"IF(ROWS(D$25:D33)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D33),TPeople[Bold?],0)))","v":""},{"$f":"IF(D33=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D33))-1)","v":""},{"$f":"IF(OR(F$24>$E33,$E33=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F33),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":""},{"$f":"IF(OR(G$24>$E33,$E33=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G33),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":""},{"$f":"IF(OR(H$24>$E33,$E33=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H33),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":""},{"$f":"IF(OR(I$24>$E33,$E33=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I33),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E33,$E33=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J33),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E33,$E33=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K33),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Imani",{"$f":"SUM(IsBold,B33)","v":4},null,{"$f":"IF(ROWS(D$25:D34)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D34),TPeople[Bold?],0)))","v":""},{"$f":"IF(D34=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D34))-1)","v":""},{"$f":"IF(OR(F$24>$E34,$E34=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F34),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":""},{"$f":"IF(OR(G$24>$E34,$E34=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G34),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":""},{"$f":"IF(OR(H$24>$E34,$E34=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H34),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":""},{"$f":"IF(OR(I$24>$E34,$E34=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I34),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E34,$E34=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J34),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E34,$E34=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K34),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Lucy",{"$f":"SUM(IsBold,B34)","v":4},null,{"$f":"IF(ROWS(D$25:D35)>$E$22,\"\",INDEX(TPeople[People],MATCH(ROWS(D$25:D35),TPeople[Bold?],0)))","v":""},{"$f":"IF(D35=\"\",\"\",COUNTIFS(TPeople[Bold?],ROWS(D$25:D35))-1)","v":""},{"$f":"IF(OR(F$24>$E35,$E35=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(F$25:F35),TPeople[[Bold?]:[Bold?]],0)+F$24))","v":""},{"$f":"IF(OR(G$24>$E35,$E35=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(G$25:G35),TPeople[[Bold?]:[Bold?]],0)+G$24))","v":""},{"$f":"IF(OR(H$24>$E35,$E35=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(H$25:H35),TPeople[[Bold?]:[Bold?]],0)+H$24))","v":""},{"$f":"IF(OR(I$24>$E35,$E35=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(I$25:I35),TPeople[[Bold?]:[Bold?]],0)+I$24))","v":""},{"$f":"IF(OR(J$24>$E35,$E35=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(J$25:J35),TPeople[[Bold?]:[Bold?]],0)+J$24))","v":""},{"$f":"IF(OR(K$24>$E35,$E35=\"\"),\"\",INDEX(TPeople[[People]:[People]],MATCH(ROWS(K$25:K35),TPeople[[Bold?]:[Bold?]],0)+K$24))","v":""}]
["Desiree",{"$f":"SUM(IsBold,B35)","v":5}]
["Xena",{"$f":"SUM(IsBold,B36)","v":5},null,"To Refresh ISBOLD with GET.CEL Excel 4 Macro Function, you must Refresh All: Ctrl + Alt + F9 (to recalculate All)"]
["John",{"$f":"SUM(IsBold,B37)","v":6}]
["Mike",{"$f":"SUM(IsBold,B38)","v":6}]
["Selma",{"$f":"SUM(IsBold,B39)","v":6}]
["Emerson",{"$f":"SUM(IsBold,B40)","v":6}]
["Dave Therese",{"$f":"SUM(IsBold,B41)","v":6}]
["Marsden",{"$f":"SUM(IsBold,B42)","v":7}]
["Kimberley",{"$f":"SUM(IsBold,B43)","v":7}]
["Gene",{"$f":"SUM(IsBold,B44)","v":7}]
["Sioux",{"$f":"SUM(IsBold,B45)","v":8}]
["Billy",{"$f":"SUM(IsBold,B46)","v":8}]
["Rina",{"$f":"SUM(IsBold,B47)","v":8}]
["Shelia",{"$f":"SUM(IsBold,B48)","v":8}]