{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Intro","Quick Review","Requirement","Solution","Characters","Links & Feedback"],"not_rendered":{"charts":0,"images":15,"macros":1,"external_links":0,"defined_names":5,"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":"Intro","index":0,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[[20,6,20,12]]}
[{"$f":"\"Custom Formats, VBA, and Nerds!\"&CHAR(10)&\"Video 00151\"","v":"Custom Formats, VBA, and Nerds!\nVideo 00151"}]

{"sheet":"Quick Review","index":1,"n_rows":22,"n_cols":5,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,"$#,###.00_);$(#,###.00);\"Zero!\";\"My name is: \"@"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Bare Value","With Custom Format"]
[null,165,{"$f":"B19","v":165}]
[null,-200,{"$f":"B20","v":-200}]
[null,0,{"$f":"B21","v":0}]
[null,"Kevin",{"$f":"B22","v":"Kevin"}]

{"sheet":"Requirement","index":2,"n_rows":13,"n_cols":2,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["Date","Sales"]
["2016-01-01",115467.32]
["2016-02-01",2472.96]
["2016-03-01",35619.79]
["2016-04-01",89222.71]
["2016-05-01",68954.02]
["2016-06-01",105777.23]
["2016-07-01",79794.039999999994]
["2016-08-01",4180.8100000000004]
["2016-09-01",237286.53]
["2016-10-01",200026.81]
["2016-11-01",279247]
["2016-12-01",1194180.81]

{"sheet":"Solution","index":3,"n_rows":14,"n_cols":22,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["Date","Sales",null,null,null,"Format List","Character",null,"Select Character"]
["2016-01-01",115467.32,null,null,null,"[>=1000000]0,,\" Million\";[>=1000]0,\" Thousand\";0","Helmut Schmidt",null,null,null,{"$f":"IF(SelectCharacter=\"\",\"<<<<< Select a character\",\"\")","v":"<<<<< Select a character"}]
["2016-02-01",2472.96,null,null,null,"$#,##0","Raj Singh"]
["2016-03-01",35619.79,null,null,null,"General","Lars Peterson",null,null,null,null,null,null,"SOLUTION DESCRIPTION"]
["2016-04-01",89222.71,null,null,null,"[>1000000]\"Yes\";;;","Over Eager Otis",null,null,null,null,null,null,"Create format list",null,null,null,null,null,null,null,true]
["2016-05-01",68954.02,null,null,null,null,null,null,null,null,null,null,null,"Select character:",null,null,null,null,null,null,null,true]
["2016-06-01",105777.23,null,null,null,null,null,null,null,null,null,null,null,"   custom format applied to sales values via vba",null,null,null,null,null,null,null,true]
["2016-07-01",79794.039999999994,null,null,null,null,null,null,null,null,null,null,null,"   'picture 5' attached to named range 'CharacterCellAddress'",null,null,null,null,null,null,null,true]
["2016-08-01",4180.8100000000004,null,null,null,null,null,null,null,null,null,null,null,"   'CharacterCellAddress' formula =INDIRECT(Characters!$A$18)",null,null,null,null,null,null,null,true]
["2016-09-01",237286.53,null,null,null,null,null,null,null,null,null,null,null,"    In sheet Characters, cells A17 & A18 selects character",null,null,null,null,null,null,null,true]
["2016-10-01",200026.81,null,null,null,null,null,null,null,null,null,null,null,"Dynamic Bubble Text:",null,null,null,null,null,null,null,true]
["2016-11-01",279247,null,null,null,null,null,null,null,null,null,null,null,"   type text in Row 7 in sheet Characters",null,null,null,null,null,null,null,true]
["2016-12-01",1194180.81,null,null,null,null,null,null,null,null,null,null,null,"   text attached to speech bubble in row 2 via formula",null,null,null,null,null,null,null,true]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"   text bubble and cartoon linked via 'picture 5' steps listed above",null,null,null,null,null,null,null,true]

{"sheet":"Characters","index":4,"n_rows":42,"n_cols":9,"rows_kept":42,"cut":false,"hidden":false,"merged":[[8,3,9,3],[32,0,32,1],[33,0,33,1],[8,0,9,0],[8,1,9,1],[8,2,9,2],[28,0,28,1],[29,0,29,1],[30,0,30,1],[31,0,31,1]]}
[null,null,null,null,null,null,null,null,true]
[null,null,null,null,null,{"$f":"IF($I$1=FALSE,\"\",\" <<<<< Position each drawing & text bubble within a cell\")","v":" <<<<< Position each drawing & text bubble within a cell"}]
["Character Set-up"]
["CEO","Data Analyst","Database Administrator","Marketing Manager",null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<< Enter job title\")","v":"<<<<< Enter job title"}]
["Helmut Schmidt","Raj Singh","Lars Peterson","Over Eager Otis",null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<< Enter nerd aame\")","v":"<<<<< Enter nerd aame"}]
[]
["I want only high level detail.","I need  to show only integers!","No special formatting please.","Were December sales more than 1 million?",null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<< Enter bubble description\")","v":"<<<<< Enter bubble description"}]
[]
["…wants to read high level details in a compact dashboard","…needs to work with data to create several reports. He only wants to display integers","…needs to load the data in it's original form. It could be changed to any format via queries afterwards","…Otis wants to know if December sales were greater than 1 million. If so he gets a bonus."]
[]
[]
[]
[]
["Formulas"]
[]
[{"$f":"IF(SelectCharacter=\"\",\"\",INDEX(Table2[Format List],MATCH(SelectCharacter,Table2[Character],0)))","v":""},{"$f":"IF($I$1=FALSE,\"\",\"<<<<< Format corresponding to selected character. VBA macro loads this format.\")","v":"<<<<< Format corresponding to selected character. VBA macro loads this format."}]
[{"$f":"IF(Solution!I2=\"\",\"$K$2\",ADDRESS(2,MATCH(SelectCharacter,Characters!$A$5:$M$5,0)))","v":"$K$2"},{"$f":"IF($I$1=FALSE,\"\",\"<<<<< Cell address of selected character (or cell $K$2 if no character is selected)\")","v":"<<<<< Cell address of selected character (or cell $K$2 if no character is selected)"}]
[{"$f":"IF(A17=\"\", \"\", \"Characters!\"&A17)","v":"Characters!$K$2"},{"$f":"IF($I$1=FALSE,\"\",\"<<<<< Sheet name & cell address ('Picture 5' to named range to cell A18\")","v":"<<<<< Sheet name & cell address ('Picture 5' to named range to cell A18"}]
[{"$f":"IF(SelectedFormat=\"\",\"\",Solution!B13)","v":""},{"$f":"IF($I$1=FALSE,\"\",\"<<<<< Sample of selected format\")","v":"<<<<< Sample of selected format"}]
[{"$f":"IF(SelectCharacter=\"\",\"\",INDEX($A$4:$D$4,MATCH(SelectCharacter,Characters!$A$5:$D$5,0))&CHAR(10)&SelectCharacter)","v":""},{"$f":"IF($I$1=FALSE,\"\",\"<<<<< Selected character's job title & name\")","v":"<<<<< Selected character's job title & name"}]
[]
[]
[]
[]
[]
[]
["VBA CODE"]
["PULLS SELECTED CUSTOM FORMAT INTO CODE THEN CHANGES FORMAT OF SALES DATA",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<CODE DESCRIPTION\")","v":"<<<<<CODE DESCRIPTION"}]
["Sub AssignCustomNumberFormat()",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<Sub procedure name\")","v":"<<<<<Sub procedure name"}]
["Dim Getformat As String ",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<Will be used to retrieve selected format from a cell\")","v":"<<<<<Will be used to retrieve selected format from a cell"}]
["Getformat = Range(\"SelectedFormat\").Value \n",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<Assigns selected format to string variable\")","v":"<<<<<Assigns selected format to string variable"}]
["Range(\"Table1[Sales]\").NumberFormat = Getformat",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<Selects sales data and changes the format\")","v":"<<<<<Selects sales data and changes the format"}]
["Range(\"SampleFormat\").NumberFormat = Getformat",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<Used to display sample format above character's picture\")","v":"<<<<<Used to display sample format above character's picture"}]
["End Sub",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<End sub procedure\")","v":"<<<<<End sub procedure"}]
[]
[]
["CODE RUNS EACH TIME THE DROP DOWN VALUE (CELL I2) IS CHANGED. MACRO ABOVE IS THEN USED"]
["Private Sub Worksheet_Change(ByVal Target As Range)"]
["If Target.Address = \"$I$2\" Then ",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<if cell I2 changes…\")","v":"<<<<<if cell I2 changes…"}]
["Call AssignCustomNumberFormat  ",null,null,{"$f":"IF($I$1=FALSE,\"\",\"<<<<<then run this macro (macro listed above)\")","v":"<<<<<then run this macro (macro listed above)"}]
["End If"]
["End Sub"]

{"sheet":"Links & Feedback","index":5,"n_rows":30,"n_cols":32,"rows_kept":30,"cut":false,"hidden":false,"merged":[[20,6,20,12],[21,6,21,12],[22,6,22,12],[23,6,23,12],[28,6,28,12]]}
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":" DEC2HEX(RANDBETWEEN(0, 2^16), 4)\n & DEC2HEX(RANDBETWEEN(0, 2^16), 4)\n & DEC2HEX(RANDBETWEEN(0, 2^16), 4)\n & DEC2HEX(RANDBETWEEN(0, 2^16), 4)\n & DEC2HEX(RANDBETWEEN(0, 2^16), 4)\n & DEC2HEX(RANDBETWEEN(0, 2^16), 4)\n & DEC2HEX(RANDBETWEEN(0, 2^16), 4)\n & DEC2HEX(RANDBETWEEN(0, 2^16), 4)","v":"7E30840E1A71B3C38D1541B0BAA59F9B"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,"Watch my video:","https://youtu.be/gVNvI8unSQA"]
[null,null,null,null,null,"My Excel files:","Click here to see my Excel files"]
[null,null,null,null,null,"Join Mr Excel's FREE help forum","http://www.mrexcel.com/forum/forum.php"]
[null,null,null,null,null,"One of my favorite Excel books!","'Control Shift Enter' by Mike Girvin!"]
[null,null,null,null,null,"Custom Formatting Numbers in Charts","http://peltiertech.com/Excel/NumberFormats.html"]
[null,null,null,null,null,"Custom Number Format Samples","http://spreadsheetpage.com/index.php/tip/using_custom_number_formats/"]
[null,null,null,null,null,"Custom Format Resource","http://www.bettersolutions.com/excel/EVD243/QM215412021.htm"]
[null,null,null,null,null,"Customer Number Formats: from Wittwer, J.W.","http://www.vertex42.com/ExcelTips/custom-number-formats.html"]
[null,null,null,null,null,"Combine Pictures with Drop Down Selection","How to make pictures appear based on drop down selection"]
[null,null,null,null,null,"Customer Number Formats: Show Results as Fractions","http://www.excelarticles.com/LE10ePub-453.html"]