tag:blogger.com,1999:blog-78267208476391139392024-03-16T02:28:30.032-07:00Excel Spreadsheets HelpExcel help, tips, and templates. Learn how to write VBA macros or browse our project management resources.Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.comBlogger296125tag:blogger.com,1999:blog-7826720847639113939.post-30613655754056718512024-01-28T20:52:00.000-08:002024-02-05T18:40:01.001-08:00Super Bowl Squares 2024 - The BEST Excel TemplatesIt's finally time to play Super Bowl Squares! As the biggest sporting event of the year approaches, hardcore football and casual fans alike gear up for an unforgettable Super Bowl experience. To add an extra layer of excitement to your game day festivities, I'm here to introduce you to the magic of <a href="https://dsadsadsadsadsa.gumroad.com/l/superbowlsquares" target="_blank">Super Bowl Squares</a> and simplify the process with my specially crafted and powerful Excel template. Whether you're a seasoned veteran or a newcomer to this classic game of chance, my template is designed to make organizing and tracking your Super Bowl Squares pool a breeze. Let's get to it!<br /><br />I’ve been making Super Bowl Squares templates available for you to download since 2012 and the latest versions are the most versatile yet. There are two templates to choose from, I'll explain the pros and cons of each.<div><br /></div><h2 style="text-align: left;">2024 Super Bowl Squares - 54 Ways to Play</h2>Released in 2021, this is the more "classic" and straightforward way to play. When you first open the sheet, it has a nice clean look with only one sheet visible. You select the options for how you want to play, click Generate Squares, and it sets up the sheet exactly for the version you want to play. <div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCF4Q5SBUO-xB_SmAAMk7B5Be8-1Y_gXqhW5ruK3RYAzEbu1Uo45F8yYSAfE7SdqcAQA2LQSQ-XKxWKymMybS2VuqT3LxOYlwSMzUSPI0VkVmMu335FMzy4kftakPteJNcObxv7WA5sZLJLzFh8AUCeioGVER-G5As1tb0flXTlguajqUvPfsWVSQhyphenhyphenNo/s867/super%20bowl%20squares%202024%20free%20template%20excel%20pdf.png" style="margin-left: 1em; margin-right: 1em;"><img alt="excel file for superbowl squares template" border="0" data-original-height="546" data-original-width="867" height="282" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCF4Q5SBUO-xB_SmAAMk7B5Be8-1Y_gXqhW5ruK3RYAzEbu1Uo45F8yYSAfE7SdqcAQA2LQSQ-XKxWKymMybS2VuqT3LxOYlwSMzUSPI0VkVmMu335FMzy4kftakPteJNcObxv7WA5sZLJLzFh8AUCeioGVER-G5As1tb0flXTlguajqUvPfsWVSQhyphenhyphenNo/w445-h282/super%20bowl%20squares%202024%20free%20template%20excel%20pdf.png" width="445" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div><br /></div><div>The way it works is all the scoring systems are already built into the sheet. If you go to the Squares or the Manager sheet you will find many hidden rows or columns. The Generate Squares macro simply hides or unhides the data based on the user’s selections.</div><div><br /><div><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/Au32PwGmyV0?si=eXtARG-8dfHD0zYc" title="YouTube video player" width="560"></iframe></div><div><br /></div><div><h2><a href="https://gumroad.com/l/superbowlsquares" style="background-color: white; color: blue; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; text-decoration-line: none;" target="_blank"><b>Download 2024 Super Bowl Squares here</b></a></h2><br /><div><h2 style="text-align: left;">Super Bowl LVIII Squares Unlimited</h2>54 ways to play apparently wasn't enough as I still received numerous requests for customizations and more features. This lead to the creation of a new approach to the template: Super Bowl Squares Unlimited. The main difference in how it works is there are no pre-built scoring systems. Instead, they are built on the fly by macros based on the user's input; therefore there are an infinite number of ways to play.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF9ijm60JKfXE0b9Xaa4ITrP16cGss-B7ZyWQe5u4AZIWzSpbj1uMJVPu60mn4W8TAwta0vdNtYitGxmQMs3Ul9Kodlum_BXejeGY-2RoCF73xBpFpMMxwsaMJ2egp0dl_3j0-JRSEM8nHDMuKgVvP3WCP9JxlT0Y91Og3dISzm5H16Fif8hTlzWkpVQQ/s547/best%20superbowl%20squares%20template%20printable%20file.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="547" data-original-width="510" height="434" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF9ijm60JKfXE0b9Xaa4ITrP16cGss-B7ZyWQe5u4AZIWzSpbj1uMJVPu60mn4W8TAwta0vdNtYitGxmQMs3Ul9Kodlum_BXejeGY-2RoCF73xBpFpMMxwsaMJ2egp0dl_3j0-JRSEM8nHDMuKgVvP3WCP9JxlT0Y91Og3dISzm5H16Fif8hTlzWkpVQQ/w405-h434/best%20superbowl%20squares%20template%20printable%20file.png" width="405" /></a></div><br /><div>This sheet offers the most flexibility but might take some getting used to. At first it may be a little difficult to understand how the scoring works, so I recommend you watch this video:</div><div><br /></div><div><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/Vv6J4wbXKpU?si=BBowF3vmqx1AmfLB" title="YouTube video player" width="560"></iframe><div><br /></div><span><a name='more'></a></span><div><br /></div><h2 style="text-align: left;"><a href="https://gumroad.com/l/superbowlsquares" style="background-color: white; color: blue; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; text-decoration-line: none;" target="_blank"><b>Download 2024 Super Bowl Squares here</b></a></h2><div><b style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"><span style="color: red;">*If you’re using iOS or cannot enable macros you will not be able to use this sheet, sorry. There is a simple printable PDF in the download and there is a Google sheet.</span></b></div><div><b style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"><span style="color: red;"><br /></span></b></div><div><b style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"><span style="color: red;">You also might need to make sure the folder the spreadsheet is downloaded to is a Trusted located in Excel. <a href="https://support.ebmsoftware.com/hc/en-us/articles/8178204626829-How-do-I-create-a-Trusted-Location-in-Excel" target="_blank">See this article for how to check.</a></span></b></div><div><br /></div><span><!--more--></span>Get ready to elevate your Super Bowl party with a touch of friendly competition, a dash of luck, and the convenience of our user-friendly Excel template. Let me know in the comments below what version of the squares template you'll be using and what team you're rooting for! My heart breaks for Detroit Lions fans - you were so close to earning a trip to your first Super Bowl! </div></div></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-68327953232629203892024-01-19T07:15:00.000-08:002024-01-28T20:58:42.883-08:00Girl Scout Cookie Tracking Spreadsheet Template<p>My daughter is in Girl Scouts and her troop wanted to sell Girl Scout cookies for the first time this year. In order to sell Girl Scout cookies you need an adult to be the TCPC: Troop Cookie Program Coordinate. Or as my daughter said, "We need you to be the Cookie Daddy!" I figured all this meant was they needed someone to manage an Excel spreadsheet for all the cookie sales so I agreed.</p><p>Little did I know what was in store as there is much more to it. First is all the training and webinars, background check, volunteer forms, etc. Then when it comes to finally sell cookies, there are no spreadsheets; they've got Digital Cookie online selling platform and eBudde management system. While the eBudde system and tools seem to have all the information needed on the backend, the front end is very confusing for a new user. It's very confusing and cumbersome and the key information the TCPC needs to know is spread out over different pages. Seriously, if you have to have numerous training sessions to explain how everything works and people are still asking tons of questions, your system is too complicated.</p><p>So I decided to make a spreadsheet!</p><p>Introducing the <b><a href="https://dsadsadsadsadsa.gumroad.com/l/girlscoutcookietemplate" target="_blank">Girl Scout Cooking Tracking Template</a></b>:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsqftr0mC9_SI1rmrBsNl_s-l4owzZQJHb2Jm2KR8r8HIoJkCP5oeSV2-KlWuYSsJH8EzyGoL4tb9S4o1tAG7XWPnZygjLQZ9YqpKneKKJskoJcPBnxcDmdxf8tF-XdG_V0bh_aAEQoPgPq3aX7I-398OvxGIY2tWdmekMvFiduJTUC-7o0Lw7AOaidxo/s1355/girl%20scout%20cookie%20tracking%20spreadsheet.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="648" data-original-width="1355" height="281" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsqftr0mC9_SI1rmrBsNl_s-l4owzZQJHb2Jm2KR8r8HIoJkCP5oeSV2-KlWuYSsJH8EzyGoL4tb9S4o1tAG7XWPnZygjLQZ9YqpKneKKJskoJcPBnxcDmdxf8tF-XdG_V0bh_aAEQoPgPq3aX7I-398OvxGIY2tWdmekMvFiduJTUC-7o0Lw7AOaidxo/w585-h281/girl%20scout%20cookie%20tracking%20spreadsheet.JPG" width="585" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;">With this spreadsheet you still have to manually input all the cookie sale information from eBudde but now all the information you need to know is shown visually on just two sheets and is easy to understand. Here's how to use the cookie tracker:</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">On the sales summary page, copy and paste your Girl Scouts. Then input each of their Digital Cookie sales, which are broken into four categories: </div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">1. Cookies to be hand delivered (from the Initial Orders page in eBudde)</div><div class="separator" style="clear: both; text-align: left;">2. Donated cookies for Operation Salute</div><div class="separator" style="clear: both; text-align: left;">3. Any cookie sales from paper order forms that need to be input manually</div><div class="separator" style="clear: both; text-align: left;">4. Orders that will be shipped direct from the bakery (from the Girl Orders page)</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">Next, you can input each Girl Scout's goal for how many packages they want to sell. These will add up at the bottom to show the overall Troop Cookie Goal. There is a donut graph to show the troop's progress towards their goal.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">On the right it shows the Instant Rewards and how many packages it takes to reach. Once a girl has reached the goal, the formula automatically shows as having achieved it.</div><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCcT1vrMlqZtmIAtqghu06MTCrZD_SMptw2uRXuuT_C3r1KYuZYY2pFZI3r6lmaVq5gZn1A1EBDIyoEFyJGV7JtxBWYvwPGCm0snF6axYyFh08IaHsrcjEMUK-BRP_h5m9Dv-pFvji6OT4V_7gtdfOYAZXXu8TS5eupbdJWi7l3epvGAv69fmxjoyECwM/s1459/girl%20scout%20cookie%20selling%20spreadsheet.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="408" data-original-width="1459" height="169" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCcT1vrMlqZtmIAtqghu06MTCrZD_SMptw2uRXuuT_C3r1KYuZYY2pFZI3r6lmaVq5gZn1A1EBDIyoEFyJGV7JtxBWYvwPGCm0snF6axYyFh08IaHsrcjEMUK-BRP_h5m9Dv-pFvji6OT4V_7gtdfOYAZXXu8TS5eupbdJWi7l3epvGAv69fmxjoyECwM/w608-h169/girl%20scout%20cookie%20selling%20spreadsheet.jpg" width="608" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;">On the next sheet, we have a breakdown of how many of each type of girl scout cookie has been sold (thin mints being the most popular, obviously). As the cookie daddy and the person responsible for picking up the cookies from the cookie cupboard to distribute to the troop, the most important info is the number of cases of each type of cookie I need to pickup. </div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">There is another sheet in my template that explains some of the terminology, the key being the word "box" is never used. The girls sell "packages" of cookies and 12 of those packages make up a case. The cookies are never mixed up in different cases and if you only sell one package of LemonUps, you have to pickup an entire case of LemonUps.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwNvT2wZ4jkVPKtby-jBrknZwwNDl7ZBAly5OuXd_IHheWU3JbP2eIITsWnk6qVR5eLGxws7D8W-7HpqfAEW8Ke2P9dTsc6H2NNa5bXTWlCl9gaLpaeBL1r-oy7Tnkqx6nu9L3C4_FjC1ErRFvXHX34VH2c4_HOVmCBTxc9Zq-LuxNglCWt8czBZO7Fp8/s501/girl%20scout%20cookie%20terminology%20cases%20versus%20packages.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="277" data-original-width="501" height="177" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwNvT2wZ4jkVPKtby-jBrknZwwNDl7ZBAly5OuXd_IHheWU3JbP2eIITsWnk6qVR5eLGxws7D8W-7HpqfAEW8Ke2P9dTsc6H2NNa5bXTWlCl9gaLpaeBL1r-oy7Tnkqx6nu9L3C4_FjC1ErRFvXHX34VH2c4_HOVmCBTxc9Zq-LuxNglCWt8czBZO7Fp8/s320/girl%20scout%20cookie%20terminology%20cases%20versus%20packages.JPG" width="320" /></a></div><br /><div class="separator" style="clear: both; text-align: left;">The Girl Scout Cooking Tracking Spreadsheet shows you exactly how many cases of each cookie you need and how many unsold packages in each case. The unsold cookies from the initial order can be used to sell in-person during the booth phase.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;"><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/V_MwZt1Pyhc?si=w-tf1GNh1NNbnehJ" title="YouTube video player" width="560"></iframe></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div>I added a sheet you can use as a template for booth signups.</div><h4 style="text-align: left;"><a href="https://dsadsadsadsadsa.gumroad.com/l/girlscoutcookietemplate" target="_blank">Download the Girl Scout Cookie Tracking Spreadsheet Template here.</a></h4><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /><br /><p><br /></p></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-63400394834316850162023-12-03T19:44:00.000-08:002023-12-03T19:44:50.838-08:002023 College Football Bowl Prediction Pool<p><span style="color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 13.3333px; white-space-collapse: preserve;">The college football conference championships were played this past weekend which means the 2023 NCAA college football bowl season is here again! Therefore, it’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season (</span><a href="https://excelspreadsheetshelp.blogspot.com/2021/11/gift-guide-for-excel-users-2021.html" style="color: blue; font-family: "Trebuchet MS"; font-size: 13.3333px; text-decoration-line: none; white-space-collapse: preserve;" target="_blank">other than giving and receiving gifts</a><span style="color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 13.3333px; white-space-collapse: preserve;">) is being able to talk trash to your relatives about their terrible bowl picks. </span></p><p style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"><span style="font-family: "Trebuchet MS"; font-size: 13.3333px; white-space-collapse: preserve;">This year has the added bonus of not just single bowl games but the tenth and final year of a four team playoff to determine the national champion. Starting in 2024 the college football playoff will expand to twelve teams (couldn't have been one year earlier, could it). Can't wait to see how that plays out!</span></p><p style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijlx_qzeVfO0MLf-oYGW81t6XazW12EYErPAJ6VJHf7otXW8K-f9YJePdAIqvE9j4ZitJEoNoPhTjFuTwfsNoYHI0CQQ7XeCzop3Mm2CD2LWhhGvimkOMTz8SmT1EVTWAWL-amRnkW6Zn_Oai-JyiUliOFZOmetg35_6Aogkclx2cDqarfz4tQw4QeBak/s1415/2023%20college%20football%20bowl%20prediction%20pool%20spreadsheet%20manager.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="638" data-original-width="1415" height="233" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijlx_qzeVfO0MLf-oYGW81t6XazW12EYErPAJ6VJHf7otXW8K-f9YJePdAIqvE9j4ZitJEoNoPhTjFuTwfsNoYHI0CQQ7XeCzop3Mm2CD2LWhhGvimkOMTz8SmT1EVTWAWL-amRnkW6Zn_Oai-JyiUliOFZOmetg35_6Aogkclx2cDqarfz4tQw4QeBak/w519-h233/2023%20college%20football%20bowl%20prediction%20pool%20spreadsheet%20manager.jpg" width="519" /></a></div><br /><span style="font-family: "Trebuchet MS"; font-size: 13.3333px; white-space-collapse: preserve;">But let's not get ahead of ourselves and just enjoy that fact that the SEC was ALMOST left out of the playoff for the first time. It's good to see at least one new team make the CFB playoff. Here's the full breakdown of bowl teams per conference:</span><p></p><p style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrGuLn7_cgFr7AXOdGtPvzCWMWaDDhrvZR6MDc8h2qhbQyFqSxGRnuajABygC5-Y-0AGXCUdb03ZCK74QkWVT5qI91R-Q8TDIy4HtBWa0jIU6VStJyzfAS5TgqhjWiyG6t_ZO00dk29K1oXJdmj66YE1Jso8pxXCCMifytTYGGQXo3aEN8XqnNYryW_Eo/s1653/2023%20bowl%20teams%20by%20conference.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="993" data-original-width="1653" height="284" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrGuLn7_cgFr7AXOdGtPvzCWMWaDDhrvZR6MDc8h2qhbQyFqSxGRnuajABygC5-Y-0AGXCUdb03ZCK74QkWVT5qI91R-Q8TDIy4HtBWa0jIU6VStJyzfAS5TgqhjWiyG6t_ZO00dk29K1oXJdmj66YE1Jso8pxXCCMifytTYGGQXo3aEN8XqnNYryW_Eo/w472-h284/2023%20bowl%20teams%20by%20conference.png" width="472" /></a></div><p></p><p style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; white-space-collapse: preserve;">There are multiple ways to play Bowl pick'em. Features for this year's bowl prediction pool over the previous college football bowl pool manager spreadsheets include the following:</span></p><ul style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.4; margin: 0px; padding-inline-start: 48px; padding: 0px 2.5em;"><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 12pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; text-wrap: wrap; vertical-align: baseline;">Easy method to make each bowl game worth a different point value, so the national championship game and semi-finals can be worth more points, or however you want to customize it.</span></p></li><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; text-wrap: wrap; vertical-align: baseline;">Updated leaderboard tab with new stats</span></p></li><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; text-wrap: wrap; vertical-align: baseline;">Separate entry sheet to pass out to participants or co-workers that can be imported automatically by a built-in macro</span></p></li><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; text-wrap: wrap; vertical-align: baseline;">Complete NCAA college football bowl schedule with game times and TV stations</span></p></li><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 0pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; text-wrap: wrap; vertical-align: baseline;">New stat sheet to track each conference's record during bowl season. Graph shows total conference teams and total conference wins</span></p></li></ul><div style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space-collapse: preserve;">The bowl prediction sheets include the football helmet designs for every team (taken from my college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space-collapse: preserve;"><b><a href="https://dsadsadsadsadsa.gumroad.com/l/cfpbowlsheet" style="color: blue; text-decoration-line: none;" target="_blank">Download the CFP Pool Manager and Single Entry Form here</a></b></span></p><h3 style="line-height: 1.38; margin: 12pt 0px; position: relative;"><span style="font-family: "Trebuchet MS";"><span style="font-size: 13.3333px; white-space-collapse: preserve;"><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/q3dztEOa0bw?si=nUqBnO5iGGGw-5rt" title="YouTube video player" width="560"></iframe></span></span></h3><h3 style="line-height: 1.38; margin: 12pt 0px; position: relative;"><span style="font-family: "Trebuchet MS";"><span style="font-size: 13.3333px; white-space-collapse: preserve;">Bowl Pick'em Game For Google Sheets</span></span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space-collapse: preserve;">Unlike many of my other spreadsheets, macros are not critical for the Bowl prediction pool. If you have a Google drive account you should be able to upload the pool manager and use it inside Google sheets. Participants can make their picks directly in the sheet using the drop down lists and everyone can see the scoreboard update in real time. All the formulas should work. Insert new columns where it says in order to add more players. The macro to import single player picks won't work but it is not needed. Try it if you want to play that way and let me know if you find any issues in Google sheets.</span></p><h3 style="font-size: 13px; line-height: 1.38; margin: 12pt 0px; position: relative;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space-collapse: preserve;">College Football Bowl Pick'em with Confidence Points</span></h3><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space-collapse: preserve;"></span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space-collapse: preserve;">If you watch the video above you can see how this works. E</span><span style="background-color: transparent;">ach player can select confidence picks where they rank each game according to how confident they are in their pick. If you're super confident Georgia is going to roll over Ohio State you would rank that game 42 from the drop down list and would receive 42 points if you're correct. If you're not so sure Michigan will beat TCU you might only rank that game 1 or 2 points. </span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space-collapse: preserve;"><a href="https://gumroad.com/l/cfpbowlsheet" style="color: blue; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; text-align: center; text-decoration-line: none; white-space-collapse: collapse;"><span style="color: red; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre;">Download the 2023 CFP Bowl Prediction Pool Manager.xlsm file here</span></a></span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;">Have fun! As always, I welcome any feedback or ideas for improvement.</p></div><p style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"><span style="font-family: "Trebuchet MS"; font-size: 13.3333px; white-space-collapse: preserve;"><br /></span></p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com2tag:blogger.com,1999:blog-7826720847639113939.post-69062689992093125192023-05-12T13:09:00.000-07:002023-05-12T13:09:00.444-07:002023 NFL Helmet Schedule Spreadsheet The 2023 NFL schedule was released yesterday and since I used my Excel skills to automate the creation of this spreadsheet I was able to create the NFL Helmet Schedule in less than a minute! All 32 teams, all 18 weeks of the season (now 17 games plus one bye week), all in one spreadsheet:<div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPz4BIhsZOmr-g7Gm5NrchpeffP6vv_Fu01iRgv8-xwnRa4ylwQtTW6PFapekOx48QhHi2ayzT7p0lshXQF05DC_7Mkz00nnwEJNNkFc5YHv2M2eDaTyW_j_f5ayzBpjJ-t2dMUjgsQn9bCTZ6IuoGR4oHCdx2w_dli2VPdKEBCH8xXAcDZB72gRZA/s1241/2023%20nfl%20helmet%20schedule%20spreadsheet.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="618" data-original-width="1241" height="218" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPz4BIhsZOmr-g7Gm5NrchpeffP6vv_Fu01iRgv8-xwnRa4ylwQtTW6PFapekOx48QhHi2ayzT7p0lshXQF05DC_7Mkz00nnwEJNNkFc5YHv2M2eDaTyW_j_f5ayzBpjJ-t2dMUjgsQn9bCTZ6IuoGR4oHCdx2w_dli2VPdKEBCH8xXAcDZB72gRZA/w438-h218/2023%20nfl%20helmet%20schedule%20spreadsheet.png" width="438" /></a></div><br />A job that used to take hour by moving helmets manually is now so easy to do. Yes, that's why I love the power of macros and automation. I wrote the macro myself but nowadays <a href="https://excelspreadsheetshelp.blogspot.com/2023/01/using-openais-chatgpt-to-update-my-year.html" target="_blank">you could probably just ask ChatGPT to write the macro for you.</a><div><br /></div><div>On previous versions of the sheet I divided out the two conferences on separate sheets: NFC and AFC. This year, I’ve put all the teams into one sheet. However, there is a new filter option where you can filter by NFC or AFC or even by division: AFC North, AFC South, etc.<div style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px;"><br /></div><div><a href="https://dsadsadsadsadsa.gumroad.com/l/nflhelmetschedule" style="background-color: white; color: blue; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; text-decoration-line: none;" target="_blank">Download the 2023 NFL Helmet Schedule Spreadsheet here</a><br /><br /><br />Watch the video below to see how the filter works. I also so a tip in Excel how to select multiple objects at once with the mouse. And I walk through the populate helmets macro code as well. Lots of good stuff here!</div></div><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/hWb6GeY5wo8" title="YouTube video player" width="560"></iframe><div><br /></div><div>As you can see, the NFL helmet schedule is printable too. You can save the spreadsheet as a PDF file or print it out and pin it up in your cubicle at work. If you do, please email or tweet me a picture of it hanging up - I'd love to see it!<br /><br />Please note, an email is <a href="https://dsadsadsadsadsa.gumroad.com/l/nflhelmetschedule">required to download it.</a> I do this so you will be automatically updated you if changes or additions are made and will update you when the next year’s schedule is ready. I do not use your email for anything else.<br /><br />As always, I welcome any comments or suggestions about how to fix or improve the sheet! How can I improve this football spreadsheet into something you’ll use all the time during pro-football season? What future features would you like to see?</div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com1tag:blogger.com,1999:blog-7826720847639113939.post-84595992317032892632023-03-11T21:15:00.005-08:002023-03-12T21:47:31.256-07:002023 March Madness SquaresWhen I created my new Super Bowl Squares Unlimited spreadsheet for last month's football game, I had the goal in mind to be able to use the same sheet for other sports, like basketball. I tweaked my template a little and now here it is for the first time - March Madness Squares!<div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWm52MfbqFN7rfs9CMZ6UZ8NLNB-h849rImHr090qiLa9E6HAbF3yBAdg3QiRnMK8TJS30EQ6_VtZLaeTak7Kg-ddmLXl5Qwc61Aoh_hLsjYTacnR-TMdBdd1eqkscDkfZREh3qRI_n5C29XXQ3G7gm8yZUNa9I49_nvbeHIy0gugRQQpYueSfYcLm/s650/march%20madness%20squares.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="330" data-original-width="650" height="205" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWm52MfbqFN7rfs9CMZ6UZ8NLNB-h849rImHr090qiLa9E6HAbF3yBAdg3QiRnMK8TJS30EQ6_VtZLaeTak7Kg-ddmLXl5Qwc61Aoh_hLsjYTacnR-TMdBdd1eqkscDkfZREh3qRI_n5C29XXQ3G7gm8yZUNa9I49_nvbeHIy0gugRQQpYueSfYcLm/w405-h205/march%20madness%20squares.png" width="405" /></a></div><br /><div>Super Bowl squares are a popular game often played during the Super Bowl where participants select squares on a grid, with each square assigned a specific combination of numbers. The goal is to correctly predict the final score of the game based on the last digit of each team's score.<br /><br />While Super Bowl squares are typically played during the Super Bowl, the concept can also be applied to March Madness. To play this game, you can create a grid with 100 squares, just like in the Super Bowl squares game. Instead of using the final score of the game, the grid will be based on the final score of each March Madness game.<br /><br />Each square in the grid represents a combination of the final score of the two teams playing in the game. For example, if the final score of a game is 85-76, the winner would be the square where the "5" intersects with the "6" on the grid. You can assign a prize to each square, and participants can buy as many squares as they want.<br /><div><br /></div><div>There's multiple ways you could go about playing <a href="https://dsadsadsadsadsa.gumroad.com/l/march_madness_squares" target="_blank">March Madness Squares</a>:</div><div><br /></div><div>1. Everyone keeps the same random numbers for every game</div><div>2. The random numbers change for every round</div><div>3. The random numbers change for every game</div><div><br /></div><div>You can play either way with my spreadsheet. I designed it to be very versatile. Payout percentage can change per round as well. Update the settings how you want them then you click Generate Scoreboard once for every game, so 32 times in the first round. There are a lot of other options built in that can be ignored - it's all up to you!</div></div><div><br /></div><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/D1NUToM4PLM" title="YouTube video player" width="560"></iframe><div><br /></div><div><a href="https://dsadsadsadsadsa.gumroad.com/l/march_madness_squares" target="_blank"><b>Download March Madness Squares here.</b></a><br /><br />Usually, when it comes to March Madness I use <a href="https://excelspreadsheetshelp.blogspot.com/2021/03/excel-tips-from-best-2021-march-madness.html" target="_blank">David Tyler's incredible brackets in Excel</a>. I'll still be using them this year but in addition I plan on trying March Madness Squares for the first time. Have you ever played before?<br /><br />Try it and let me know what you think!</div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-90715501083310672962023-01-29T19:53:00.005-08:002023-01-31T09:52:59.152-08:00Super Bowl Squares 2023 - Unlimited Ways to Play<p>Since 54 ways to play still wasn't enough, I've created a new version of my Super Bowl Squares template that should allow nearly limitless ways to play. I'm going to show you how to use it and what make it awesome. But first, let's take a quick look back at the evolution of my football grid game template.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsRVAehmtFqsq11zd9Ie0hOYhK3yhqjEftEWBJkMIiQnubMWP9YttryYj6wo6ElGyYpNWfdZrg7-abGQ98PFRJ92yivFRZcoa_nWNZdi-_xbZDvfgZM3tXWpiNRHvLuQZFw8sQVu5x-5RGKLayUs_YV7xn60ErYMxBGWahn_wvchI6U0PjGa377hub/s671/how%20to%20play%20superbowl%20squares%20game%20in%20excel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="372" data-original-width="671" height="177" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsRVAehmtFqsq11zd9Ie0hOYhK3yhqjEftEWBJkMIiQnubMWP9YttryYj6wo6ElGyYpNWfdZrg7-abGQ98PFRJ92yivFRZcoa_nWNZdi-_xbZDvfgZM3tXWpiNRHvLuQZFw8sQVu5x-5RGKLayUs_YV7xn60ErYMxBGWahn_wvchI6U0PjGa377hub/s320/how%20to%20play%20superbowl%20squares%20game%20in%20excel.JPG" width="320" /></a></div><p>I’ve been making Super Bowl Squares templates available for you to download for over 10 years now. The first version in 2012 was very plain and simple and was basically just meant to be printed.</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9w0zmKIcX8U5TvkpTyBI-HMz-fDxN6Guc8JjhI675cwuiUC7VEVT4kEb86yjPlrJzCeTtufgvFJ11hEWkOf9-U4OAkGOSe2THOXjFZJAU7l8fdY5eoOoUrfvMbjoM8wzVMtKTE-d6ZL1PCnxnVZIJhkmEjXIXTS1tSXJAKfDiOx8FnitPlVPq0zZN/s1079/superbowl%20squares%20pool%20excel.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="683" data-original-width="1079" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9w0zmKIcX8U5TvkpTyBI-HMz-fDxN6Guc8JjhI675cwuiUC7VEVT4kEb86yjPlrJzCeTtufgvFJ11hEWkOf9-U4OAkGOSe2THOXjFZJAU7l8fdY5eoOoUrfvMbjoM8wzVMtKTE-d6ZL1PCnxnVZIJhkmEjXIXTS1tSXJAKfDiOx8FnitPlVPq0zZN/s320/superbowl%20squares%20pool%20excel.JPG" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><p></p><p>I started adding more features and automation in 2014, beginning with a button you would click that runs a macro to generate the random numbers. </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvD6c-2hbDhQWS5p86_RiypeCeyc7JJIpQJdXy7-u_HD-DSakvc89he2vtc6C3NFq3KjBV4w2Nd6BPjjuy3A5Rp6oFI13gBfwYv7AIZatNUt5nL3Bwu3pSU15SNStZzqSl6ytfL4jbK0y64WY-pq-DwWRB96sse9E3kk1i8n_BuW1jFq_WHJqdW3Te/s1336/2014%20super%20bowl%20squares%20spreadsheet.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="546" data-original-width="1336" height="131" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvD6c-2hbDhQWS5p86_RiypeCeyc7JJIpQJdXy7-u_HD-DSakvc89he2vtc6C3NFq3KjBV4w2Nd6BPjjuy3A5Rp6oFI13gBfwYv7AIZatNUt5nL3Bwu3pSU15SNStZzqSl6ytfL4jbK0y64WY-pq-DwWRB96sse9E3kk1i8n_BuW1jFq_WHJqdW3Te/s320/2014%20super%20bowl%20squares%20spreadsheet.PNG" width="320" /></a></div><p>The problem I soon discovered is there are countless ways to play Super Bowl Squares. Initially, I tried to account for different ways to play by having multiple sheets within the workbook. But this got very messy very quickly. A new user would open the template and could be very confused by all the sheets. I wanted a better solution.</p><p>In 2021, I released a new version that featured 54 different ways to play Super Bowl Squares all contained within one easy to use spreadsheet. When you first open the sheet, it has a nice clean look with only one sheet visible. You select the options for how you want to play, click Generate Squares, and it sets up the sheet exactly for the version you want to play.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimybMURj-W2UnMDTBn7UPElul-bO0_05iBE85O--LV2wo0LxTzBj3IQ_Wjq4EgkHcO8zV8GwH2NCoGJWBELLf_2EYeNeECPnYPm7cXB7ICGOtuNhc9ZV7h2a_zHBkFCSX2TDvdK7ipfsxr9r57hJkuROW6VNZpQSwFlPTiK_AdnIE10jOXWsjnFFX0/s1500/54%20ways%20to%20play%20superbowl%20squares%20game.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="625" data-original-width="1500" height="133" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimybMURj-W2UnMDTBn7UPElul-bO0_05iBE85O--LV2wo0LxTzBj3IQ_Wjq4EgkHcO8zV8GwH2NCoGJWBELLf_2EYeNeECPnYPm7cXB7ICGOtuNhc9ZV7h2a_zHBkFCSX2TDvdK7ipfsxr9r57hJkuROW6VNZpQSwFlPTiK_AdnIE10jOXWsjnFFX0/s320/54%20ways%20to%20play%20superbowl%20squares%20game.png" width="320" /></a></div><br /><p>The way it works is all the scoring systems are already built into the sheet. If you go to the Squares or the Manager sheet you will find many hidden rows or columns. The Generate Squares macro simply hides or unhides the data based on the user’s selections.</p><p>54 ways to play is a lot – but it is still not enough. Over the years I continued to receive countless requests for different ways to play. I don’t have time to make very specific sheets for each individual request – I needed a solution to account for unlimited ways to play. </p><p>That’s when I had the idea for <a href="https://gumroad.com/l/superbowlsquares" target="_blank">Super Bowl Squares Unlimited</a>. The main difference in how it works is there are no pre-built scoring systems. Instead, they are built on the fly by macros. </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUqfSF0tjWI3UfOrSexph84BcmC50qJiyYyX85ExLWY-kQUzheBSbb7yE2OKOPlklE22BvUjLbKi2TpFQQx2_6rMBFCP2UOIRtadRPZjiRcA7lcVwczQlgXDjYBjLeyQST4ZmEyACFk4jAN6uOuAyb44zVBVHTkfY_R8nL7SyHV57neqK8K2kBioXv/s804/super%20bowl%20squares%20unlimited.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="671" data-original-width="804" height="267" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUqfSF0tjWI3UfOrSexph84BcmC50qJiyYyX85ExLWY-kQUzheBSbb7yE2OKOPlklE22BvUjLbKi2TpFQQx2_6rMBFCP2UOIRtadRPZjiRcA7lcVwczQlgXDjYBjLeyQST4ZmEyACFk4jAN6uOuAyb44zVBVHTkfY_R8nL7SyHV57neqK8K2kBioXv/s320/super%20bowl%20squares%20unlimited.png" width="320" /></a></div><br /><p><b><span style="color: red;">If you’re using iOS or cannot enable macros you will not be able to use this sheet, sorry. The is a simple printable PDF in the download and here is a Google sheet.</span></b></p><p>The first thing you’ll want to do is go to the Manager sheet and complete columns B and C by inputting all the player names and using the drop down to select how many squares for each. You can type the names manually into each square but you would also have to list the names on the Manager sheet and make sure they match (no typos). </p><p>Back to the Squares sheet, the buttons in the Grid Tools box are pretty self explanatory. On the squares sheet, click the Random Names button to populate the grid with the player names in random positions. Click Randomize Numbers to generate the list of random numbers. Similar to previous sheet.</p><p>The main difference that gives this sheet more flexibility is the score boards are not built in, they are generated when the user clicks the Generate Scoreboard button every time there is a scoring event. Meaning, the settings can be changed. Let’s look at some examples.</p><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/Vv6J4wbXKpU" title="YouTube video player" width="560"></iframe><div><br /></div><div><a href="https://gumroad.com/l/superbowlsquares" target="_blank"><b>Download 2023 Super Bowl Squares here</b></a></div><p>So that’s how you use the Super Bowl Squares Unlimited spreadsheet template. I’m excited to hear your thoughts. Is it better than the previous sheet? This is brand new and hasn’t been tested too much so there could be some bugs. But I’d love to know your thoughts on which sheet you will use and why so leave me a comment or email. Thanks for watching and have fun playing Super Bowl Squares!</p><div><br /></div><p><br /></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com2tag:blogger.com,1999:blog-7826720847639113939.post-16498795234147546622023-01-22T19:32:00.000-08:002023-01-22T19:32:43.864-08:00Elevate Your Sports Betting Game with Our Free Excel Tracking Tool<p>Sports gambling just became legal here in Ohio and we've been flooded with sign-up offers. Since just about every sportsbook will give you free bonus bets (around $200) for a small ($5 or $10) deposit, I decided to dabble a little bit into sports betting to see what all the fuss is about. I signed up for three different sportsbook sites and quickly realized I needed a way to organize and track all my bets.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZkjRd-c0GFc288IXtWcmc7kUh15ZISavRtS-D8ojWLWnupidKw1k2JWz9JAb_MFlfWxCHVsO0pw1ZpbCg4Jpb0nLzcscaQ5F_h5CFxrnRploCSM-NbjuHixyQOZAGDckd_W4hoxCbQnwKVlsykfqJkqsUU261mCaYg99NDA0J2NdDeK4IrmBrudEu/s541/sportbook%20tracking%20template%20in%20excel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="313" data-original-width="541" height="185" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZkjRd-c0GFc288IXtWcmc7kUh15ZISavRtS-D8ojWLWnupidKw1k2JWz9JAb_MFlfWxCHVsO0pw1ZpbCg4Jpb0nLzcscaQ5F_h5CFxrnRploCSM-NbjuHixyQOZAGDckd_W4hoxCbQnwKVlsykfqJkqsUU261mCaYg99NDA0J2NdDeK4IrmBrudEu/s320/sportbook%20tracking%20template%20in%20excel.JPG" width="320" /></a></div><br /><p>Naturally, <a href="https://dsadsadsadsadsa.gumroad.com/l/sports-bet-tracker" target="_blank">I decided to make an Excel template</a> to track which bets I'd made, track my earnings, and make sure I was still making a profit. First, I created a Lists sheet for all my drop down lists including which sportsbook, which sport, result, and so on. Personally, so far I've only stuck to football because that's what I follow the most and already have some interest and knowledge in it.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-hg8zONOCMaJJAPj2JKvtOKrxEAtO8WlXgMeqfa3_zaD4x7Ig5zHvEy2Wdr20-fE6zYeUQbYzd0Jjtxh3cV_BDflni_8mfG-hn_vrGOO9rFeFtZ5DggFtX7crtogsnEg1AxdAj0UaF6c9UERam14iuEbiFjsktys13XHzs5OhYhi_o5_uyLLN0QoG/s479/the%20lists%20sheet.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="173" data-original-width="479" height="135" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-hg8zONOCMaJJAPj2JKvtOKrxEAtO8WlXgMeqfa3_zaD4x7Ig5zHvEy2Wdr20-fE6zYeUQbYzd0Jjtxh3cV_BDflni_8mfG-hn_vrGOO9rFeFtZ5DggFtX7crtogsnEg1AxdAj0UaF6c9UERam14iuEbiFjsktys13XHzs5OhYhi_o5_uyLLN0QoG/w372-h135/the%20lists%20sheet.JPG" width="372" /></a></div><br /><p>The Bet Tracker sheet is used to track every individual bet including the odds, wager amount, and formulas to determine the potential winnings and the actual result.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7PCvtGexI-VvgwjBFqxYIvjrz2NmNHobI_ZR-92NdOU77zli__Nmplv825Gs4mNxGkFSAswiPw6FLn4Dyyefiy1QxkFi4mioK6Y6tB7UHVkCFL2B4I0osuw9TmMIYGdN1MpGSX7LI0LvV30HUn73XT5KNjKYieMMcL6Z-3GKfOVLl3-vQ2IVjP6OH/s1048/sports%20bet%20tracking%20excel%20template%20spreadsheet.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="bet tracker sheet" border="0" data-original-height="508" data-original-width="1048" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7PCvtGexI-VvgwjBFqxYIvjrz2NmNHobI_ZR-92NdOU77zli__Nmplv825Gs4mNxGkFSAswiPw6FLn4Dyyefiy1QxkFi4mioK6Y6tB7UHVkCFL2B4I0osuw9TmMIYGdN1MpGSX7LI0LvV30HUn73XT5KNjKYieMMcL6Z-3GKfOVLl3-vQ2IVjP6OH/w419-h203/sports%20bet%20tracking%20excel%20template%20spreadsheet.JPG" width="419" /></a></div><br /><p>When you bet with your own money, if you win the bet you get the winnings plus your original bet back. But when you use Bonus money to place your bet, that "fake" money is not added to the winnings. I had to account for this in my Potential Winnings column formula.</p><p>If your own money then: <b>Winnings = ((Odds/100)*Wager)+Original Wager</b></p><p>If bonus money then: <b>Winnings = (Odds/100)*Wager)</b></p><p><b>=IF(E2="My$",((G2/100)*F2)+F2,((G2/100)*F2))</b></p><p>If you get lucky and win a bet, you'll be able to withdraw the money into your own bank or Paypal account. There's a summary sheet to track exactly how much money you've put in versus what has come out. The most important stat to me is my running total, and making sure it is well in the positive otherwise it's time to quit. </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9op9LQ7lVZAgF32NRccYajTPjIeWdI38LqTl6cLE1f9mzwVDpyFHJhJ3LWyJKoLg0XxP-xSkdp9bFYaitobQfeMC5M84mcOwR7MLXcYi7LZ4lNSMFmzazy8Kp0cKvZuLzZRwD8L_eVqVUBed7pRbbVee7WSIGvRh_vgbqQKSvs4XBwvA9DujFF6Lt/s660/sports%20betting%20results%20and%20strategies.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="418" data-original-width="660" height="230" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9op9LQ7lVZAgF32NRccYajTPjIeWdI38LqTl6cLE1f9mzwVDpyFHJhJ3LWyJKoLg0XxP-xSkdp9bFYaitobQfeMC5M84mcOwR7MLXcYi7LZ4lNSMFmzazy8Kp0cKvZuLzZRwD8L_eVqVUBed7pRbbVee7WSIGvRh_vgbqQKSvs4XBwvA9DujFF6Lt/w362-h230/sports%20betting%20results%20and%20strategies.JPG" width="362" /></a></div><br /><p>How have I done? In the past two weeks, I placed 33 bets and won 8 of them. I've only had to input $27 of my own dollars, while I've gotten to bet $650 bonus bucks. I'm not huge into taking risks, so instead of betting on crazy parleys with larger payouts, I've bet on safer, more likely outcomes but with smaller rewards. What do I plan to do with my winnings? Probably invest in an AI company <a href="https://excelspreadsheetshelp.blogspot.com/2023/01/using-openais-chatgpt-to-update-my-year.html" target="_blank">since they're changing the game</a>.</p><p>In all my years of making sports related Excel spreadsheet templates, I've never really gambled on sports. I'm not sure I'll keep gambling once my free funds run out, but it is a fun experiment while it lasts. Like playing <a href="https://dsadsadsadsadsa.gumroad.com/l/fantasyfootball101" target="_blank">fantasy football</a> or <a href="https://dsadsadsadsadsa.gumroad.com/l/superbowlsquares" target="_blank">Super Bowl Squares</a>, it gives you an extra incentive for watching the game. Hey, speaking of Super Bowl Squares, I've got a new version coming out this weekend and I can't wait to hear your thoughts on it...</p><p><a href="https://dsadsadsadsadsa.gumroad.com/l/sports-bet-tracker" target="_blank">.Download Sports Bet Tracker.xlsx file here</a></p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com2tag:blogger.com,1999:blog-7826720847639113939.post-34489687496813922452023-01-08T19:36:00.000-08:002023-01-08T19:36:07.169-08:00Using OpenAI's ChatGPT to Update My Year in Pixels Excel Template<p>I've been seeing more and more videos on TikTok about this mysterious new artificial intelligence program called ChatGPT. People are saying it will eventually replace Google search. But what really got my attention was a demonstration of ChatGPT writing VBA macros for Microsoft Excel. Now I knew I had to try it for myself. </p><p>I made a <a href="https://dsadsadsadsadsa.gumroad.com/l/year-in-pixels" target="_blank">Year in Pixels Excel template</a> to track my daily mood back in 2018 but haven't updated it lately. Since it's the start of a new year, I recently received some new comments and questions about how to use and modify the spreadsheet so I figured now would be a great time to update it and add a few new features. Then I had an idea - why don't I use this opportunity to update my mood tracking Excel template to try out ChatGPT? Let's give it a go!</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv1zTY315_E2Q0v_CSi5gGZ01mubLu0ly2zsFxqmKIcrxCKSjbuH75ByYFp7eGVS-15rCnKYPKVMN9FZJs1JtNr-fBxX2jqzCQm0CwV9eL9GaTbWUlgiy5vEHi3tQErW26PUfydHNAaqbWlyY56K2znKlGGnox02JO8JX_3PDKOV9CX9Zz4M_Si9RW/s1280/Year%20in%20Pixels.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="720" data-original-width="1280" height="180" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjv1zTY315_E2Q0v_CSi5gGZ01mubLu0ly2zsFxqmKIcrxCKSjbuH75ByYFp7eGVS-15rCnKYPKVMN9FZJs1JtNr-fBxX2jqzCQm0CwV9eL9GaTbWUlgiy5vEHi3tQErW26PUfydHNAaqbWlyY56K2znKlGGnox02JO8JX_3PDKOV9CX9Zz4M_Si9RW/s320/Year%20in%20Pixels.png" width="320" /></a></div><br /><p></p><h3 style="text-align: left;">How to have cell fill color conditional formatting change based on the color of another cell?</h3><p>The request from a user that got me thinking was "how to easily change the colors in my year in pixels sheet?" An experienced Excel user could probably ascertain the colors are applied by conditional formatting formula rules. However, for users who are not Excel savvy, it's not very obvious and there should be an easier way to change the colors. As far as I know, there is none, or at least no easy way in my current version of Excel, to have the fill color in the conditional formatting reference another cell's color. So instead, I will have a macro update the conditional formatting rules at the click of a button. Let's jump over to <a href="https://chat.openai.com/chat">ChatGPT.</a></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgc8ck7YBC6G8qRGexJ9oHQqCWBuDMFND9FD0xh0riaW7prwwy49wTv_mwv1XSb2knuS7SbBPKWAPlfejDAEp9DNA_vTbTTMjsypVwc4mG5CuDKrM-RQgYPOPZsFEL-Ze1rkG71ubCy0b9ngl0vXiDvNpl6N11dudl_tSlVZaI9Gn429THcWo76HxLQ/s1600/conditional%20formatting%20based%20on%20cell%20color.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="860" data-original-width="1600" height="235" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgc8ck7YBC6G8qRGexJ9oHQqCWBuDMFND9FD0xh0riaW7prwwy49wTv_mwv1XSb2knuS7SbBPKWAPlfejDAEp9DNA_vTbTTMjsypVwc4mG5CuDKrM-RQgYPOPZsFEL-Ze1rkG71ubCy0b9ngl0vXiDvNpl6N11dudl_tSlVZaI9Gn429THcWo76HxLQ/w437-h235/conditional%20formatting%20based%20on%20cell%20color.png" width="437" /></a></div><h3 style="text-align: left;">How does ChatGPT work?</h3><p><span style="background-color: white;">Here's the answer after asking it directly: </span></p><p><span style="background-color: white;"><span face="Söhne, ui-sans-serif, system-ui, -apple-system, "Segoe UI", Roboto, Ubuntu, Cantarell, "Noto Sans", sans-serif, "Helvetica Neue", Arial, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Noto Color Emoji"" style="color: #374151; font-size: 16px; white-space: pre-wrap;"><i>GPT (Generative Pre-trained Transformer) is a type of language model developed by OpenAI that uses a transformer neural network architecture to generate text. GPT is trained on a large dataset of text and uses this training to generate new text that is similar in style and content to the training data.</i></span></span></p><p style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-size: 16px; margin: 1.25em 0px; white-space: pre-wrap;"><span style="background-color: white; font-family: inherit;"><i>ChatGPT is a variant of GPT that has been fine-tuned on a specific task, such as having conversations with humans. To fine-tune GPT for this task, ChatGPT is trained on a large dataset of conversation data and learns to predict the next word in a conversation based on the words that came before it.</i></span></p><p style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-size: 16px; margin: 1.25em 0px 0px; white-space: pre-wrap;"><span style="background-color: white; font-family: inherit;"><i>When you input a message to ChatGPT, it processes the message and uses its knowledge of language and conversation patterns to generate a response. It does this by predicting the next word in the conversation based on the input it has received and the context of the conversation so far. The output of ChatGPT is then a response that is generated based on this prediction.</i></span></p><div style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-size: 16px; margin: 1.25em 0px 0px; text-align: left; white-space: pre-wrap;"><span style="background-color: white; font-family: inherit;">One thing to keep in mind right off the bat: ChatGPT is not always right. It says it right there on the main page under limitations: <b>may occasionally generate incorrect information; may occasionally produce harmful instructions.</b></span></div><div style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-size: 16px; margin: 1.25em 0px 0px; text-align: left; white-space: pre-wrap;"><span style="background-color: white; font-family: inherit;">For example, I asked "list the ten tallest roller coasters in the world" which should be fairly easy and instead it produces a list that is incorrect and doesn't even make sense.</span></div><div style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-family: Söhne, ui-sans-serif, system-ui, -apple-system, "Segoe UI", Roboto, Ubuntu, Cantarell, "Noto Sans", sans-serif, "Helvetica Neue", Arial, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Noto Color Emoji"; font-size: 16px; margin: 1.25em 0px 0px; text-align: left; white-space: pre-wrap;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzDPDP8vMmUlH5sSa0-d7FnzCqSevhzNhBe01IiSCU8gED_s27dDze-0006Kct2XIGor5yFuHdXr5K5GNFYP2SR9z0gz0bYh5iudiyFE7XvE3uY-YpkiIj-rcFqMislr4NKJyGdLVMT_UrE0nPT7NfgNm35Sq_exUy8ywN767fqKLi-7aCRDxKfIUI/s1033/ten%20tallest%20roller%20coasters.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="342" data-original-width="1033" height="130" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzDPDP8vMmUlH5sSa0-d7FnzCqSevhzNhBe01IiSCU8gED_s27dDze-0006Kct2XIGor5yFuHdXr5K5GNFYP2SR9z0gz0bYh5iudiyFE7XvE3uY-YpkiIj-rcFqMislr4NKJyGdLVMT_UrE0nPT7NfgNm35Sq_exUy8ywN767fqKLi-7aCRDxKfIUI/w393-h130/ten%20tallest%20roller%20coasters.JPG" width="393" /></a></div><br /><span style="background-color: white;"><br /></span></div><div style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-size: 16px; margin: 1.25em 0px 0px; text-align: left; white-space: pre-wrap;"><span style="background-color: white; font-family: inherit;">Not a great start but let's see how it does creating a macro from scratch. </span></div><div style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-size: 16px; margin: 1.25em 0px 0px; text-align: left; white-space: pre-wrap;"><span style="background-color: white; font-family: inherit;"><h2 style="--artdeco-reset-typography_getfontsize: 2.4rem; --artdeco-reset-typography_getlineheight: 1.33333; border: var(--artdeco-reset-base-border-zero); box-sizing: inherit; color: rgba(0, 0, 0, 0.9); font-size: var(--font-size-large); line-height: 1.4; margin: 2.4rem 0px; outline: var(--artdeco-reset-base-outline-zero); padding: var(--artdeco-reset-base-padding-zero); text-align: left; vertical-align: var(--artdeco-reset-base-vertical-align-baseline); white-space: normal;"><span style="background: var(--artdeco-reset-base-background-transparent); border: var(--artdeco-reset-base-border-zero); box-sizing: inherit; font-size: var(--artdeco-reset-base-font-size-hundred-percent); margin: var(--artdeco-reset-base-margin-zero); outline: var(--artdeco-reset-base-outline-zero); padding: var(--artdeco-reset-base-padding-zero); vertical-align: var(--artdeco-reset-base-vertical-align-baseline);">Creating Custom VBA Macros Automatically with ChatGPT</span></h2></span></div><div style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-size: 16px; margin: 1.25em 0px 0px; text-align: left; white-space: pre-wrap;"><span style="background-color: white; font-family: inherit;">I wasn't sure how specific I needed to be so I decided to err on the side of caution, plus I thought the more detailed I am the less modification I'll probably need to do later. Now I ask ChatGPT: "create a vba macro to make a new conditional formatting rule where range is C4 to N34, if cell value = 5 then change the fill color to the same color as in cell P12." Then it began typing and my jaw dropped.</span></div><div style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; color: #374151; font-family: Söhne, ui-sans-serif, system-ui, -apple-system, "Segoe UI", Roboto, Ubuntu, Cantarell, "Noto Sans", sans-serif, "Helvetica Neue", Arial, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Noto Color Emoji"; font-size: 16px; margin: 1.25em 0px 0px; text-align: left; white-space: pre-wrap;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqfvJkyc86t9sI1MhgE1g5n-z27E8hV0BnVULrt5X_RXnN8MofEpWVoM3pL5kdVqz-wzc-u87p6A4aAWbhV8ZCrXKFf4mM77rC9JIVKhuKUfh_-1EJRdUizDuwLvBIGYHPZi63CUmWpVLkgoQ7Z1uJouMwKFwvD2t-ilrljg_gwIgGZQSuFOvdn6Zp/s1280/chatgpt%20excel%20macro.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="720" data-original-width="1280" height="180" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqfvJkyc86t9sI1MhgE1g5n-z27E8hV0BnVULrt5X_RXnN8MofEpWVoM3pL5kdVqz-wzc-u87p6A4aAWbhV8ZCrXKFf4mM77rC9JIVKhuKUfh_-1EJRdUizDuwLvBIGYHPZi63CUmWpVLkgoQ7Z1uJouMwKFwvD2t-ilrljg_gwIgGZQSuFOvdn6Zp/s320/chatgpt%20excel%20macro.gif" width="320" /></a></div></div><div style="--tw-border-spacing-x: 0; --tw-border-spacing-y: 0; --tw-ring-color: rgba(59,130,246,0.5); --tw-ring-offset-color: #fff; --tw-ring-offset-shadow: 0 0 transparent; --tw-ring-offset-width: 0px; --tw-ring-shadow: 0 0 transparent; --tw-rotate: 0; --tw-scale-x: 1; --tw-scale-y: 1; --tw-scroll-snap-strictness: proximity; --tw-shadow-colored: 0 0 transparent; --tw-shadow: 0 0 transparent; --tw-skew-x: 0; --tw-skew-y: 0; --tw-translate-x: 0; --tw-translate-y: 0; border: 0px solid rgb(217, 217, 227); box-sizing: border-box; margin: 1.25em 0px 0px; text-align: left;"><p style="text-align: left;"><span style="color: #374151;"><span style="white-space: pre-wrap;">I copied and pasted the code into Excel VBA editor and it worked! One thing I forgot is this macro will just add new rules applied to the same range. I need to delete all the existing formatting rules before adding new ones, so I ask ChapGPT:</span></span></p><p style="text-align: left;"><span style="color: #374151;"></span></p><div class="separator" style="clear: both; text-align: center;"><span style="color: #374151;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsHmLYrdd5mK1HcUQiZI6Mn4_9Ee5CFu27A5iE6U3M96crPJM-iZdRVJ-bVoFPqwoIho1rWl71zkEBRM6L5NvzhrCH8l7EBcDMw3SfZhmywe_7bqS7Cu9wbGve9duiRWLKfLxfvyZu1PIH9sZ-LnwFso95LGhl5qx0dX-bLA12AXC8QsJSC3H-ZBto/s799/delete%20conditional%20formatting%20macro%20generated%20by%20chatgpt%20ai.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="636" data-original-width="799" height="255" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsHmLYrdd5mK1HcUQiZI6Mn4_9Ee5CFu27A5iE6U3M96crPJM-iZdRVJ-bVoFPqwoIho1rWl71zkEBRM6L5NvzhrCH8l7EBcDMw3SfZhmywe_7bqS7Cu9wbGve9duiRWLKfLxfvyZu1PIH9sZ-LnwFso95LGhl5qx0dX-bLA12AXC8QsJSC3H-ZBto/s320/delete%20conditional%20formatting%20macro%20generated%20by%20chatgpt%20ai.png" width="320" /></a></span></div><div class="separator" style="clear: both; text-align: center;"><span style="color: #374151;"><br /></span></div><p></p><div class="separator" style="clear: both; text-align: left;">I added the above code to the earlier response and linked it to a newly added button on my sheet. Now a user can change the fill color of a cell, click the button, and the fill colors update automatically to match.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">If you want to see all this play out in real time, watch the video I recorded below:</div><div class="separator" style="clear: both; text-align: left;"><br /></div><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/OE8dOoeN5j8" title="YouTube video player" width="560"></iframe><div class="separator" style="clear: both; text-align: center;"><h2 style="clear: both; text-align: left;">Thoughts on the Future of ChatGPT</h2><div class="separator" style="clear: both; text-align: left;">It's awesome that it doesn't just spit out the code, but it also suggests how you might need to modify it AND tells you how to run the macro as well. Even though I've only asked it to make simple macros, I already see how this program could save a lot of time.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">I'm not done experimenting but so far ChatGPT seems like a much better option over Recoding macros or Google searches that might take you a few tries to find exactly what you're looking for. Especially when you can get custom code on the fly. On one hand, I feel a little obsolete, but on the other I also don't think ChatGPT will completely be replacing programmers just yet as you can see I still had to understand the code and modify it to fit my exact needs.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0VmwA33ZmL6ZsDTBi_OAxdijm29BFp9q3PVmFpaBZ3qWueq21W7r6iA9aaWkww20xu2DoF40xCg33yw1b3YGJ0QukKGcClGaAw8x_ao6n1oGD8woFzFfMKZCHBH1rtEnWoDThw3yCd6VaZ2t9Nl6qhmhuH7-6k5IS1AFG8RGC4FB6Y6v0iXjwJqtq/s985/year%20in%20pixels%20spreadsheet.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="630" data-original-width="985" height="283" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0VmwA33ZmL6ZsDTBi_OAxdijm29BFp9q3PVmFpaBZ3qWueq21W7r6iA9aaWkww20xu2DoF40xCg33yw1b3YGJ0QukKGcClGaAw8x_ao6n1oGD8woFzFfMKZCHBH1rtEnWoDThw3yCd6VaZ2t9Nl6qhmhuH7-6k5IS1AFG8RGC4FB6Y6v0iXjwJqtq/w442-h283/year%20in%20pixels%20spreadsheet.png" width="442" /></a></div><div class="separator" style="clear: both; text-align: left;"><br /></div></div><p style="text-align: left;"><span style="color: #374151;"><span style="font-family: inherit;"><span style="background-color: white; color: rgba(0, 0, 0, 0.9); font-size: 20px;">Download my </span><a href="https://dsadsadsadsadsa.gumroad.com/l/year-in-pixels" style="background-color: white; border: var(--artdeco-reset-link-border-zero); box-sizing: inherit; font-size: 20px; font-weight: var(--font-weight-bold); margin: var(--artdeco-reset-base-margin-zero); overflow-wrap: break-word; padding: var(--artdeco-reset-base-padding-zero); text-decoration: var(--artdeco-reset-link-text-decoration-none); touch-action: manipulation; vertical-align: var(--artdeco-reset-base-vertical-align-baseline);">Year in Pixels template for free here</a><span style="background-color: white; color: rgba(0, 0, 0, 0.9); font-size: 20px;"> and try it for yourself. Open the macro editor to view the final codes written by ChatGPT.</span></span><br /><span style="white-space: pre-wrap;"><br /></span></span></p></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-55501403017969119502022-12-04T19:53:00.006-08:002022-12-05T18:10:51.462-08:002022 College Football Bowl Prediction Pool<p><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 13.3333px; white-space: pre-wrap;">The college football conference championships were played this past weekend which means the 2022 NCAA college football bowl season is here again! Therefore, it’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season (</span><a href="https://excelspreadsheetshelp.blogspot.com/2021/11/gift-guide-for-excel-users-2021.html" style="color: blue; font-family: "Trebuchet MS"; font-size: 13.3333px; text-decoration-line: none; white-space: pre-wrap;" target="_blank">other than giving and receiving gifts</a><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 13.3333px; white-space: pre-wrap;">) is being able to talk trash to your relatives about their terrible bowl picks. </span></p><p><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 13.3333px; white-space: pre-wrap;">This year has the added bonus of not just single bowl games but the ninth year of a four team playoff to determine the national champion. And it was recently announced this will be the second to last year of the four team format as starting in 2024 the college football playoff will expand to twelve teams. Can't wait to see how that plays out!</span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsuId_zEN4Xb9NgL3iYY8yjZLukVohV1R8Z-MIJdghiMxbS-TusgMqgU5MfnyK0zqZ2C0aVH1B7UIlI6qFyvOsV3GAhSGg7Bmxqnijc790h4nTKoV5O9DchFptBEqHR5Nai2_5duPujZx89XGVKfWDBlypExxzmX5nvuVJt2U6BRQu3ghlmKqEgd7k/s1515/2022%20college%20football%20bowl%20prediction%20pool%20spreadsheet%20template%20office%20pools.JPG" style="margin-left: 1em; margin-right: 1em;"><img alt="ncaa college football bowl pickem game in excel" border="0" data-original-height="578" data-original-width="1515" height="175" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsuId_zEN4Xb9NgL3iYY8yjZLukVohV1R8Z-MIJdghiMxbS-TusgMqgU5MfnyK0zqZ2C0aVH1B7UIlI6qFyvOsV3GAhSGg7Bmxqnijc790h4nTKoV5O9DchFptBEqHR5Nai2_5duPujZx89XGVKfWDBlypExxzmX5nvuVJt2U6BRQu3ghlmKqEgd7k/w461-h175/2022%20college%20football%20bowl%20prediction%20pool%20spreadsheet%20template%20office%20pools.JPG" width="461" /></a></div><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; white-space: pre-wrap;"><p><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; white-space: pre-wrap;"><br /></span></p>But let's not get ahead of ourselves and just enjoy that fact that for the first time the playoff doesn't have Alabama or Clemson in it! It's good to see at least one new team make the CFB playoff. Here's the full breakdown of bowl teams per conference:</span><div><span style="color: #3e3e3e; font-family: Trebuchet MS;"><span style="font-size: 13.3333px; white-space: pre-wrap;"><br /></span></span><div><span style="color: #3e3e3e; font-family: Trebuchet MS;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjL8dwQvamPtpNFaDMAc-mJzIYZQyUSRSzuao-jUrFHimaotpknCE2GOLsny9oE4QEF1NjpuFNtP5TanYL5uo-5JxEMlI36RhnrJv33m25OarUOpl0iTBN3D8u6mlgtfmor6FzqO4j9qqjVOMt_0TpWrFagtvun9zUSPnEx7aRkM-CWlPRwX6rtyAO/s1653/2022%20college%20football%20number%20of%20bowl%20teams%20for%20each%20conference.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="bowl pickem excel game template college football" border="0" data-original-height="993" data-original-width="1653" height="231" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjL8dwQvamPtpNFaDMAc-mJzIYZQyUSRSzuao-jUrFHimaotpknCE2GOLsny9oE4QEF1NjpuFNtP5TanYL5uo-5JxEMlI36RhnrJv33m25OarUOpl0iTBN3D8u6mlgtfmor6FzqO4j9qqjVOMt_0TpWrFagtvun9zUSPnEx7aRkM-CWlPRwX6rtyAO/w386-h231/2022%20college%20football%20number%20of%20bowl%20teams%20for%20each%20conference.JPG" width="386" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><br /></div></span><p><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; white-space: pre-wrap;">There are multiple ways to play Bowl pick'em. Features for this year's bowl prediction pool over the previous college football bowl pool manager spreadsheets include the following:</span></p><ul style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.4; margin: 0px; padding-inline-start: 48px; padding: 0px 2.5em;"><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 12pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Easy method to make each bowl game worth a different point value, so the national championship game and semi-finals can be worth more points, or however you want to customize it.</span></p></li><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Updated leaderboard tab with new stats</span></p></li><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Separate entry sheet to pass out to participants or co-workers that can be imported automatically by a built-in macro</span></p></li><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Complete NCAA college football bowl schedule with game times and TV stations</span></p></li><li aria-level="1" dir="ltr" style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; margin: 0px 0px 0.25em; padding: 0px; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 0pt;"><span style="font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">New stat sheet to track each conference's record during bowl season. Graph shows total conference teams and total conference wins</span></p></li></ul><div><p dir="ltr" style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">The bowl prediction sheets include the football helmet designs for every team (taken from my college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!</span></p><p dir="ltr" style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><b><a href="https://dsadsadsadsadsa.gumroad.com/l/cfpbowlsheet" style="color: blue; text-decoration-line: none;" target="_blank">Download the CFP Pool Manager and Single Entry Form here</a></b></span></p><p dir="ltr" style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/q3dztEOa0bw" title="YouTube video player" width="560"></iframe></span></p><h3 style="background-color: white; color: #3e3e3e; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt; text-align: left;"><span style="font-family: Trebuchet MS;"><span style="font-size: 13.3333px; white-space: pre-wrap;">Bowl Pick'em Game For Google Sheets</span></span></h3><p dir="ltr" style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Unlike many of my other spreadsheets, macros are not critical for the Bowl prediction pool. If you have a Google drive account you should be able to upload the pool manager and use it inside Google sheets. Participants can make their picks directly in the sheet using the drop down lists and everyone can see the scoreboard update in real time. All the formulas should work. Insert new columns where it says in order to add more players. The macro to import single player picks won't work but it is not needed. Try it if you want to play that way and let me know if you find any issues in Google sheets.</span></p><h3 style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt; text-align: left;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">College Football Bowl Pick'em with Confidence Points</span></h3><p dir="ltr" style="background-color: white; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">If you watch the video above you can see how this works. E</span><span style="background-color: transparent;"><span style="color: #3e3e3e; font-family: Trebuchet MS, Trebuchet, sans-serif;"><span style="font-size: 13px;">ach player can select confidence picks where they rank each game according to how confident they are in their pick. If you're super confident Georgia is going to roll over Ohio State you would rank that game 42 from the drop down list and would receive 42 points if you're correct. If you're not so sure Michigan will beat TCU you might only rank that game 1 or 2 points. </span></span></span></p><p dir="ltr" style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLTJHcUsF9g5XWXdhChaHk2fWlKaVosTOnioxHRSO9SASgKVeilrSBGS5XUQaL1fF2Auc41fS8NRsmcMSeyna7PjfE51H5pEG9BpZ4mvp7blnqDrIJGk1AoNLEgM5oShmCNuckKtv1MLPUAClQTcSOAvOhW9q9AO1jkdMbJQvMd6_7TkPh7f4LOWBe/s1378/college%20bowl%20game%20confidence%20picks%20in%20excel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="470" data-original-width="1378" height="119" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLTJHcUsF9g5XWXdhChaHk2fWlKaVosTOnioxHRSO9SASgKVeilrSBGS5XUQaL1fF2Auc41fS8NRsmcMSeyna7PjfE51H5pEG9BpZ4mvp7blnqDrIJGk1AoNLEgM5oShmCNuckKtv1MLPUAClQTcSOAvOhW9q9AO1jkdMbJQvMd6_7TkPh7f4LOWBe/w348-h119/college%20bowl%20game%20confidence%20picks%20in%20excel.JPG" width="348" /></a></div><span style="background-color: transparent;"><br /></span><p></p><p dir="ltr" style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><a href="https://gumroad.com/l/cfpbowlsheet" style="color: blue; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; text-align: center; text-decoration-line: none; white-space: normal;"><span style="color: red; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre;">Download the 2022 CFP Bowl Prediction Pool Manager.xlsm file here</span></a></span></p><p dir="ltr" style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", Trebuchet, sans-serif; font-size: 13px; line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;">Have fun! As always, I welcome any feedback or ideas for improvement.</p></div></div></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com12tag:blogger.com,1999:blog-7826720847639113939.post-16312216648594769802022-06-21T19:31:00.000-07:002022-06-21T19:31:18.651-07:00How to change relative hyperlink to exact link in Excel<p>You may have used the HYPERLINK function in Excel before but what you may not have released is your links can be either relative or absolute (or sometimes called exact or specific). What does this even mean? And how do you change from relative to exact?</p><span><a name='more'></a></span><h3 style="text-align: left;">What Are Absolute and Relative Hyperlinks?</h3><p>An <b>absolute hyperlink reference</b>, or what I call an exact link to the specific file location, shows the entire path to the referenced file. For example: <b>C:\Documents\User\MyExcelFile.xlsm</b></p><p>A <b>relative hyperlink</b> is a link to another file <i>relative</i> to where the current workbook is saved at. So the above example's relative link would look like this: <b>User\MyExcelFile.xlsm</b></p><p>The advantage of using a relative link is you can change the names of everything in the path and the relative links will still work (useful if you have a document with hundreds of links). In our same example, you could change the <b>C:</b> drive to <b>D</b><b>:\Documents\User\MyExcelFile.xlsm</b>or you could change the Documents folder to <b>C:\MyDocuments\</b> and all your links will still work because they're still in the same folder/subfolder relationship.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTNoXGCOgiYxMzEH1zDd0dbgPuB2iO6RuPHhMHKZJ33chH4uo7YuRi1PHUegp9mxdUmET8aMcDz3gmc5t1pF1WDxbBSdVoPwD1GHxu8YsJ7oKbbqHD6tduscxhAmnJ1bukuJLotnsXp4trQHDFhK3GDCv160arLmBM02_56wXzsTNvV47Pcai9ysfm/s647/relative%20hyperlink%20in%20excel%20spreadsheet.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="337" data-original-width="647" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTNoXGCOgiYxMzEH1zDd0dbgPuB2iO6RuPHhMHKZJ33chH4uo7YuRi1PHUegp9mxdUmET8aMcDz3gmc5t1pF1WDxbBSdVoPwD1GHxu8YsJ7oKbbqHD6tduscxhAmnJ1bukuJLotnsXp4trQHDFhK3GDCv160arLmBM02_56wXzsTNvV47Pcai9ysfm/w434-h226/relative%20hyperlink%20in%20excel%20spreadsheet.JPG" width="434" /></a></div><p>However, the problem with using all relative links is that is you copy or do a Save As on the original template containing all the links and place it in a different folder location, the relative links will no longer work. This is because they are no longer in the same relative location to the parent folder. If you copied the entire folder structure into the new location it would work but you probably don't want to do that.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9WkjN-7vOAUGCoCVMQ_lIskmbtBdv8_-CetRvgH-v_aB8OkajXQgSDbFck0MMx0PrGbKUsE5WX2SKwQReJS8rZ_fakNJDfWXMNmTO_9bIu55owCSIjrrI34wty-fXndRElor84ks7GPfF-5BN0qOsYXbgPEwp8AhstI8pBJJywsJXqJdn_P0ibGlY/s710/relative%20link%20verses%20specific%20link%20in%20excel.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="324" data-original-width="710" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9WkjN-7vOAUGCoCVMQ_lIskmbtBdv8_-CetRvgH-v_aB8OkajXQgSDbFck0MMx0PrGbKUsE5WX2SKwQReJS8rZ_fakNJDfWXMNmTO_9bIu55owCSIjrrI34wty-fXndRElor84ks7GPfF-5BN0qOsYXbgPEwp8AhstI8pBJJywsJXqJdn_P0ibGlY/w483-h221/relative%20link%20verses%20specific%20link%20in%20excel.JPG" width="483" /></a></div><br /><p><br /></p><h3 style="text-align: left;">How to Change Relative Links to Exact Links</h3><p>Instead, you could have both types of hyperlinks within your Excel file. Here's how to easily transform relative hyperlinks into absolute links.</p><p>First, I wrote a simple VBA macro to get the relative hyperlink address and put them into a helper column.</p><p><i>Sub CreateHyperLinks()</i></p><p><i>'find the last row in column B</i></p><p><i>Dim LastRow As Long</i></p><p><i>LastRow = Cells(Rows.Count, 2).End(xlUp).Row</i></p><p><i>On Error Resume Next</i></p><p><i>Dim i As Integer</i></p><p><i>For i = 3 To LastRow</i></p><p><i> Cells(i, 3) = Cells(i, 2).Hyperlinks(1).Address</i></p><p><i>Next</i></p><p><i>End Sub</i></p><p>You should only really have to run this macro once, unless the sub folder structure changes.</p><p>The next column I used the HYPERLINK function to combine the part location in the yellow squares with the address from the macro generated column. </p><p><b>=HYPERLINK(C1&C4,B4)</b></p><p>If the parent folder location changes, you just simply update the location in the yellow square and all the links will automatically be updated.</p><p>Still confused? I try to explain in this how to video:</p><p> <iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/D4qSJtob5Aw" title="YouTube video player" width="560"></iframe></p><p><br /></p><span></span><span><!--more--></span><p><br /></p><p>Does that make sense? I hope this saves you time from having to redo any hyperlinks in your Excel spreadsheets.</p><p><br /></p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-16195034440524286822022-06-12T16:19:00.008-07:002022-09-20T14:30:56.157-07:002022 NFL Helmet Schedule Spreadsheet<p> As I explained last year, I finally automated this spreadsheet so I was able to create the <b>2022 NFL Helmet Schedule</b> in less than a minute! All 32 teams, all 18 weeks of the season (now 17 games plus one bye week), all in one spreadsheet:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCEASnyjVzMwwk5lFxfC3BoXoO3tZ2t0uoUGc-MsLarM8e0six-RuxjRVwtp20vMkyT_g5P09i-54DgdCXewqTOxGDcYn_WehmiXkQX7S8MayRWuo3DYY7IXesKtSes7TBix4qVU6FbwS3HOXQz6dLhCREAsPrj8iksgVOGc6SpmTpJKF7hYkFfp7u/s1243/2022%20nfl%20helmet%20schedule%20spreadsheet%20in%20excel%20free%20football.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="530" data-original-width="1243" height="176" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCEASnyjVzMwwk5lFxfC3BoXoO3tZ2t0uoUGc-MsLarM8e0six-RuxjRVwtp20vMkyT_g5P09i-54DgdCXewqTOxGDcYn_WehmiXkQX7S8MayRWuo3DYY7IXesKtSes7TBix4qVU6FbwS3HOXQz6dLhCREAsPrj8iksgVOGc6SpmTpJKF7hYkFfp7u/w415-h176/2022%20nfl%20helmet%20schedule%20spreadsheet%20in%20excel%20free%20football.JPG" width="415" /></a></div><br /><p>A job that used to take hour by moving helmets manually is now so easy to do. Yes, that's why I love the power of macros and automation. See how fast the sheet can be created by watching this clip:</p><blockquote class="instagram-media" data-instgrm-captioned="" data-instgrm-permalink="https://www.instagram.com/reel/CeuZclnjbcd/?utm_source=ig_embed&utm_campaign=loading" data-instgrm-version="14" style="background: rgb(255, 255, 255); border-radius: 3px; border: 0px; box-shadow: rgba(0, 0, 0, 0.5) 0px 0px 1px 0px, rgba(0, 0, 0, 0.15) 0px 1px 10px 0px; margin: 1px; max-width: 540px; min-width: 326px; padding: 0px; width: calc(100% - 2px);"><div style="padding: 16px;"> <a href="https://www.instagram.com/reel/CeuZclnjbcd/?utm_source=ig_embed&utm_campaign=loading" style="background: rgb(255, 255, 255); line-height: 0; padding: 0px; text-align: center; text-decoration: none; width: 100%;" target="_blank"> <div style="align-items: center; display: flex; flex-direction: row;"> <div style="background-color: #f4f4f4; border-radius: 50%; flex-grow: 0; height: 40px; margin-right: 14px; width: 40px;"></div> <div style="display: flex; flex-direction: column; flex-grow: 1; justify-content: center;"> <div style="background-color: #f4f4f4; border-radius: 4px; flex-grow: 0; height: 14px; margin-bottom: 6px; width: 100px;"></div> <div style="background-color: #f4f4f4; border-radius: 4px; flex-grow: 0; height: 14px; width: 60px;"></div></div></div><div style="padding: 19% 0px;"></div> <div style="display: block; height: 50px; margin: 0px auto 12px; width: 50px;"><svg height="50px" version="1.1" viewbox="0 0 60 60" width="50px" xmlns:xlink="https://www.w3.org/1999/xlink" xmlns="https://www.w3.org/2000/svg"><g fill-rule="evenodd" fill="none" stroke-width="1" stroke="none"><g fill="#000000" transform="translate(-511.000000, -20.000000)"><g><path d="M556.869,30.41 C554.814,30.41 553.148,32.076 553.148,34.131 C553.148,36.186 554.814,37.852 556.869,37.852 C558.924,37.852 560.59,36.186 560.59,34.131 C560.59,32.076 558.924,30.41 556.869,30.41 M541,60.657 C535.114,60.657 530.342,55.887 530.342,50 C530.342,44.114 535.114,39.342 541,39.342 C546.887,39.342 551.658,44.114 551.658,50 C551.658,55.887 546.887,60.657 541,60.657 M541,33.886 C532.1,33.886 524.886,41.1 524.886,50 C524.886,58.899 532.1,66.113 541,66.113 C549.9,66.113 557.115,58.899 557.115,50 C557.115,41.1 549.9,33.886 541,33.886 M565.378,62.101 C565.244,65.022 564.756,66.606 564.346,67.663 C563.803,69.06 563.154,70.057 562.106,71.106 C561.058,72.155 560.06,72.803 558.662,73.347 C557.607,73.757 556.021,74.244 553.102,74.378 C549.944,74.521 548.997,74.552 541,74.552 C533.003,74.552 532.056,74.521 528.898,74.378 C525.979,74.244 524.393,73.757 523.338,73.347 C521.94,72.803 520.942,72.155 519.894,71.106 C518.846,70.057 518.197,69.06 517.654,67.663 C517.244,66.606 516.755,65.022 516.623,62.101 C516.479,58.943 516.448,57.996 516.448,50 C516.448,42.003 516.479,41.056 516.623,37.899 C516.755,34.978 517.244,33.391 517.654,32.338 C518.197,30.938 518.846,29.942 519.894,28.894 C520.942,27.846 521.94,27.196 523.338,26.654 C524.393,26.244 525.979,25.756 528.898,25.623 C532.057,25.479 533.004,25.448 541,25.448 C548.997,25.448 549.943,25.479 553.102,25.623 C556.021,25.756 557.607,26.244 558.662,26.654 C560.06,27.196 561.058,27.846 562.106,28.894 C563.154,29.942 563.803,30.938 564.346,32.338 C564.756,33.391 565.244,34.978 565.378,37.899 C565.522,41.056 565.552,42.003 565.552,50 C565.552,57.996 565.522,58.943 565.378,62.101 M570.82,37.631 C570.674,34.438 570.167,32.258 569.425,30.349 C568.659,28.377 567.633,26.702 565.965,25.035 C564.297,23.368 562.623,22.342 560.652,21.575 C558.743,20.834 556.562,20.326 553.369,20.18 C550.169,20.033 549.148,20 541,20 C532.853,20 531.831,20.033 528.631,20.18 C525.438,20.326 523.257,20.834 521.349,21.575 C519.376,22.342 517.703,23.368 516.035,25.035 C514.368,26.702 513.342,28.377 512.574,30.349 C511.834,32.258 511.326,34.438 511.181,37.631 C511.035,40.831 511,41.851 511,50 C511,58.147 511.035,59.17 511.181,62.369 C511.326,65.562 511.834,67.743 512.574,69.651 C513.342,71.625 514.368,73.296 516.035,74.965 C517.703,76.634 519.376,77.658 521.349,78.425 C523.257,79.167 525.438,79.673 528.631,79.82 C531.831,79.965 532.853,80.001 541,80.001 C549.148,80.001 550.169,79.965 553.369,79.82 C556.562,79.673 558.743,79.167 560.652,78.425 C562.623,77.658 564.297,76.634 565.965,74.965 C567.633,73.296 568.659,71.625 569.425,69.651 C570.167,67.743 570.674,65.562 570.82,62.369 C570.966,59.17 571,58.147 571,50 C571,41.851 570.966,40.831 570.82,37.631"></path></g></g></g></svg></div><div style="padding-top: 8px;"> <div style="color: #3897f0; font-family: Arial, sans-serif; font-size: 14px; font-style: normal; font-weight: 550; line-height: 18px;">View this post on Instagram</div></div><div style="padding: 12.5% 0px;"></div> <div style="align-items: center; display: flex; flex-direction: row; margin-bottom: 14px;"><div> <div style="background-color: #f4f4f4; border-radius: 50%; height: 12.5px; transform: translateX(0px) translateY(7px); width: 12.5px;"></div> <div style="background-color: #f4f4f4; flex-grow: 0; height: 12.5px; margin-left: 2px; margin-right: 14px; transform: rotate(-45deg) translateX(3px) translateY(1px); width: 12.5px;"></div> <div style="background-color: #f4f4f4; border-radius: 50%; height: 12.5px; transform: translateX(9px) translateY(-18px); width: 12.5px;"></div></div><div style="margin-left: 8px;"> <div style="background-color: #f4f4f4; border-radius: 50%; flex-grow: 0; height: 20px; width: 20px;"></div> <div style="border-bottom: 2px solid transparent; border-left: 6px solid rgb(244, 244, 244); border-top: 2px solid transparent; height: 0px; transform: translateX(16px) translateY(-4px) rotate(30deg); width: 0px;"></div></div><div style="margin-left: auto;"> <div style="border-right: 8px solid transparent; border-top: 8px solid rgb(244, 244, 244); transform: translateY(16px); width: 0px;"></div> <div style="background-color: #f4f4f4; flex-grow: 0; height: 12px; transform: translateY(-4px); width: 16px;"></div> <div style="border-left: 8px solid transparent; border-top: 8px solid rgb(244, 244, 244); height: 0px; transform: translateY(-4px) translateX(8px); width: 0px;"></div></div></div> <div style="display: flex; flex-direction: column; flex-grow: 1; justify-content: center; margin-bottom: 24px;"> <div style="background-color: #f4f4f4; border-radius: 4px; flex-grow: 0; height: 14px; margin-bottom: 6px; width: 224px;"></div> <div style="background-color: #f4f4f4; border-radius: 4px; flex-grow: 0; height: 14px; width: 144px;"></div></div></a><p style="color: #c9c8cd; font-family: Arial, sans-serif; font-size: 14px; line-height: 17px; margin-bottom: 0px; margin-top: 8px; overflow: hidden; padding: 8px 0px 7px; text-align: center; text-overflow: ellipsis; white-space: nowrap;"><a href="https://www.instagram.com/reel/CeuZclnjbcd/?utm_source=ig_embed&utm_campaign=loading" style="color: #c9c8cd; font-family: Arial, sans-serif; font-size: 14px; font-style: normal; font-weight: normal; line-height: 17px; text-decoration: none;" target="_blank">A post shared by Nick (@ntweisen)</a></p></div></blockquote> <script async="" src="//www.instagram.com/embed.js"></script><div><br /><div>On previous versions of the sheet I divided out the two conferences on separate sheets: NFC and AFC. This year, I’ve put all the teams into one sheet. However, there is a new filter option where you can filter by NFC or AFC or even by division: AFC North, AFC South, etc.</div><div><br /></div><div><a href="https://dsadsadsadsadsa.gumroad.com/l/nflhelmetschedule" target="_blank"><b>Download the 2022 NFL Helmet Schedule Spreadsheet here</b></a><br /><br /><br />Watch the video below to see how the filter works. I also so a tip in Excel how to select multiple objects at once with the mouse. And I walk through the populate helmets macro code as well. Lots of good stuff here!</div><div><br /></div><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/hWb6GeY5wo8" title="YouTube video player" width="560"></iframe><div><br /><br />As you can see, the NFL helmet schedule is printable too. You can save the spreadsheet as a PDF file or print it out and pin it up in your cubicle at work. If you do, please email or tweet me a picture of it hanging up - I'd love to see it!<br /><br />Please note, an email is <a href="https://dsadsadsadsadsa.gumroad.com/l/nflhelmetschedule" target="_blank">required to download it.</a> I do this so you will be automatically updated you if changes or additions are made and will update you when the next year’s schedule is ready. I do not use your email for anything else.<br /><br />As always, I welcome any comments or suggestions about how to fix or improve the sheet! How can I improve this football spreadsheet into something you’ll use all the time during pro-football season? What future features would you like to see?<br /><br />PS. I will not be rooting for the dumpster fire known as the Cleveland Browns this season so if any fanbases want to recruit, now's your chance to pitch me on your favorite team!<p></p></div></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com2tag:blogger.com,1999:blog-7826720847639113939.post-55536479967220445942022-04-25T19:51:00.004-07:002022-04-25T19:51:59.332-07:00The Best FREE Template to Automatically Create Folders From Excel<p>One topic I've been discussing for more than ten years, and keep coming back to with improvements, is how to automatically create folders from Excel. I originally shared a simple <a href="https://excelspreadsheetshelp.blogspot.com/2011/05/how-do-you-create-folders-with-excel.html" target="_blank">macro to make folders back in 2011</a>. More recently, I showed <a href="https://excelspreadsheetshelp.blogspot.com/2016/05/how-to-create-folders-with-hyperlink.html" target="_blank">how to add hyperlinks back to the newly created folders</a>. But I was never quite happy with these programs. So now I'm back with a better, more powerful, and easy to use template that will instantly create folders for you.</p><p>This Excel template to create folders will teach you:</p><p></p><ul style="text-align: left;"><li>How to use a macro to automatically create folders from Excel (duh)</li><li>How to change the color of a toggle button (and how to use a toggle button in Excel)</li><li>How to add hyperlinks to folders</li><li>Conditional formatting to change cell color based on combo box selection</li><li>How to copy existing folder names into Excel</li><li>How to duplicate a file structure</li></ul><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcjhcUWjEKuo6kpasOoXOQDDGxdFft1_wFfTJcr5vxyz821ZD7dOTvh7axHCkVxkEVyeEifPSmzlI_k1sIGhMPPo3-uoZqGnDURuwW5CHNRB9tOXo0rvK4vYjF09dfyBsMY2UXkWgzTCweE8Az1OIK34SPoS5_v2ZVbNO3L1c5KtBwBcz_xamYRSe_/s1600/free%20excel%20template%20to%20create%20folders%20with%20vba%20macro.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="create folders from excel file" border="0" data-original-height="631" data-original-width="1600" height="195" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcjhcUWjEKuo6kpasOoXOQDDGxdFft1_wFfTJcr5vxyz821ZD7dOTvh7axHCkVxkEVyeEifPSmzlI_k1sIGhMPPo3-uoZqGnDURuwW5CHNRB9tOXo0rvK4vYjF09dfyBsMY2UXkWgzTCweE8Az1OIK34SPoS5_v2ZVbNO3L1c5KtBwBcz_xamYRSe_/w497-h195/free%20excel%20template%20to%20create%20folders%20with%20vba%20macro.png" width="497" /></a></div><br /><p>As I've done with all my recent templates, I try to make them look as clean as possible when the user first opens the sheet so as not to overwhelm them. I like to use yellow to clearly show the cells that require user input. Command buttons make running your macros very easy.</p><p>I envision <a href="https://dsadsadsadsadsa.gumroad.com/l/foldergenerator" target="_blank">this template</a> being used in two primary ways: to create a set of new folders from scratch (their names manually typed in), or to quickly duplicate an existing file structure.</p><p>First, enter the location in the first yellow box where you want the new folders to be created. Next, type the folder names in the column. Or, if you want to duplicate existing folders, select the folders in explorer, hold down shift and right click, copy as Path. Use find and replace to remove the path. See in this example video:</p>
<iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/t_huswRvo_E" title="YouTube video player" width="560"></iframe><p>One unique feature about this template is it allows the user to choose from one of four different options about how they want the folders to be named.</p><p>You can <a href="https://dsadsadsadsadsa.gumroad.com/l/foldergenerator" target="_blank">download this Folder Generator template for free</a>, just enter a 0 here, an examine the VBA macro code yourself:</p><p>Quick VBA tip: To change the color of a toggle button once it is clicked (toggled on or toggled off), use this code:</p><p><i>Private Sub ToggleButton1_Click()</i></p><p><i> If ToggleButton1.Value = True Then ToggleButton1.BackColor = vbGreen</i></p><p><i> If ToggleButton1.Value = False Then ToggleButton1.BackColor = vbWhite</i></p><p><i>End Sub</i></p><p>Do you find this template to be useful? If so, please let me know in the comments below!</p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-29447605576250578712022-03-28T04:00:00.010-07:002022-03-28T04:00:00.184-07:00Free Meeting Scheduling Excel TemplateHow often do you ask friends, family, or coworkers which day is the best to get together? Whether it’s a meeting, party, trip, or some other occasion I find myself in these situations all the time. There are several apps and online calendars available to help you coordinate which day works for everyone involved. I used to use a site called Doodle all the time.
The problem with these services is they are not always free, they’re constantly changing and you have to keep relearning how to use them, you have to make an online account, and so on. So I did what I always do – I decided to make an Excel template to determine which day works best for meetings and events!
<br /><br />I used a lot of the same concepts and macro code from my <a href="https://excelspreadsheetshelp.blogspot.com/2022/01/2022-super-bowl-squares-spreadsheet-one.html" target="_blank">Super Bowl Squares spreadsheet</a>. When you first open the spreadsheet you are greeted by a simple, clean setup page. Here you’ll manually input the number of participants, meeting subject and description.<br />
<div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFnTPjIynXFR1pifzFYSggyU0biPysyIJ4vCixpCTDmDAuMVN8rry_4dAm_nTOeWclmZxVcN6mvIO1GJlLdy_J-lKtjYAB-xBfYF1FK6eWq2HQ0hVr_9veWpA98sYD5nBMTKH3GtIR05xhSE_Dj2G8W0lEOphemIXqJ-PL1t01_GJ8FhO-8Gm-Fa3P/s687/schedule%20a%20meeting%20in%20excel%20template.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="461" data-original-width="687" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFnTPjIynXFR1pifzFYSggyU0biPysyIJ4vCixpCTDmDAuMVN8rry_4dAm_nTOeWclmZxVcN6mvIO1GJlLdy_J-lKtjYAB-xBfYF1FK6eWq2HQ0hVr_9veWpA98sYD5nBMTKH3GtIR05xhSE_Dj2G8W0lEOphemIXqJ-PL1t01_GJ8FhO-8Gm-Fa3P/w462-h310/schedule%20a%20meeting%20in%20excel%20template.PNG" width="462" /></a></div>
Next, you’ll enter the start and end dates of the days you want the participants to choose from. It’s very important that these two cells ONLY contain dates. <a href="https://www.youtube.com/watch?v=o_pddqnnh38" target="_blank">To restrict a user to only being able to enter a date in a cell</a>, go to Data > Data Validation. Under Allow select Date. Be sure to enter a custom error message so if a user makes a mistake they understand what needs to be entered.<br /><br />When the user clicks Generate Schedule the Schedule sheet is unhidden. The schedule can handle up to 100 participants and up to 365 days. However, you probably won’t need all that so the macro will automatically hide all the rows and columns not needed to make it easy for the user to input their information.
<div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaylz3SNp6exsDOxWvgUG1vDIl5UUxLEKOEGduGI4aJjOWYIpclh3JfpAFmtbGCjTagZAggxBFSvi9RiHXQNK6Fs_5KJnbenJBXAHVualGj5Qiy5HnOHs4pOAxZ082zezcRobNIE8Mm9YXwdcIyEyZ9mirmTKeL724B2Bk4pEwVMf3o1t0Hw5tq6Hw/s766/meeting%20scheduling%20excel%20spreadsheet%20doodle.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="214" data-original-width="766" height="89" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaylz3SNp6exsDOxWvgUG1vDIl5UUxLEKOEGduGI4aJjOWYIpclh3JfpAFmtbGCjTagZAggxBFSvi9RiHXQNK6Fs_5KJnbenJBXAHVualGj5Qiy5HnOHs4pOAxZ082zezcRobNIE8Mm9YXwdcIyEyZ9mirmTKeL724B2Bk4pEwVMf3o1t0Hw5tq6Hw/s320/meeting%20scheduling%20excel%20spreadsheet%20doodle.PNG" width="320" /></a></div></div><br />A lot of good Excel tips can be gleaned from examining the event timing spreadsheet. Here's a quick summary of what can be learned by dissecting this free Excel template:<br /><ul style="text-align: left;"><li>How to add and use Option buttons</li><li>How to use data validation to restrict entry in a cell to a date</li><li>How to use data validation to restrict entry in a cell to an email address</li><li>How to get the day of the week from a date</li><li>How to use command buttons and assign specific macros to them</li><li>How to send emails from Excel with hyperlinks</li><li>How to use a formula to show only weekends in Excel </li><li>How to use conditional formatting to change cell color based on cell value</li><li>How to hide command buttons by macro</li></ul><a href="https://dsadsadsadsadsa.gumroad.com/l/meetingsinexcel" target="_blank"><b>Download the Meeting Scheduler Template here.</b></a><div><br /></div><div><h3 style="text-align: left;">Watch How to Coordinate Meetings with Excel</h3><div>If you want to see how this spreadsheet works and some tips like how to limit a cell where a user can only input a date then watch the video below:</div><div><br /><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/xK3o5pGu1n4" title="YouTube video player" width="560"></iframe></div><div><br /></div><div><a href="https://dsadsadsadsadsa.gumroad.com/l/meetingsinexcel" target="_blank">Try it out</a> and let me know if you think it’s a legitimate replacement for Doodle, Calendly or whatever meeting scheduling apps you currently use. </div></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-49285325437555699482022-02-14T18:13:00.005-08:002022-02-14T18:13:25.716-08:00Weighted Olympic Medal Count 2022<p><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;">In honor of the <b>2022 Winter Olympic Games</b>
currently being held<span style="mso-spacerun: yes;"> </span>Beijing, China, I
decided to create a Microsoft Excel spreadsheet template for the medal count as
I did for the <a href="https://excelspreadsheetshelp.blogspot.com/2021/07/weighted-olympic-medal-count-2021.html" target="_blank">2020 (2021) Summer Olympic Games</a>, 2018 Winter Olympics, </span><span face=""Trebuchet MS",sans-serif" style="color: #3e3e3e; font-size: 10pt;"><a href="http://excelspreadsheetshelp.blogspot.com/2016/08/weighted-olympic-medal-count-2016.html"><span style="background: white;">2016 Summer Olympic Games</span></a><span style="background: white;">, </span><a href="http://excelspreadsheetshelp.blogspot.com/2014/02/weighted-winter-olympics-medal-count.html"><span style="background: white;">2014 Winter Olympics</span></a><span style="background: white;"> and</span><a href="http://excelspreadsheetshelp.blogspot.com/2012/08/weighted-olympic-medal-count-2012.html"><span style="background: white;"> 2012 Summer Olympics</span></a><span style="background: white;">. </span></span></p><p><span face=""Trebuchet MS",sans-serif" style="color: #3e3e3e; font-size: 10pt;"><span style="background: white;">There are two primary methods most websites appear
to be ranking the 2022 medal count. Most sites rank countries by the total
number of Olympic medals won. Other sites, like the International Olympic
Committee (or IOC) rank countries by their gold medal count. And others rank by
other factors like per capita or GDP.</span></span></p>
<p style="background: white; margin: 0in;"><span face=""Trebuchet MS",sans-serif" style="color: #3e3e3e; font-size: 10pt;">Pictured below is a bar
chart showing all medals won for the top countries (as of the time of this
posting on 2-14-22). The bar chart is created in Excel by highlighting the data
then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars
by right clicking on them then use the drop down menu to select the data you
want to change.</span></p><p style="background: white; margin: 0in;"><br /></p><p style="background: white; margin: 0in;"><span face=""Trebuchet MS",sans-serif" style="color: #3e3e3e; font-size: 10pt;"><a href="https://gum.co/medalcountsheet">You can update the
chart yourself by download the Excel file here.</a></span></p><p style="background: white; margin: 0in;"><span style="color: #3e3e3e;"><span style="font-size: 13.3333px;"><br /></span></span><span face=""Trebuchet MS",sans-serif" style="color: #3e3e3e; font-size: 10pt;"><o:p></o:p><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjvR9FQl-PlxOQ3uoGFlWwg84sXSz6XLVMqsMXXTm17aSlrKoiBsrQZc411vjQ_eurlVCSx9XExMYlzQSEWaQLduBi-dl-pUd8RfjircltKqb81Wn1wrFFzkb9w4h-TLkgwpfJO86UEriV--_hqZGx8m2B80MWoLAybJ1QEe0-Odl7JmCnIzc0omoWb=s1106" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="638" data-original-width="1106" height="286" src="https://blogger.googleusercontent.com/img/a/AVvXsEjvR9FQl-PlxOQ3uoGFlWwg84sXSz6XLVMqsMXXTm17aSlrKoiBsrQZc411vjQ_eurlVCSx9XExMYlzQSEWaQLduBi-dl-pUd8RfjircltKqb81Wn1wrFFzkb9w4h-TLkgwpfJO86UEriV--_hqZGx8m2B80MWoLAybJ1QEe0-Odl7JmCnIzc0omoWb=w495-h286" width="495" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">As of 2/14/22</td></tr></tbody></table></span></p>
<p style="background: white; margin: 0in;"><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;"><o:p> </o:p></span></p>
<p style="background: white; margin: 0in;"><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;">I’ve
devised my own ranking system to give each Olympic medal a weight where the
silver is worth half a gold medal and a bronze is worth only a quarter of the
gold. Based on this new scoring system, previous Olympic results suddenly
became quite interesting. However, for the 2020 Summer Games not too much
actually changes (so far, will revisit after more events are completed).<o:p></o:p></span></p><p style="background: white; margin: 0in;"><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;"><br /></span></p><p style="background: white; margin: 0in;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgpAFV-HpGenGcHa7jNHmjQpphhURSHlTH7K-nUmtVBxBx6lDBFz4BVu3QMMFmO3yu088vtJNSzfwBDCc21lmMlEr-0L8CA21_p1ty3_jep51wSbZpcdzYZxBSGsQvpXW3gx9vpUBsK9BCxVtbatEVaKm7Pp6QtVBnfK02aWUPmNNEMs1UvekiX33DU=s956" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="557" data-original-width="956" height="289" src="https://blogger.googleusercontent.com/img/a/AVvXsEgpAFV-HpGenGcHa7jNHmjQpphhURSHlTH7K-nUmtVBxBx6lDBFz4BVu3QMMFmO3yu088vtJNSzfwBDCc21lmMlEr-0L8CA21_p1ty3_jep51wSbZpcdzYZxBSGsQvpXW3gx9vpUBsK9BCxVtbatEVaKm7Pp6QtVBnfK02aWUPmNNEMs1UvekiX33DU=w496-h289" width="496" /></a></div><br /><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;"><br /></span><p></p>
<p style="background: white; margin: 0in;"><span style="color: #3e3e3e; font-size: 10pt;">If you’ve
been reading my blog for awhile, you’ll probably know </span><a href="https://excelspreadsheetshelp.blogspot.com/2016/07/how-to-write-book-fast-by-using-excel.html" style="font-size: 10pt;" target="_blank">I’m a roller coaster enthusiast</a><span style="color: #3e3e3e; font-size: 10pt;">, so you probably won’t be surprised that the Olympic event that
intrigues me the most is the bobsled and luge. As they said on the broadcast of
the monobob following the Super Bowl, “this track more than any other is
similar to a roller coaster” as it has 4 uphill sections and a helix. It’s over
4,000 feet long, has a total elevation difference of 117 meters (383 feet), and
bobsleders were hitting 75 miles per hour while sustaining 4gs up to 7 seconds
at a time. That’s intense! A ride on a bobsled would scare me more than any
roller coaster that’s secured to the rails, as you could legit crash! What
Winter Olympic sport do you like to follow?</span></p><p style="background: white; margin: 0in;"><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;"><br /></span></p><p style="background: white; margin: 0in;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjGw_oYX0_EhTnso7BLYKY6ABbqBiFUv3d9Yn__fziJnmNS3WcokWYHmIIaN2cslYrCJmRnyG0E_jfnINxDfNtw3SMm3rOgn24937PdL8Gh_BQ2UuOmbQa5CzKrp1IIqp4FvAsx4rA3etZSBc_89kecJEzBtoIkpyC53IoAS6Yj4QNRJVGlJnLeSvKq=s4032" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="2268" data-original-width="4032" height="211" src="https://blogger.googleusercontent.com/img/a/AVvXsEjGw_oYX0_EhTnso7BLYKY6ABbqBiFUv3d9Yn__fziJnmNS3WcokWYHmIIaN2cslYrCJmRnyG0E_jfnINxDfNtw3SMm3rOgn24937PdL8Gh_BQ2UuOmbQa5CzKrp1IIqp4FvAsx4rA3etZSBc_89kecJEzBtoIkpyC53IoAS6Yj4QNRJVGlJnLeSvKq=w375-h211" width="375" /></a></div><br /><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;"><br /></span><p></p><div style="border-bottom: double windowtext 2.25pt; border: none; mso-element: para-border-div; padding: 0in 0in 1pt;"><p class="MsoNormal" style="border: none; mso-border-bottom-alt: double windowtext 2.25pt; mso-padding-alt: 0in 0in 1.0pt 0in; padding: 0in;"><b><a href="https://gum.co/medalcountsheet"><span face=""Trebuchet MS",sans-serif" style="background: white; font-size: 10pt;">Download the
spreadsheet and see for yourself.</span></a><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;"> </span></b></p><p class="MsoNormal" style="border: none; mso-border-bottom-alt: double windowtext 2.25pt; mso-padding-alt: 0in 0in 1.0pt 0in; padding: 0in;"><span face=""Trebuchet MS",sans-serif" style="background: white; color: #3e3e3e; font-size: 10pt;">I’ve
shared my Olympic Medal Count spreadsheet and listed out the Olympic medals by
country. How would you weight each medal against the others? Comment below and
share any of your Olympic medal rating systems!</span>
</p></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com2tag:blogger.com,1999:blog-7826720847639113939.post-20809991737617609992022-01-30T18:50:00.008-08:002022-02-01T20:01:49.147-08:002022 Super Bowl Squares Spreadsheet - one size fits allI’ve been creating Super Bowl Squares spreadsheets for over ten years now but this is just the second year of using <a href="https://gumroad.com/l/superbowlsquares" target="_blank">my new and improved one-size fits all generator sheet.</a> When you first open my free grid game template, you’re met with a simple setup screen where the user will decide how to play the game:<ul style="text-align: left;"><li>How do you want to assign names to the boxes: manually pick and choose or automatic randomized?</li><li>Do you want to use the same numbers of the Super Bowl score, rotate by quarter or by each half?</li><li>Do you want to earn points per quarter, per minute, or every time the score changes?</li></ul>No matter what you choose, after you click the “Generate Squares” button the sheet automatically updates the Super Bowl boxes and the leaderboard to reflect only the version of the game you want to play. <b>There 54 different ways to play, all contained within this one template. </b><div><b><br /></b><div><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgX_ETEtheQGZFa7iDXWM3NFcdGhlT2u91gdLOkNjoJWwXQQx-8kjerYm8OoNOHiunhUkEZY-m2WPFUdezmjC7MsbPT2hg-8_FJeE0eh5FeIaRl9mW5Fi3fFj7TvNHabB7Sg2mdLV9SyukJ0dk859ZhOM2S_cijFwIOhtw7hV0mhymOQNtKGuk-cIY_=s1109" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="2022 super bowl squares template in excel" border="0" data-original-height="613" data-original-width="1109" height="282" src="https://blogger.googleusercontent.com/img/a/AVvXsEgX_ETEtheQGZFa7iDXWM3NFcdGhlT2u91gdLOkNjoJWwXQQx-8kjerYm8OoNOHiunhUkEZY-m2WPFUdezmjC7MsbPT2hg-8_FJeE0eh5FeIaRl9mW5Fi3fFj7TvNHabB7Sg2mdLV9SyukJ0dk859ZhOM2S_cijFwIOhtw7hV0mhymOQNtKGuk-cIY_=w510-h282" width="510" /></a><br /><br />After the Super Bowl, you can keep using this sheet for regular season NFL games. Simply use the drop down lists in the Squares sheet to select the team from the list (broken up by AFC and NFC) and their helmets will update automatically! Or make a few modifications and use this template for other sports. The possibilities are endless!<br /><br />Watch me demo the new Super Bowl board in the video below. Also be sure to subscribe to my email list and YouTube channel as I'll be showing off all the tricks and tips I used to make this spreadsheet work. <br /><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/52a4o5SDA8E" title="YouTube video player" width="560"></iframe><br /><br />As you can see, the new sheet allows more ways to play than a fixed style but is simple and easy to setup and is automated as much as possible.<br /><br /><a href="https://gumroad.com/l/superbowlsquares" target="_blank"><b>Get the Super Bowl pool template here.</b></a><br /><br />Even if you’re not into football, you can still use the template to learn how to do all of these cool Excel tricks:<br /><ul style="text-align: left;"><li>Lookup pictures based on cell values</li><li>Generate random numbers</li><li>Use the NameManager</li><li>Create drop down lists</li><li>Use index and match formulas</li></ul>If you dissect the macros in the spreadsheet you’ll learn how to:<br /><ul style="text-align: left;"><li>Hide rows and columns</li><li>Hide or unhide sheets</li><li>Generate random numbers between 0 and 9 with no duplicates</li><li>How to hide command buttons by VBA</li><li>How to change cell fill color</li><li>How to send an email from Excel</li><li>How to attach an image to an email using VBA</li></ul><h3 style="text-align: left;">New Features for 2022</h3>A new feature I added for 2022 is the ability for Outlook users to easily share the Super Bowl squares via email. Enter all the player’s emails in Column A on the Send Scoreboard sheet. Just click the button and an email will automatically be composed from Excel complete with an image of the grid. I explained how to insert images into emails from a spreadsheet in a previous article.</div><div><br /></div><iframe width="560" height="315" src="https://www.youtube.com/embed/Au32PwGmyV0" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe><div><br /><br />Another way to share, copy and paste the board into a Google doc. This sheet won’t work in Google due to the macros but once you’ve got the grid filled in and the score numbers have been randomly generated, you could share it with your colleagues by copying and pasting as an image into a Google sheet. The manager would still keep score in the template but that way everyone can see what numbers they have. Let me know if you use this feature or if it isn't useful for you.<br /><br />Get the Super Bowl squares spreadsheet here (enter a 0 into the price box then input an email address):<div><br /><a href="https://gumroad.com/l/superbowlsquares"><b>https://gumroad.com/l/superbowlsquares</b></a><br /><br />*Intended for PC/Microsoft Office/Excel. I don't think it will work in Mac Numbers or Google sheets as macros are not supported*<br /><br />Let me know what you think. I’d love to hear from you. Is this version as much of an upgrade and easy to use as I think it is? I welcome any and all questions, comments, suggestions, cuss words, and compliments. Let me know using the comments below or via email. Enjoy playing Super Bowl Squares! <br /><br /><div style="border-bottom: double windowtext 2.25pt; border: none; mso-element: para-border-div; padding: 0in 0in 1pt;"><br />
</div></div><div class="separator" style="clear: both; text-align: center;"><br /></div><br /><div class="separator" style="clear: both; text-align: center;"><br /></div><br /></div></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com5tag:blogger.com,1999:blog-7826720847639113939.post-44254507503521910962022-01-06T17:56:00.004-08:002022-01-06T19:00:50.233-08:00How to Add an Excel Shape to Outlook Mail by VBA<p>Two spreadsheets I am most proud of are my <a href="https://excelspreadsheetshelp.blogspot.com/2021/01/2021-super-bowl-squares-spreadsheet.html" target="_blank">Super Bowl SquaresGame Generator</a> and my <a href="https://excelspreadsheetshelp.blogspot.com/2021/12/2021-college-football-bowl-prediction.html" target="_blank">College Football Bowl Prediction Pool Manager</a> (Bowl
Pick’em Game). I’ve put a lot of time and features into these free templates.
However, I still get many requests to add even more features. One of the most
asked questions is “how can I share the leaderboard results when all the
players aren’t connected to the same network?” I would normally respond with
how I do it: “I take a screenshot of the scoreboard and manually email it to
the players.” Then it dawned on me – why not automate this process to make it
easy for everyone to use? Why not automatically add a picture of the Excel
sheet into an email?</p>
<p class="MsoNormal">There are two methods I can think of off the top of my head
for attaching an image into an email with a macro, and here are the pros and
cons of each:</p>
<ol start="1" style="margin-top: 0in;" type="1">
<li class="MsoListParagraph" style="margin-left: 0in; mso-list: l0 level1 lfo1;">The
picture is saved on your PC (or needs to be saved by the macro before
inserting into the email)<span style="mso-spacerun: yes;"> </span>– but
either way you have to know the location of the file.<o:p></o:p></li>
<li class="MsoListParagraph" style="margin-left: 0in; mso-list: l0 level1 lfo1;">Copy
and paste an image already in your spreadsheet into an email. Does not
require saving the image. But you must know the shape name so the macro
can find it.</li></ol>
<p class="MsoNormal">For today’s tutorial, I am going to show you how to use the
#2 method. </p><p class="MsoNormal">A thread on method #1 can be found here: <a href="https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba">https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba</a></p>
<p class="MsoNormal">You can read along or scroll down to watch the video below.
Again, for this method to insert an image from a spreadsheet into an email, the
image must already be created and named manually so the macro knows what image
within the sheet to use.</p><h3 style="text-align: left;">Name the Shape or Image You Want to Copy and Paste From Excel into Email</h3>
<p class="MsoNormal">First, I need to have a linked image in my spreadsheet that
will be copied to the email. Highlight the area (the cells) that you want to
have an image of, in my example the scoreboard of my Super Bowl Squares sheet.
Next, I created a new sheet within my workbook where I will collect the emails
of all the players. I right click, paste special, linked picture. Select the
image. Under page layout go to Selection Pane. Rename the picture “Preview1” or
some other descriptive name. This is what the macro will use to identify which
picture to attach to the email.</p>
<p class="MsoNormal">I also want to allow the user the option to include a
hyperlink to the Excel workbook in the email or not. To do this, I create a
checkbox in Excel by going to the developer tab, insert, ActiveX controls,
Check Box.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi1h1YhwjMS0eo4d7X6Yvv5oQc06kJ0GHExl5tbCVCdJGg3jHKA2T6EgirjULlZ9kV8qQhV4LnTrDTDL9OPibfHkSL2jTahIpZTaJYvMqBiDe9cmT2UQ-lKQ37ZLLYEDugQVB7TIX8sbhCGbqubWmXe90xQxxOOEJfQt_ABBXYKAwM48YdFu_68a9ke=s1916" style="margin-left: 1em; margin-right: 1em;"><img alt="How to Add an Excel Shape to Outlook Mail by VBA" border="0" data-original-height="1006" data-original-width="1916" height="317" src="https://blogger.googleusercontent.com/img/a/AVvXsEi1h1YhwjMS0eo4d7X6Yvv5oQc06kJ0GHExl5tbCVCdJGg3jHKA2T6EgirjULlZ9kV8qQhV4LnTrDTDL9OPibfHkSL2jTahIpZTaJYvMqBiDe9cmT2UQ-lKQ37ZLLYEDugQVB7TIX8sbhCGbqubWmXe90xQxxOOEJfQt_ABBXYKAwM48YdFu_68a9ke=w606-h317" width="606" /></a></div><br /><p></p>
<h3 style="text-align: left;">Attach Image to Email Excel Macro Code</h3><p class="MsoNormal">Now it’s time to write the VBA macro that will automatically
send an email to all the players with a picture of the latest scoreboard – all
at the click of a button!</p>
<p class="MsoNormal">I’ve previously shared how to send an email from an Excel
sheet but this is my first time attaching an image. Below is the full code with
my comments explaining what is happening along the way.</p>
<p class="MsoNormal"><i>Sub SendEmailUpdate()</i></p>
<p class="MsoNormal"><i>'Optimize Macro Speed<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.ScreenUpdating = False<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.EnableEvents = False<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.Calculation = xlCalculationManual<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>'define the workbook, location, and name<o:p></o:p></i></p>
<p class="MsoNormal"><i>Dim Wb1 As Workbook<o:p></o:p></i></p>
<p class="MsoNormal"><i>Set Wb1 = ThisWorkbook<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>Dim OwnerName As String<o:p></o:p></i></p>
<p class="MsoNormal"><i>OwnerName = Application.UserName<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>Dim FileLoc As String<o:p></o:p></i></p>
<p class="MsoNormal"><i>FileLoc = Wb1.FullName<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>Dim WorkbookName As String<o:p></o:p></i></p>
<p class="MsoNormal"><i>WorkbookName = Wb1.Name<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>'SendEmailTo will count the number of people who the email
will be sent to<o:p></o:p></i></p>
<p class="MsoNormal"><i>Dim SendEmailTo As Integer<o:p></o:p></i></p>
<p class="MsoNormal"><i>SendEmailTo = 0<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>'we will store all the email addresses in one long string
then insert them into the TO line of the email later<o:p></o:p></i></p>
<p class="MsoNormal"><i>Dim ToPerson As String<o:p></o:p></i></p>
<p class="MsoNormal"><i>ToPerson = ""<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>'loop through all players in column A of the Send Scoreboard
sheet (up to 100 players max)<o:p></o:p></i></p>
<p class="MsoNormal"><i>Dim x As Integer<o:p></o:p></i></p>
<p class="MsoNormal"><i>For x = 2 To 101<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>' get the emails
to fill in the TO line<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>If Not
IsEmpty(Wb1.Worksheets("Send Scoreboard").Range("A" &
x).Value) Then<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>ToPerson =
Wb1.Worksheets("Send Scoreboard").Range("A" & x) &
"; " & ToPerson<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>SendEmailTo =
SendEmailTo + 1<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Else<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'MsgBox
"email is blank"<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'NoSEnd = NoSEnd +
1<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>End If<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>' get the emails
to fill in the CC line<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'If Not
IsEmpty(WB3.Worksheets(1).Range(CCCol & PICRow).Value) Then<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'CCPerson =
WB3.Worksheets(1).Range("D" & PICRow) & "; " &
CCPerson<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'CCEmail = CCEmail
+ 1<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'Else<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'End If<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>Next<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>MsgBox "Email will be sent to " & SendEmailTo
& " recipients."</i><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi8rYEGUPYhhUwWnsWpFzq5cS6tFWtZaREDkD6oj0_jX2hwEEsqjxChiwdkg_alRjWL1fOUvdp5GPv7q3Q1m0X2-PFq3aIWaj4y-dQ5ZMOaFnNBAuqoRoLSBRGS0PaXNl-G4Hjimui2qEs0v7YDwSSGJM43vBvf36x9VkEqTDwlIHeY01NWaJvQqQ5E=s238" style="margin-left: 1em; margin-right: 1em;"><img alt="how to send email from excel" border="0" data-original-height="159" data-original-width="238" src="https://blogger.googleusercontent.com/img/a/AVvXsEi8rYEGUPYhhUwWnsWpFzq5cS6tFWtZaREDkD6oj0_jX2hwEEsqjxChiwdkg_alRjWL1fOUvdp5GPv7q3Q1m0X2-PFq3aIWaj4y-dQ5ZMOaFnNBAuqoRoLSBRGS0PaXNl-G4Hjimui2qEs0v7YDwSSGJM43vBvf36x9VkEqTDwlIHeY01NWaJvQqQ5E=s16000" /></a></div><br /><p></p>
<p class="MsoNormal"><i>'get the named Image to attach to the email and copy it<o:p></o:p></i></p>
<p class="MsoNormal"><i>Set oPreview = Wb1.Worksheets("Send
Scoreboard").Shapes("Preview1")<o:p></o:p></i></p>
<p class="MsoNormal"><i>oPreview.CopyPicture ' oPreview is now in Clipboard<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>'launch Outlook<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim xOutApp As
Object<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim xOutMail As
Object<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim xMailBody As
String<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>On Error Resume
Next<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set xOutApp =
CreateObject("Outlook.Application")<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set xOutMail =
xOutApp.CreateItem(0)<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i>'for html email<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i>If Wb1.Worksheets("Send Scoreboard").CheckBox1.Value
= True Then<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'include the link
to the spreadsheet<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>xMailBody =
"Hello everyone! <br><br>" & "The SuperBowl
Squares scoreboard has been updated. You can access the sheet by clicking the
link below. <br><br>" & _<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>"Link:
<br><br>" & "<a href=" & Chr(34) &
FileLoc & Chr(34) & " > " & WorkbookName & "
</a> " & "<br><br>" & _<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>"Thanks for
playing," & "<br><br>" & OwnerName<o:p></o:p></i></p>
<p class="MsoNormal"><i>Else<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'false, no link<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>xMailBody =
"Hello everyone! <br><br>" & "The SuperBowl Squares
scoreboard has been updated. Please see the below image:
<br><br>" & _<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>"Thanks for
playing," & "<br><br>" & OwnerName<o:p></o:p></i></p>
<p class="MsoNormal"><i>End If<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>On Error Resume
Next<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>With xOutMail<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>.To = ToPerson<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'.CC =
CCPerson<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>.BCC =
""<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>.Subject = WorkbookName<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'.Body =
xMailBody<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>.HTMLBody =
xMailBody<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>.Display<span style="mso-spacerun: yes;"> </span>'or use .Send<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i>Quick Note: </o:p><span face=""Segoe UI", sans-serif" style="background: white; color: #232629; font-size: 11.5pt;">To use the clipboard to copy and paste the
picture into email, you need an Outlook mail editor which can deal with the
clipboard. Here I use WordEditor for example. The </span><a href="http://msdn.microsoft.com/en-us/library/office/ff868196(v=office.15).aspx"><span face=""Segoe UI",sans-serif" style="background: white; border: 1pt none windowtext; font-size: 11.5pt; mso-border-alt: none windowtext 0in; padding: 0in;">WordEditor</span></a><span face=""Segoe UI", sans-serif" style="background: white; color: #232629; font-size: 11.5pt;"> property of the Inspector class returns an instance of
the Document class from the Word object model which represents the Body of your
email: <a href="https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/dd492012(v=office.12)?redirectedfrom=MSD">https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/dd492012(v=office.12)?redirectedfrom=MSD</a></span></p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> </span><o:p></o:p></p>
<p class="MsoNormal"> <i> </i><i>Set oInspector
= .GetInspector<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set oWdDoc =
oInspector.WordEditor<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set oWdContent
= oWdDoc.Content<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set oWdRng =
oWdDoc.Paragraphs(1).Range<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>'oWdRng.InsertBefore "This is a test"<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>oWdRng.InsertParagraphAfter<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>oWdRng.InsertParagraphAfter<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set oWdRng =
oWdDoc.Paragraphs(3).Range<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>oWdRng.Paste '
paste from oPreview Clipboard<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>olFormatHTML =
2<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>.BodyFormat =
olFormatHTML ' change to HTML<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>End With<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>On Error GoTo 0<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set xOutMail =
Nothing<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set xOutApp =
Nothing</i></p>
<p class="MsoNormal"><i>'---------------------------------------------------------------</i></p>
<p class="MsoNormal"><i>ResetSettings:<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'Reset Macro
Optimization Settings<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.EnableEvents = True<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.Calculation = xlCalculationAutomatic<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.ScreenUpdating = True</i></p>
<p class="MsoNormal"><i>End Sub</i><o:p></o:p></p>
<p class="MsoNormal"><span></span></p><a name='more'></a>See the attach image to email macro in action:<br /><p></p>
<iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/das-x-1A3bo" title="YouTube video player" width="560"></iframe>
<p class="MsoNormal"><o:p> <span></span></o:p></p><!--more--><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;">Here's what the email created from the Excel macro looks like:</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgPI-JqRANgzbS_vCIjjKjMVP0cd1gi_aBG-mR0saB2DB657FGizkeOf9l9hc4grpaR0PMcMpoD5Q05HuIgGzHuIjSj-NLpaTDAv65jYCyy7Iih6N3AL87Xs6c7MkyEkuFyo8ySMai9o7pl_QwEx1_2nw5OvVs-v2GI1m8mlRBxBDpdCnuiiaR4UwPn=s1321" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="731" data-original-width="1321" height="177" src="https://blogger.googleusercontent.com/img/a/AVvXsEgPI-JqRANgzbS_vCIjjKjMVP0cd1gi_aBG-mR0saB2DB657FGizkeOf9l9hc4grpaR0PMcMpoD5Q05HuIgGzHuIjSj-NLpaTDAv65jYCyy7Iih6N3AL87Xs6c7MkyEkuFyo8ySMai9o7pl_QwEx1_2nw5OvVs-v2GI1m8mlRBxBDpdCnuiiaR4UwPn=s320" width="320" /></a></div><br /><p></p>
<p class="MsoNormal">Now, when the user clicks a button on the sheet, it will
draft an Outlook email to all recipients listed in range A2:A101 inserted in
the TO line.<span style="mso-spacerun: yes;"> </span>The body of the email will
be slightly different depending on if the user clicked the checkbox if they
want to include the link to the sheet or not. The preview image will
automatically be copied and pasted into the email by the VBA macro.</p>
<p class="MsoNormal">Hope that helps! If you have any questions, please feel free
to ask using the comment form below.<o:p></o:p></p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com1tag:blogger.com,1999:blog-7826720847639113939.post-31637479615305601182021-12-06T20:01:00.013-08:002021-12-07T18:20:55.253-08:002021 College Football Bowl Prediction Pool<p><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; white-space: pre-wrap;">The college football conference championships were played this past weekend which means the 2021 NCAA college football bowl season is here again! Therefore, it’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season (<a href="https://excelspreadsheetshelp.blogspot.com/2021/11/gift-guide-for-excel-users-2021.html" target="_blank">other than giving and receiving gifts</a>) is being able to talk trash to your relatives about their terrible bowl picks. This year has the added bonus of not just single bowl games but the eighth year of a four team playoff to determine the national champion.</span></p><p><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; white-space: pre-wrap;"><br /></span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-uugrZ7HeR-w/Ya7bzYf08PI/AAAAAAAAIIQ/bKTFjhzVVmAqClpznAEjTyKSh6fA-Gf5ACNcBGAsYHQ/s1875/2021%2Bcollege%2Bfootball%2Bbowl%2Bpick%2Bem%2Bpool%2Bspreadsheet%2Btemplate.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="799" data-original-width="1875" height="172" src="https://1.bp.blogspot.com/-uugrZ7HeR-w/Ya7bzYf08PI/AAAAAAAAIIQ/bKTFjhzVVmAqClpznAEjTyKSh6fA-Gf5ACNcBGAsYHQ/w404-h172/2021%2Bcollege%2Bfootball%2Bbowl%2Bpick%2Bem%2Bpool%2Bspreadsheet%2Btemplate.JPG" width="404" /></a></div><br /><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; white-space: pre-wrap;"><br /></span><p></p><span id="docs-internal-guid-a694ee54-7fff-18de-5f6a-801183e99c98"><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Features for this year's bowl prediction pool over the previous college football bowl pool manager spreadsheets include the following:</span></p><ul style="margin-bottom: 0px; margin-top: 0px; padding-inline-start: 48px;"><li aria-level="1" dir="ltr" style="color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 12pt;"><span style="background-color: white; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Easy method to make each bowl game worth a different point value, so the national championship game and semi-finals can be worth more points, or however you want to customize it.</span></p></li><li aria-level="1" dir="ltr" style="color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="background-color: white; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Updated leaderboard tab with new stats</span></p></li><li aria-level="1" dir="ltr" style="color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="background-color: white; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Separate entry sheet to pass out to participants or co-workers that can be imported automatically by a built-in macro</span></p></li><li aria-level="1" dir="ltr" style="color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="background-color: white; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Complete NCAA college football bowl schedule with game times and TV stations</span></p></li><li aria-level="1" dir="ltr" style="color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; list-style-type: disc; vertical-align: baseline; white-space: pre;"><p dir="ltr" role="presentation" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 0pt;"><span style="background-color: white; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">New stat sheet to track each conference's record during bowl season. Graph shows total conference teams and total conference wins</span></p></li></ul><div><span style="color: #3e3e3e; font-family: Trebuchet MS;"><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-FYpzcQqLJug/Ya7cWmdLQxI/AAAAAAAAIIY/HBDiDoSIeSoODlYwnS2FO79UTzbbnnadACNcBGAsYHQ/s913/2021%2Bbowl%2Bgame%2Bconference%2Bwins.JPG" style="margin-left: 1em; margin-right: 1em;"><img alt="bowl pick em excel sheet download" border="0" data-original-height="331" data-original-width="913" height="116" src="https://1.bp.blogspot.com/-FYpzcQqLJug/Ya7cWmdLQxI/AAAAAAAAIIY/HBDiDoSIeSoODlYwnS2FO79UTzbbnnadACNcBGAsYHQ/w320-h116/2021%2Bbowl%2Bgame%2Bconference%2Bwins.JPG" width="320" /></a></div><br /><span style="font-size: 13.3333px; white-space: pre-wrap;"><br /></span></span></div><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">The bowl prediction sheets include the football helmet designs for every team (taken from the 2017 college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><b><a href="https://dsadsadsadsadsa.gumroad.com/l/cfpbowlsheet" target="_blank">Download the CFP Pool Manager and Single Entry Form here</a></b></span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">I'm working on a new version where you could do confidence points that you can test out now and give me feedback.</span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/q3dztEOa0bw" title="YouTube video player" width="560"></iframe></span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><br /></span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><br /></span></p><p dir="ltr" style="background-color: white; line-height: 1.38; margin-bottom: 0pt; margin-top: 10pt; padding: 0pt 0pt 10pt; text-align: center;"><a href="https://gumroad.com/l/cfpbowlsheet" style="text-decoration: none;"><span style="background-color: white; color: red; font-family: 'Trebuchet MS'; font-size: 10pt; font-style: normal; font-variant: normal; font-weight: 700; text-decoration: none; vertical-align: baseline; white-space: pre;">Download the 2021 CFP Bowl Prediction Pool Manager.xlsm file here</span></a></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><span id="docs-internal-guid-25556856-7fff-1abd-e851-40ca3b982da4"></span></span></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 12pt; margin-top: 12pt;"><span style="background-color: white; color: #3e3e3e; font-family: 'Trebuchet MS'; font-size: 10pt; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; vertical-align: baseline; white-space: pre;">Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement. I love that people are using this Bowl Prediction Game to help raise money for charity, that's so awesome to hear! What team are you rooting for?</span></p><div><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"><br /></span></div></span><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com3tag:blogger.com,1999:blog-7826720847639113939.post-53024084029198872682021-11-18T18:28:00.000-08:002021-11-18T18:28:25.690-08:00Gift Guide for Excel Users 2021<p> The 2021 holiday season is officially upon us here in the United States which means it’s time for my annual gift giving guide. I used to panic every year whenever my spouse, parents, and siblings asked me what I wanted for Christmas. I needed to give them an idea otherwise I’d end up with an ugly sweater or some random gadget I would never use.</p><div>So to help alleviate some of my stress I started compiling my own holiday gift guide. It’s kind of like the big toy catalog you used to get as a kid, only this is for adults. I’ve made a list of items I think would be very useful or exciting for your fellow Excel users, sorted by different categories (and yes, this post does contain Amazon affiliate links). Some of these items I already use on a daily basis and others are things that are on my own personal wish list. It's my biggest and best gift guide yet! Enjoy!</div><span><a name='more'></a></span><div><h2><br /></h2><h2>Microsoft Excel Books</h2></div><div><br /></div><div><a href="https://amzn.to/373XscE" target="_blank">Excel 2019 Bible by Michael Alexander</a> - Whether you are just starting out or an Excel novice, the Excel 2019 Bible is your comprehensive, go-to guide for all your Excel 2019 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features and capabilities to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more.</div><div><br /></div><div><a href="https://1.bp.blogspot.com/-Pt-MyKX4XXI/X7syLJhZEdI/AAAAAAAAHrs/85-nrbG368QcEfaJ01q84ee62SnZpuBwQCLcBGAsYHQ/s499/51aqo9qgRaL._SX397_BO1%252C204%252C203%252C200_.jpg" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="499" data-original-width="399" height="211" src="https://1.bp.blogspot.com/-Pt-MyKX4XXI/X7syLJhZEdI/AAAAAAAAHrs/85-nrbG368QcEfaJ01q84ee62SnZpuBwQCLcBGAsYHQ/w169-h211/51aqo9qgRaL._SX397_BO1%252C204%252C203%252C200_.jpg" width="169" /></a></div><div><br /></div><div><a href="https://amzn.to/3qPKbjn" target="_blank">101 Best Excel Tips (updated March 2021)</a> - <span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;">With this book, you’ll learn to apply the </span><i style="background-color: white; box-sizing: border-box; color: #0f1111; font-family: "Amazon Ember", Arial, sans-serif; font-size: 14px;"><span style="box-sizing: border-box; font-weight: 700;">must know Excel features and tricks to make your data analysis & reporting easier</span></i><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"> and will save time in the process.</span></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-TPQHCI_4XHw/YZa7YO6DKGI/AAAAAAAAIHU/NWhWcEUZ4-43fdTNHKcTe1NL2vDmUzCQgCLcBGAsYHQ/s499/best%2Bexcel%2Btips%2Bbook.jpg" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="499" data-original-width="386" height="320" src="https://1.bp.blogspot.com/-TPQHCI_4XHw/YZa7YO6DKGI/AAAAAAAAIHU/NWhWcEUZ4-43fdTNHKcTe1NL2vDmUzCQgCLcBGAsYHQ/s320/best%2Bexcel%2Btips%2Bbook.jpg" width="248" /></a></div><br /><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><span face=""Amazon Ember", Arial, sans-serif" style="background-color: white; color: #0f1111; font-size: 14px;"><br /></span></div><div><br /></div><div><a href="https://amzn.to/3qR42Ps" target="_blank">Excel VBA Programming for Dummies </a>- If you've followed my blog or newsletter for years then you'll know how much I love VBA macros and how useful they are. Learn how to program on your own with this book.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-Kbv4M7O4hfc/YZa8FQqzAjI/AAAAAAAAIHc/GGVPjoDPfe4E9boIgHU0iNkeGsfgR-9ygCLcBGAsYHQ/s499/513XbY5hskL._SX397_BO1%252C204%252C203%252C200_.jpg" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="499" data-original-width="399" height="219" src="https://1.bp.blogspot.com/-Kbv4M7O4hfc/YZa8FQqzAjI/AAAAAAAAIHc/GGVPjoDPfe4E9boIgHU0iNkeGsfgR-9ygCLcBGAsYHQ/w175-h219/513XbY5hskL._SX397_BO1%252C204%252C203%252C200_.jpg" width="175" /></a></div><br /><div><br /></div><div><h2><br /></h2><h2><br /></h2><h2><br /></h2><h2><br /></h2><div><br /></div><div><br /></div><div><br /></div><h2>Other Books Worth Reading</h2></div><div><a href="https://amzn.to/3Ft8kAp" target="_blank">Project Hail Mary by Andy Weir </a>- If you've read any of my previous gift guides you'll know I'm a big fan of Andy's book, The Martian. Project Hail Mary is similar, not as good in my opinion, but still very interesting and science heavy.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-6E3g7zpzvBY/YZa33ngZWoI/AAAAAAAAIHM/9poj2uhiwe89Wh7RryExEDMTRiOH5yutACLcBGAsYHQ/s500/51AWMwHe9rL.jpg" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="500" data-original-width="331" height="320" src="https://1.bp.blogspot.com/-6E3g7zpzvBY/YZa33ngZWoI/AAAAAAAAIHM/9poj2uhiwe89Wh7RryExEDMTRiOH5yutACLcBGAsYHQ/s320/51AWMwHe9rL.jpg" width="212" /></a></div><br /><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div>I self-published a new book earlier this year! Beside a love for roller coasters, I realized I also love all forms of transportation related to theme parks, which led me to researching <a href="https://amzn.to/3cnxnIR" target="_blank">operating and defunct steam trains and monorails</a>.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-dSZb8nBDcGs/YZa3OCIvvhI/AAAAAAAAIHE/JowleB057HUQeLFg6nOp7eFf9I4v61LoQCLcBGAsYHQ/s500/steam%2Btrains%2Band%2Bmonorails%2Bbook.jpg" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="500" data-original-width="313" height="320" src="https://1.bp.blogspot.com/-dSZb8nBDcGs/YZa3OCIvvhI/AAAAAAAAIHE/JowleB057HUQeLFg6nOp7eFf9I4v61LoQCLcBGAsYHQ/s320/steam%2Btrains%2Band%2Bmonorails%2Bbook.jpg" width="200" /></a></div><br /><div><br /></div><div><br /></div><div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div>My uncle wrote this picture book, <a href="https://amzn.to/3nZldth" target="_blank">Lily the Snail Who Wanted to Go Fast</a>. It's full of funny and educational life lessons for kids and adults alike.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;"><a href="https://1.bp.blogspot.com/-UY_6IO8ZgBQ/X8Bwpxh-PzI/AAAAAAAAHsk/yKrp_QI-IcA0vZiyKkJ8iYhFGIvDoXRlwCLcBGAsYHQ/s734/lily.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="721" data-original-width="734" height="234" src="https://1.bp.blogspot.com/-UY_6IO8ZgBQ/X8Bwpxh-PzI/AAAAAAAAHsk/yKrp_QI-IcA0vZiyKkJ8iYhFGIvDoXRlwCLcBGAsYHQ/w239-h234/lily.JPG" width="239" /></a></div></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><h2 style="clear: both; text-align: left;">Tools for the Job</h2><div><div><a href="https://amzn.to/3DvnvIZ" target="_blank">Presenter and Laser Pointer Combo</a> - I use a laser pointer nearly everyday in my day job.</div><div><br /></div><div class="separator" style="clear: both;"><a href="https://1.bp.blogspot.com/-ortcsUxdc20/YZat9jcqjnI/AAAAAAAAIGU/W-ixXG_Ek3AcjShRNpytozHbjitrE9O-ACLcBGAsYHQ/s624/laser%2Bpointer%2Bfor%2Bexcel%2Bgift%2Bguide.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="624" data-original-width="455" height="156" src="https://1.bp.blogspot.com/-ortcsUxdc20/YZat9jcqjnI/AAAAAAAAIGU/W-ixXG_Ek3AcjShRNpytozHbjitrE9O-ACLcBGAsYHQ/w114-h156/laser%2Bpointer%2Bfor%2Bexcel%2Bgift%2Bguide.JPG" width="114" /></a></div></div><div class="separator" style="clear: both;"><br /></div><div class="separator" style="clear: both;"><a href="http://amzn.to/2zQysqy">TechSmith Snagit</a> takes the hassle out of creating images and videos. Capture your screen, edit images, and deliver results. Snagit is also the only screen capture tool with built-in advanced image editing and screen recording. So you can easily create high-quality images and videos all in one program. Quickly explain a process, build visual-based documentation and be more engaging by adding images and videos to your communications. It's the tool I use to create all the images for Excel Spreadsheets Help and well worth the price.</div><div class="separator" style="clear: both;"><br /></div><div class="separator" style="clear: both;"><div><a href="https://amzn.to/37c2SCz" target="_blank">Excel 2019 Cheat Sheet</a> - Laminated quick reference showing step-by-step instructions and shortcuts for how to use Microsoft Office Excel 2019 (Windows Version). Written with trademark focus on clarity, accuracy, and the user's perspective, this guide will be a valuable resource to improve your proficiency in using Microsoft Excel 2019. This guide is suitable as a training handout, or simply an easy to use reference guide, for any type of user.</div><div><br /></div><div class="separator" style="clear: both; text-align: left;"><a href="https://1.bp.blogspot.com/-2L3O9KBtwSc/X8Bt1_Kji-I/AAAAAAAAHsE/gDW3_70rCvYmhkTd3gPKnx4GI5joH05uQCLcBGAsYHQ/s1051/excel%2B2019%2Bcheat%2Bsheet.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1051" data-original-width="814" height="287" src="https://1.bp.blogspot.com/-2L3O9KBtwSc/X8Bt1_Kji-I/AAAAAAAAHsE/gDW3_70rCvYmhkTd3gPKnx4GI5joH05uQCLcBGAsYHQ/w222-h287/excel%2B2019%2Bcheat%2Bsheet.jpg" width="222" /></a></div><div class="separator" style="clear: both; text-align: left;"><a href="https://amzn.to/3coRLZR" target="_blank">Curved LED Monitor</a> - I had never tried a curved computer screen until recently, and I am loving it! So bright and colorful too.</div><div class="separator" style="clear: both; text-align: left;"><a href="https://1.bp.blogspot.com/-wR6VkN6rjRA/YZaw5Lptk2I/AAAAAAAAIGc/sx0vz4uCq_wKeN0ywgqoksbM-c1m4AQDQCLcBGAsYHQ/s614/curved%2Bpc%2Bmonitor.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="219" data-original-width="614" height="114" src="https://1.bp.blogspot.com/-wR6VkN6rjRA/YZaw5Lptk2I/AAAAAAAAIGc/sx0vz4uCq_wKeN0ywgqoksbM-c1m4AQDQCLcBGAsYHQ/s320/curved%2Bpc%2Bmonitor.JPG" width="320" /></a></div><div class="separator" style="clear: both;"><br /></div><a href="https://amzn.to/3CuLeYm" target="_blank">Quiet Portable Desk Fan</a> - When I go into the office, the temperature of the building is always out of whack. So I took action and bought myself this portable fan that plugs into the USB on my PC. It's super quiet, has different speeds, and has worked great!</div><div class="separator" style="clear: both;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-UwLgOEmy1pE/YZa1U_lBw4I/AAAAAAAAIGs/QSJ-DkeiV4g6G4GP_w4X_M5m2wutsOJAQCLcBGAsYHQ/s618/quiet%2Bportable%2Bdesk%2Bfan%2Bfor%2Blaptop.JPG" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="609" data-original-width="618" height="186" src="https://1.bp.blogspot.com/-UwLgOEmy1pE/YZa1U_lBw4I/AAAAAAAAIGs/QSJ-DkeiV4g6G4GP_w4X_M5m2wutsOJAQCLcBGAsYHQ/w189-h186/quiet%2Bportable%2Bdesk%2Bfan%2Bfor%2Blaptop.JPG" width="189" /></a></div><br /><div class="separator" style="clear: both;"><br /></div><div class="separator" style="clear: both;"><br /><div class="separator" style="clear: both; text-align: left;"><br /></div></div><h2 style="text-align: left;">Toys, Tech, Gadgets, and More</h2><div><a href="https://www.etsy.com/market/excel_gift" target="_blank">The Etsy store</a> has a whole selection of Excel related items, from coffee mugs to T-shirts. Some of them are pretty funny and clever.</div><div><br /></div><div><a href="https://1.bp.blogspot.com/-WujD2QMW4FY/X7_dSoasZ2I/AAAAAAAAHr4/BOiMn2cG4Js4aawDJnvTOk9TlVXxYJVhwCLcBGAsYHQ/s1354/excel%2Betsy%2Bgifts.JPG" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="612" data-original-width="1354" src="https://1.bp.blogspot.com/-WujD2QMW4FY/X7_dSoasZ2I/AAAAAAAAHr4/BOiMn2cG4Js4aawDJnvTOk9TlVXxYJVhwCLcBGAsYHQ/s320/excel%2Betsy%2Bgifts.JPG" width="320" /></a></div><div><br /></div><div><a href="https://www.spreadshirt.com/shop/design/i+do+not+always+excel+but+when+i+do+its+at+spread+mens+premium+t-shirt-D5d2b9794f937640d029934c7?sellable=R43rg5RNVxu7JpGRRAo7-812-7&appearance=92" target="_blank">I do not always Excel but when I do it's at spreadsheets shirt.</a></div><div><br /></div><div><a href="https://1.bp.blogspot.com/-myk3ar6AcKg/X8BubSCXwQI/AAAAAAAAHsM/jKbvX0tJ9QcROE9OwfmNmALjfh_b2JZhQCLcBGAsYHQ/s485/excel%2Bshirt.JPG" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="451" data-original-width="485" src="https://1.bp.blogspot.com/-myk3ar6AcKg/X8BubSCXwQI/AAAAAAAAHsM/jKbvX0tJ9QcROE9OwfmNmALjfh_b2JZhQCLcBGAsYHQ/s320/excel%2Bshirt.JPG" width="320" /></a></div><div><br /></div><div><a href="https://amzn.to/3FsPg5w" target="_blank">Podcasting Microphone</a> - A purchased a legit microphone this year for two reasons: making appearances on the <a href="https://www.coaster101.com/podcast/" target="_blank">Coaster101 podcast</a> and for having better sounding <a href="https://www.youtube.com/watch?v=0V4G7H2u538&list=PLeu00YCldADlXFD1Rj0AU3_YR7M8Cf0w6" target="_blank">Excel video tutorials</a>.</div><div><br /></div><div class="separator" style="clear: both; text-align: left;"><a href="https://1.bp.blogspot.com/-YJRfWZYszgg/YZa2fsAoJhI/AAAAAAAAIG8/twpiixDgUK8QP2RY1oBaPSbeVQwEfp5mwCLcBGAsYHQ/s619/podcasting%2Bmicrophone%2Bfor%2Bvideo%2Btutorials.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="619" data-original-width="503" height="226" src="https://1.bp.blogspot.com/-YJRfWZYszgg/YZa2fsAoJhI/AAAAAAAAIG8/twpiixDgUK8QP2RY1oBaPSbeVQwEfp5mwCLcBGAsYHQ/w183-h226/podcasting%2Bmicrophone%2Bfor%2Bvideo%2Btutorials.JPG" width="183" /></a></div><br /><div><br /></div><div><a href="https://amzn.to/2KLgs8v" target="_blank">Ethernet Splitter</a> - Now that we've been working from home and home schooling a lot more often in 2020 and 2021 <a href="https://www.youtube.com/watch?v=O-XDPbaookE" target="_blank">we've upgraded our home office</a>, but we needed more ways to plug into the internet. This splitter is cheap and works perfectly.</div><div><br /></div><div><a href="https://1.bp.blogspot.com/-qcI5pDMLO3U/X8BxVmPsQHI/AAAAAAAAHss/gjYVJebi18gE8R-NlDAChf9hO58AGrIwQCLcBGAsYHQ/s1500/61PsReH1-qL._AC_SL1500_.jpg" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="810" data-original-width="1500" height="119" src="https://1.bp.blogspot.com/-qcI5pDMLO3U/X8BxVmPsQHI/AAAAAAAAHss/gjYVJebi18gE8R-NlDAChf9hO58AGrIwQCLcBGAsYHQ/w221-h119/61PsReH1-qL._AC_SL1500_.jpg" width="221" /></a></div><div><a href="https://amzn.to/3x1Zs1N" target="_blank">Laser level air grip</a> - Speaking of home work, my wife love to hang pictures and artwork (and other junk) on the walls (seriously, it looks like the inside of an Applesbees in here). We recently discovered this tool to help align things to make sure they are even and straight.</div><div><br /></div><div class="separator" style="clear: both; text-align: left;"><a href="https://1.bp.blogspot.com/-sgoWe3w-W_Y/YZaxjJ5E79I/AAAAAAAAIGk/LnimKALrtPM7-x7vJ0FwDffARYwQmiidwCLcBGAsYHQ/s711/air%2Bgrip.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="534" data-original-width="711" height="170" src="https://1.bp.blogspot.com/-sgoWe3w-W_Y/YZaxjJ5E79I/AAAAAAAAIGk/LnimKALrtPM7-x7vJ0FwDffARYwQmiidwCLcBGAsYHQ/w227-h170/air%2Bgrip.JPG" width="227" /></a></div><br /><div><br /></div><div><a href="https://amzn.to/2JhLJza" target="_blank">Anker PowerCore Fusion</a> - This is a portable power charger that plugs directly into the wall so it works as your regular charger, but is also a battery so you always have power on the go. This is something I use everyday and take wherever I go.</div><div><br /></div><div><a href="https://1.bp.blogspot.com/-YAb-kXZb5i8/W_TBxf5iHwI/AAAAAAAAG7U/9NElBU6dYpYNbcd7aWt9HDnh1Q66kahYACPcBGAYYCw/s1000/51LVZM3MZBL._SL1000_.jpg" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="1000" data-original-width="1000" height="149" src="https://1.bp.blogspot.com/-YAb-kXZb5i8/W_TBxf5iHwI/AAAAAAAAG7U/9NElBU6dYpYNbcd7aWt9HDnh1Q66kahYACPcBGAYYCw/w149-h149/51LVZM3MZBL._SL1000_.jpg" width="149" /></a></div><div><a href="https://amzn.to/3cH7Pql" target="_blank">Smart Scale</a> - After putting on a few extra pounds during COVID quarantine, I decided to try to be a little more healthy and purchased this smart scale to help. The free app on my phone tracks and shows trends.</div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-uyhRWTzcU3E/YZa16M79naI/AAAAAAAAIG0/payg00evNdoJS-MsEkD_40an9y1EnD8NwCLcBGAsYHQ/s464/smart%2Bscale.JPG" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="430" data-original-width="464" height="297" src="https://1.bp.blogspot.com/-uyhRWTzcU3E/YZa16M79naI/AAAAAAAAIG0/payg00evNdoJS-MsEkD_40an9y1EnD8NwCLcBGAsYHQ/s320/smart%2Bscale.JPG" width="320" /></a></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><br /><div><br /><div><span face="trebuchet ms, trebuchet, sans-serif" style="color: #3e3e3e;"><span style="font-size: 13px;"><br /></span></span><a href="https://amzn.to/3fFiKkN" target="_blank">LEGO Star Wars Boost Droid Commander</a> - This toy is right up my alley combining multiple things I love: Star Wars, LEGO, and programming. Kids can learn to code and develop creative problem-solving skill (fun for adults too)!</div><div><br /></div><div><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/jp1DaPoZQ8w" title="YouTube video player" width="560"></iframe></div></div></div><div><br /></div><div><br /></div><div>Need room for more toys? Consider selling your old stuff on Facebook Marketplace while making a little extra money. <a href="https://excelspreadsheetshelp.blogspot.com/2020/08/facebook-marketplace-sales-tracking.html" target="_blank">Use my spreadsheet to track your sales on FB Martketplace.</a> I've now sold over 50 items!</div><div><br /><div><a href="https://1.bp.blogspot.com/-Z52gKllebcs/XzApzHnVRlI/AAAAAAAAHOg/TPklBbnCu7UALGqYICODZtkCEYa-2R7NQCPcBGAYYCw/s1710/facebook%2Bmarketplace%2Bsales%2Btracking%2Bspreadsheet.JPG" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="464" data-original-width="1710" src="https://1.bp.blogspot.com/-Z52gKllebcs/XzApzHnVRlI/AAAAAAAAHOg/TPklBbnCu7UALGqYICODZtkCEYa-2R7NQCPcBGAYYCw/s320/facebook%2Bmarketplace%2Bsales%2Btracking%2Bspreadsheet.JPG" width="320" /></a></div></div><h2 style="text-align: left;">Your Recommendations?</h2><div><br /></div><div>Are you putting any of these items on your holiday wish list? If so, let me know which ones in the comments below. Do have anything you’d like to recommend to me?</div><div><br /></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-48972930570854430232021-08-29T19:14:00.001-07:002021-08-29T19:14:48.098-07:00How to Search an Outlook Email Folder by using an Excel VBA Macro<p>One exciting aspect of using macros in Excel is that they
can “talk” to other programs, like PowerPoint. One example I’ve shared is
<a href="https://www.youtube.com/watch?v=DqmnnLLmO9o&t=10s" target="_blank">exporting data from Excel into Microsoft Word as the basis for writing a book</a>.
Another common use is <a href="https://www.youtube.com/watch?v=T2Yi3eZwq-s&t=5s" target="_blank">exchanging information with Microsoft Outlook and writing emails from Excel</a>.
Previously, I showed how you can send emails from Excel. Today I want to show
you a quick example how you can export email data from a folder in Outlook to
Excel.</p>
<p class="MsoNormal">Let’s pretend you’ve saved emails every month with monthly
expenses for your business in a folder called “01 Reports” in your Outlook email.
You want to summarize the expenses in an Excel sheet without having to open and
copy and paste every email in the folder. A macro in Excel written with VBA is
the perfect solution for this scenario. Here’s how to do it.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-YMdmX7Yw2bY/YSw9Rc92TTI/AAAAAAAAIAM/mTKWu5ol1jwiy1kAfQSDt8KUuSTcH2EzQCLcBGAsYHQ/s215/search%2Bemail%2Bfolder%2Bvba%2Bmacro%2Bexcel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="53" data-original-width="215" height="53" src="https://1.bp.blogspot.com/-YMdmX7Yw2bY/YSw9Rc92TTI/AAAAAAAAIAM/mTKWu5ol1jwiy1kAfQSDt8KUuSTcH2EzQCLcBGAsYHQ/s0/search%2Bemail%2Bfolder%2Bvba%2Bmacro%2Bexcel.JPG" width="215" /></a></div><br /><p></p>
<p class="MsoNormal">First, setup the template. In cell A2 I am going to allow
the user to write in the name of the folder they want to search through for the
email reports to export to Excel. Then, we will place the email report date,
email sender, and the expense cost into columns B, C, and D respectively. Once
the template is setup, we can begin coding.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-e52X9HB_dj8/YSw9vPqnYoI/AAAAAAAAIAU/LnOhnemeyfMlEG4nW4ZTTNZ0N2TSRF91QCLcBGAsYHQ/s562/search%2Boutlook%2Bfolder%2Bmacro%2Bsetup.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="426" data-original-width="562" height="243" src="https://1.bp.blogspot.com/-e52X9HB_dj8/YSw9vPqnYoI/AAAAAAAAIAU/LnOhnemeyfMlEG4nW4ZTTNZ0N2TSRF91QCLcBGAsYHQ/s320/search%2Boutlook%2Bfolder%2Bmacro%2Bsetup.JPG" width="320" /></a></div><br /><p></p>
<p class="MsoNormal">Create a new macro called “Search_Email_Folder.” Open the
Visual Basic Editor (VBE). Go to Tools > references. In the object library,
scroll down and Check the box of <b>“MICROSOFT OUTLOOK 14.0 OBJECT
LIBRARY” </b>to make it available for Excel VBA.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-Ld1R1lhBvgU/YSw-P0XnXqI/AAAAAAAAIAc/t8173LogRNsPG9J0IwcK4YSgYgVpQuXlwCLcBGAsYHQ/s445/outlook%2Bobject%2Blibrary%2Bmacro%2Bvba%2Bexcel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="358" data-original-width="445" height="257" src="https://1.bp.blogspot.com/-Ld1R1lhBvgU/YSw-P0XnXqI/AAAAAAAAIAc/t8173LogRNsPG9J0IwcK4YSgYgVpQuXlwCLcBGAsYHQ/s320/outlook%2Bobject%2Blibrary%2Bmacro%2Bvba%2Bexcel.JPG" width="320" /></a></div><br /><p></p>
<p class="MsoNormal">Add a header to the top of the code that explains what the
macro does. This macro loops through a specified folder in Outlook to
export all the expense report data</p>
<p class="MsoNormal"><i>Sub Search_Email_Folder()</i></p>
<p class="MsoNormal"><i>On Error GoTo ErrHandler<o:p></o:p></i></p>
<i><br />
</i><p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'Optimize Macro
Speed<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Application.ScreenUpdating
= False<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.EnableEvents = False<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.Calculation = xlCalculationManual<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim WS As
Worksheet<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set WS =
Worksheets(1)<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'Find the last
non-blank cell in column B and clear all the old data<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim lRow As Long<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>lRow =
Cells(Rows.Count, 2).End(xlUp).Row<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>WS.Range("B2:G" & lRow).ClearContents</i><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> </span><o:p></o:p></p>
<p class="MsoNormal">The Outlook object model provides all of the functionality
necessary to manipulate data that is stored in Outlook folders, and it provides
the ability to control many aspects of the Outlook user interface (UI). What is
MAPI? Use GetNameSpace ("MAPI") to return the Outlook NameSpace
object from the Application object. The only data source supported is MAPI,
which allows access to all Outlook data stored in the user's mail stores.
This is a “late binding” example. the following code sets an object variable to
the Outlook <a href="https://docs.microsoft.com/en-us/office/vba/api/outlook.application"><span color="windowtext" style="text-decoration: none; text-underline: none;">Application</span></a> object,
which is the highest-level object in the Outlook object model. All Automation
code must first define an Outlook <b>Application</b> object to be
able to access any other Outlook objects. Most programming solutions interact
with the data stored in Outlook. Outlook stores all of its information as items
in folders. Folders are contained in one or more stores. After you set an
object variable to the Outlook <b>Application</b> object, you will
commonly set a <a href="https://docs.microsoft.com/en-us/office/vba/api/outlook.namespace"><span color="windowtext" style="text-decoration: none; text-underline: none;">NameSpace</span></a> object
to refer to MAPI, as shown in the following example.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> </span><i>Dim objOutlook
As Object<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set objOutlook
= CreateObject("Outlook.Application")<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim objNSpace
As Object<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set objNSpace
= objOutlook.GetNamespace("MAPI")<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim myFolder As
Object<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'---define the
Outlook folder to search through. refers to cell so anyone can change the text
without changing the macro code<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim
EmailFolderToSearch As String<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>EmailFolderToSearch = WS.Cells(2, 1) '—place name of folder in cell A2.
must update if insert new columns before the first one<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'error
handling if no folder specified<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>If
EmailFolderToSearch = "" Then<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>MsgBox
"No folder specificed."<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Exit Sub<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Else<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'proceed<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>End If<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>'MsgBox
EmailFolderToSearch</i></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> <i> </i></span><i>‘the email
folder to loop through is actually a sub folder of the Inbox</i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set myFolder =
objNSpace.GetDefaultFolder(olFolderInbox).Folders(EmailFolderToSearch)</i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>Dim rcvDate As Date<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim iRows As
Integer<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim objItem As
Object<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim
EmailSender As String<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim
SenderEmailAddress As String<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim
NumofReports As String<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim filID As
Integer<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim DrwPost As
Integer<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span></i><i><span style="mso-spacerun: yes;"> </span>iRows = 2</i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span></i><i><span style="mso-spacerun: yes;"> </span>MsgBox "The
number of emails found is: " & myFolder.Items.Count & " in
" & myFolder.Name & " folder."</i></p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> </span><o:p> </o:p><span style="mso-spacerun: yes;"> <i> </i></span><i>'Loop through
every email in outlook drawing folder</i></p><p class="MsoNormal"><i><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>For Each
objItem In myFolder.Items<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>If
objItem.Class = olMail Then</i></p><p class="MsoNormal"><i><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Dim
objMail As Outlook.MailItem<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set
objMail = objItem<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>rcvDate = objMail.ReceivedTime<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>EmailSender = objMail.SenderName<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>SenderEmailAddress =
objMail.SenderEmailAddress<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>If
Left(SenderEmailAddress, 3) = "/O=" Then<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>'internal gemail, skip, don't increase the row number<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span></i><i><span style="mso-spacerun: yes;"> </span>Else</i></p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span><i>‘where to put the data in the Excel sheet:</i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>WS.Cells(iRows, 2).Value = rcvDate<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>WS.Cells(iRows, 3).Value = EmailSender<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>WS.Cells(iRows, 4).Value = SenderEmailAddress<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>'find the number of reports,
information contained within the body of the email<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>filID = 0<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>DrwPost = 0<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>mailBody = objMail.Body</i><o:p></o:p></p>
<p class="MsoNormal"><i><o:p> </o:p>‘search the email body for the word REPORTS</i></p><p class="MsoNormal"><i><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>filID = InStr(1, mailBody,
"REPORTS", vbTextCompare)<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>If filID> 0 Then<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>DrwPost = filID + 6<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>NumofReports = Mid(mailBody, DrwPost, 15)<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>WS.Cells(iRows, 7).Value = NumofReports<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Else<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>'number of reports not found<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>End If<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>iRows = iRows + 1</i></p><p class="MsoNormal"><i><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>End If<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>End If</i></p><p class="MsoNormal"><i><o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Next</i><o:p></o:p></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> </span><i><span style="mso-spacerun: yes;"> </span>'Release</i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set objMail =
Nothing<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set objOutlook
= Nothing<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set objNSpace
= Nothing<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Set myFolder =
Nothing<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span></i><i>ErrHandler:</i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span>Debug.Print Err.Description<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;"> </span></i><i><span style="mso-spacerun: yes;"> </span>'Reset Macro
Optimization Settings</i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.EnableEvents = True<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.Calculation = xlCalculationAutomatic<o:p></o:p></i></p>
<p class="MsoNormal"><i><span style="mso-spacerun: yes;">
</span>Application.ScreenUpdating = True<o:p></o:p></i></p>
<p class="MsoNormal"><o:p><i> </i></o:p><i><span style="mso-spacerun: yes;"> </span>MsgBox
"Macro complete!"</i></p>
<p class="MsoNormal"><o:p><i> </i></o:p><i>End Sub</i></p><p class="MsoNormal"><o:p></o:p></p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-60093413631268127462021-07-27T19:19:00.004-07:002021-07-27T19:19:24.769-07:00Weighted Olympic Medal Count 2021<p><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">In honor of the </span><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; font-weight: 700; vertical-align: baseline; white-space: pre-wrap;">2020 Summer Olympic Games</span><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> currently being held in Tokyo, Japan (in the year 2021 no less), I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2018 Winter Olympics, </span><a href="http://excelspreadsheetshelp.blogspot.com/2016/08/weighted-olympic-medal-count-2016.html" style="text-decoration-line: none;"><span style="background-color: white; color: blue; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">2016 Summer Olympic Games</span></a><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">, </span><a href="http://excelspreadsheetshelp.blogspot.com/2014/02/weighted-winter-olympics-medal-count.html" style="text-decoration-line: none;"><span style="background-color: white; color: blue; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">2014 Winter Olympics</span></a><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> and</span><a href="http://excelspreadsheetshelp.blogspot.com/2012/08/weighted-olympic-medal-count-2012.html" style="text-decoration-line: none;"><span style="background-color: white; color: blue; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> 2012 Summer Olympics</span></a><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">. There are two primary methods most websites appear to be ranking the 2020 medal count. Most sites rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. And others rank by other factors like per capita or GDP.</span></p><span id="docs-internal-guid-a1c522da-7fff-25df-3417-1fe39bf507b8"><p dir="ltr" style="background-color: white; line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="background-color: transparent; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Pictured below is a bar chart showing all medals won for the top 22 countries (as of the time of this posting on 7-27-21). The bar chart is created in Excel by highlighting the data then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars by right clicking on them then use the drop down menu to select the data you want to change.. </span><a href="https://gum.co/medalcountsheet" style="text-decoration-line: none;"><span style="background-color: transparent; color: blue; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">You can update the chart yourself by download the Excel file here.</span></a></p><p dir="ltr" style="background-color: white; line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-aA0_nTrTJyM/YQC91bB3fsI/AAAAAAAAH_E/b9Cu-wSctZon-JMHp7OKcXlc1rsMQ1CRQCLcBGAsYHQ/s704/weighted%2Bolympic%2Bmedal%2Bcount%2B2021.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="weighted Olympic Medal count 2021 in excel" border="0" data-original-height="449" data-original-width="704" height="266" src="https://1.bp.blogspot.com/-aA0_nTrTJyM/YQC91bB3fsI/AAAAAAAAH_E/b9Cu-wSctZon-JMHp7OKcXlc1rsMQ1CRQCLcBGAsYHQ/w417-h266/weighted%2Bolympic%2Bmedal%2Bcount%2B2021.JPG" title="2020 summer games medal count spreadsheet" width="417" /></a></div><br /><p dir="ltr" style="background-color: white; line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><br /></p><p dir="ltr" style="line-height: 1.38; margin-bottom: 0pt; margin-top: 0pt;"><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">I’ve devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, previous Olympic results suddenly became quite interesting. However, for the 2020 Summer Games not too much actually changes (so far, will revisit after more events are completed).</span></p><br /><a href="https://gum.co/medalcountsheet" style="text-decoration-line: none;"><span style="background-color: white; color: blue; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;">Download the spreadsheet and see for yourself.</span></a><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS"; font-size: 10pt; font-variant-east-asian: normal; font-variant-numeric: normal; vertical-align: baseline; white-space: pre-wrap;"> I’ve shared my Olympic Medal Count spreadsheet and listed out the Olympic medals by country. How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!</span></span><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-88144458940263156652021-07-15T20:00:00.003-07:002021-07-19T17:31:24.434-07:002021 NFL Helmet Schedule Spreadsheet – finally automated!One of the <a href="https://excelspreadsheetshelp.blogspot.com/p/downloads.html" target="_blank">many templates</a> I update and release on an annual basis is my <a href="https://dsadsadsadsadsa.gumroad.com/l/nflhelmetschedule" target="_blank">NFL Helmet schedule spreadsheet</a>, where you’ll find the complete schedule for every single pro football team’s season with an image of each team’s helmet. Updating the schedule by hand is very tedious. Thankfully, <a href="https://excelspreadsheetshelp.blogspot.com/2020/09/2020-nfl-helmet-schedule-spreadsheet.html" target="_blank">a reader did it for me last year</a>. But I’ve always wanted to automate a solution. To do this, there were two main problems to solve:<br /><br />1. How to get the NFL schedule into Excel quickly without a lot of manual work<br /><br />2. How to assign the correct helmets to every game without doing any manual work<br /><br />I’ve finally got a solution for both problems and can proudly say the sheet is now fully automated. Here’s how it works.<br /><br /><h3 style="text-align: left;">Getting the NFL Schedule Into Excel</h3><br />Problem number one: how to get the complete NFL schedule into Excel without having to copy and paste 32 team’s individual schedules manually. There’s got to be an online solution, right? First, I went to <a href="https://www.nfl.com/schedules/2021/reg1/">NFL.com</a>. No good: no easily copy-able full schedule. Next, tried ESPN. Success! <a href="http://www.espn.com/nfl/schedulegrid" target="_blank">Grid format</a> is perfect for copying pasting right into Excel. As long as ESPN (or another site) always posts the schedule in this format we can update all 544 games and their helmets within a minute. If you download my template, unhide the hidden columns. The blue cells are copy and pasted directly from ESPN. I use formulas to change the three letter team abbreviations into the full team names. <div><br /></div><div>As you can see, the NFL expanded the regular season this year by one game, from 16 to 17 (plus each team gets a bye week hence 18 weeks in the regular season). The preseason is reduced form 4 to 3 games.<br /><div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-hefpXsLqIl0/YPDtrjapl7I/AAAAAAAAH-Q/vbZYgqNNP5QFFcKsNMRATZF13HKIVcDwgCLcBGAsYHQ/s925/2021%2BNFL%2Bschedule%2Bgrid%2Bformat%2Bfor%2Bexcel.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="844" data-original-width="925" src="https://1.bp.blogspot.com/-hefpXsLqIl0/YPDtrjapl7I/AAAAAAAAH-Q/vbZYgqNNP5QFFcKsNMRATZF13HKIVcDwgCLcBGAsYHQ/s320/2021%2BNFL%2Bschedule%2Bgrid%2Bformat%2Bfor%2Bexcel.JPG" width="320" /></a></div><br /><h3 style="text-align: left;">Automating Assigning All the Helmets</h3>Problem number two: how to populate the schedule with all the helmets? I was thinking about using linked pictures like I do in my <a href="http://excelspreadsheetshelp.blogspot.com/2021/01/2021-super-bowl-squares-spreadsheet.html" target="_blank">Super Bowl Squares template</a>. But this would have required adding a formula in name manager to all 544 helmets. Instead, I decided to have a macro copy and paste helmets associated with each team automatically into the schedule. This required giving all 32 helmets a unique variable name, which was time consuming, but now that I have it setup I don’t have to change again, even when it’s time to update for next year’s schedule.<br /><br />On previous versions of the sheet I divided out the two conferences on separate sheets: NFC and AFC. This year, I’ve put all the teams into one sheet. However, there is a new filter option where you can filter by NFC or AFC or even by division: AFC North, AFC South, etc.<br /><br /><a href="https://1.bp.blogspot.com/-KCHt_cpFcMM/YO44_3ik5UI/AAAAAAAAH98/6p6Jyb6GrbQe2j745JoN9eTwHJ9cz90SQCLcBGAsYHQ/s1904/2021%2BNFL%2Bhelmet%2Bschedule%2Bspreadsheet%2Btemplate%2Bdownload.JPG" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="2021-2022 nfl schedule in excel spreadsheet" border="0" data-original-height="802" data-original-width="1904" height="251" src="https://1.bp.blogspot.com/-KCHt_cpFcMM/YO44_3ik5UI/AAAAAAAAH98/6p6Jyb6GrbQe2j745JoN9eTwHJ9cz90SQCLcBGAsYHQ/w594-h251/2021%2BNFL%2Bhelmet%2Bschedule%2Bspreadsheet%2Btemplate%2Bdownload.JPG" title="nfl schedule template excel 2021" width="594" /></a><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><h3 style="clear: both; text-align: left;">Download the 2021 NFL Helmet Schedule Spreadsheet</h3><div class="separator" style="clear: both; text-align: center;"><div style="text-align: left;"><b><a href="https://dsadsadsadsadsa.gumroad.com/l/nflhelmetschedule" target="_blank">Download the 2021 NFL Helmet Schedule Spreadsheet here</a></b></div><div style="text-align: left;"><br /></div><div style="text-align: left;">Watch the video below to see how the filter works. I also so a tip in Excel how to select multiple objects at once with the mouse. And I walk through the populate helmets macro code as well. Lots of good stuff here!</div></div><div class="separator" style="clear: both; text-align: center;"><br /><div style="text-align: left;"><iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/hWb6GeY5wo8" title="YouTube video player" width="560"></iframe></div><div style="text-align: left;"><br /></div><div style="text-align: left;">Please note, an email is required to download it. I do this so you will be automatically updated you if changes or additions are made and will update you when the next year’s schedule is ready. I do not use your email for anything else.</div><div style="text-align: left;"><br /></div><div style="text-align: left;">This goes to show you a little bit of time and thinking now can save you a LOT of time and trouble later on.</div><div style="text-align: left;"><br /></div><div style="text-align: left;">As you can see, the NFL helmet schedule is printable too. You can save the spreadsheet as a PDF file or print it out and pin it up in your cubicle at work. If you do, please email or tweet me a picture of it hanging up - I'd love to see it!</div><div style="text-align: left;"><br /></div><div style="text-align: left;">As always, I welcome any comments or suggestions about how to fix or improve the sheet! How can I improve this football spreadsheet into something you’ll use all the time during pro-football season? What future features would you like to see?</div></div><br /><br /></div></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com8tag:blogger.com,1999:blog-7826720847639113939.post-77858163771793644422021-06-30T08:00:00.002-07:002021-06-30T08:00:52.482-07:00Podcast Analytics Tracking Spreadsheet<p><span style="background-color: white; color: #3e3e3e; font-family: "Trebuchet MS", sans-serif; font-size: 10pt;">When the COVID-19 pandemic struck last year,
many people were stuck at home with nothing to do so they started podcasting. As many of you may know, I love traveling to theme parks around the country. We had discussed starting a podcast for a site I also created content for, Coaster101.com, for years but it wasn’t until the
midst of the global pandemic when we finally turned it into reality. Listen to
me ramble about visiting amusement parks and riding roller coasters here: </span><span style="color: #3e3e3e; font-family: Trebuchet MS, sans-serif;"><span style="font-size: 13.3333px;"><a href="https://www.coaster101.com/podcast/">https://www.coaster101.com/podcast/</a></span></span></p>
<p class="MsoNormal"><span style="background: white; color: #3e3e3e; font-family: "Trebuchet MS",sans-serif; font-size: 10.0pt;">Once we got the hang of recording and it become
a permanent thing, we started to analyze our analytics to try to decide what
was working and what wasn’t. By understanding the data, you can make decision
to help you grow your podcast. Like everything else I do, I decided to make a spreadsheet to track specific stats I had in mind. I’ve turned it into a template
you can use, <a href="https://dsadsadsadsadsa.gumroad.com/l/podcastspreadsheet" target="_blank">available to download for free here.</a><o:p></o:p></span></p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-3WAfKsxnaUc/YNyGemWIjRI/AAAAAAAAH9Q/8q9WmwgWwEwFNZrSA7AClMaEcfO8Gzw7wCLcBGAsYHQ/s1525/podcast%2Bdownload%2Btracking%2Btemplate%2Bfor%2Banalytics.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="podcast tracking spreadsheet" border="0" data-original-height="781" data-original-width="1525" height="234" src="https://1.bp.blogspot.com/-3WAfKsxnaUc/YNyGemWIjRI/AAAAAAAAH9Q/8q9WmwgWwEwFNZrSA7AClMaEcfO8Gzw7wCLcBGAsYHQ/w456-h234/podcast%2Bdownload%2Btracking%2Btemplate%2Bfor%2Banalytics.JPG" width="456" /></a></div><br /><p class="MsoNormal"><br /></p>
<p class="MsoNormal"><span style="background: white; color: #3e3e3e; font-family: "Trebuchet MS",sans-serif; font-size: 10.0pt;"><o:p> As I always say, even if you don't have a direct use for this spreadsheet you can still learn something about Excel by examining this template.</o:p></span></p>
<p class="MsoNormal"><span style="background: white; color: #3e3e3e; font-family: "Trebuchet MS",sans-serif; font-size: 10.0pt;">For starters, it uses Ranking formulas in Excel to show the most
popular and least popular episodes. As I do with almost all of my spreadsheets,
I color code the columns so I can easily know which require manual data input
by me, which are drop down lists, and which use formulas to be left alone. I
use the TODAY() formula to help determine how many days old a podcast episode
is (because one just released will obviously have fewer downloads than other
episodes). You’ll see there are SUMIF and VLOOKUP formulas as well. Feel free to take a look:</span></p><p class="MsoNormal"><span style="background: white; color: #3e3e3e; font-family: "Trebuchet MS",sans-serif; font-size: 10.0pt;"><a href="https://dsadsadsadsadsa.gumroad.com/l/podcastspreadsheet" target="_blank">Podcast Downloads Tracking Spreadsheet here</a></span></p><span></span><span><a name='more'></a></span><p class="MsoNormal">Do you own a podcast? Or do you regularly listen to any and would recommend them to me? Let me know in the comments below!</p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-82607064104176847122021-06-21T18:57:00.005-07:002021-06-21T18:57:47.320-07:00How to Add Conditional Formatting with a Macro<p>Conditional Formatting is a useful tool in Excel that allows
you to do things like highlight duplicate cells, or color every other row in
with color, and so on. If you have a large range or table with many conditional
formatting rules, sometimes things can get a little messy. If you’re inserting,
adding, or deleting rows and columns often, your conditional formatting rules
might go from a short, highly understandable list, to a complete cluster:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-X3NW9qVMrac/YNFCgaVNsjI/AAAAAAAAH80/4Gs84AKWZtwGVNjQ4f_WS1q1-Q69Zr8OQCLcBGAsYHQ/s1456/conditional%2Bformatting%2Bwith%2Ba%2Bmacro.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="513" data-original-width="1456" height="204" src="https://1.bp.blogspot.com/-X3NW9qVMrac/YNFCgaVNsjI/AAAAAAAAH80/4Gs84AKWZtwGVNjQ4f_WS1q1-Q69Zr8OQCLcBGAsYHQ/w577-h204/conditional%2Bformatting%2Bwith%2Ba%2Bmacro.JPG" width="577" /></a></div><br /><p>One way to be able to reset your conditional formatting
rules is with a macro. We’re going to use a macro to automatically delete the
conditional formatting and then add it back.</p>
<p class="MsoNormal">First, to clear and delete all the conditional formatting
from a sheet with a macro, use this code, changing the A:AQ with whatever range
you’re using:</p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;">
</span>'--------delete the conditional formatting--------<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i>With ActiveSheet.Range("A:AQ")<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;">
</span>.FormatConditions.Delete<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i>End With</i></b></p>
<p class="MsoNormal">Now it’s time to add conditional formatting with a macro. In
this example, I have a status column C where I enter values, and based on these
values the format of my range will change.</p>
<p class="MsoNormal">Use LastRow to find the last row of data, making it a
dynamic range (meaning the size of the range changes based on how much data is
inside the range).</p>
<p class="MsoNormal"><b><i>'define the last row<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i>LastRow = Cells(Rows.Count, 1).End(xlUp).Row</i></b></p>
<p class="MsoNormal">First, define the range where you want to apply the
formatting.<o:p></o:p></p>
<p class="MsoNormal">Next, define the formula or rule. Here, I want if the value
in Column C is the letter N to change the font color to red. I use double
quotations to have a quotation. Notice there is only one $ sign. If I put $C$11
then the formula would not trickle down through the rest of the range.<o:p></o:p></p>
<p class="MsoNormal">Finally, define the condition, font color to red the color
index is 3. See the font color index here: <a href="https://www.automateexcel.com/excel-formatting/color-reference-for-color-index/">https://www.automateexcel.com/excel-formatting/color-reference-for-color-index/</a></p>
<p class="MsoNormal">This is the first rule I am adding so notice the (1) inside
the parenthesis.</p>
<p class="MsoNormal"><b><i>'-------add the conditional formatting-------<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i>'if new, change font to red<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;"> </span>With
ActiveSheet.Range("D11:AP" & LastRow)<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;">
</span>.FormatConditions.Add Type:=xlExpression,
Formula1:="=$C11=""N"""<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;">
</span>.FormatConditions(1).Font.ColorIndex = 3<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i>End With<o:p></o:p></i></b></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Now I want to add another conditional formatting rule
programmatically. This time, change the font color to green if there is an H in
the column C. Green uses a 4 in the color index. This is the 2<sup>nd</sup>
rule so notice the (2). <o:p></o:p></p>
<p class="MsoNormal"><span style="mso-spacerun: yes;"> </span><o:p></o:p></p>
<p class="MsoNormal"><b><i>'if h, change font to green<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;"> </span>With
ActiveSheet.Range("D11:AP" & LastRow)<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;">
</span>.FormatConditions.Add Type:=xlExpression,
Formula1:="=$C11=""h"""<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;">
</span>.FormatConditions(2).Font.ColorIndex = 4<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i>End With<o:p></o:p></i></b></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Finally, if there is an X in column C, I want to use
strikethrough to cross out the words.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><i>'if x, then cross-out<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;"> </span>With
ActiveSheet.Range("D11:AP" & LastRow)<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;">
</span>.FormatConditions.Add Type:=xlExpression,
Formula1:="=$C11=""x"""<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><span style="mso-spacerun: yes;">
</span>.FormatConditions(3).Font.Strikethrough = True<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i>End With<o:p></o:p></i></b></p>
<p class="MsoNormal"><o:p><b> </b>Here's what the conditional formatting rules look like after running the macro:</o:p></p>
<div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-CLWIWdBXPkI/YNFCrH7otcI/AAAAAAAAH84/FHvoedQlfiYynObw86ZXs4lDuRNoSP2aACLcBGAsYHQ/s1099/how%2Bto%2Bapply%2Bconditional%2Bformatting%2Bwith%2Ba%2Bvba%2Bmacro%2Bin%2Bexcel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="355" data-original-width="1099" height="174" src="https://1.bp.blogspot.com/-CLWIWdBXPkI/YNFCrH7otcI/AAAAAAAAH84/FHvoedQlfiYynObw86ZXs4lDuRNoSP2aACLcBGAsYHQ/w539-h174/how%2Bto%2Bapply%2Bconditional%2Bformatting%2Bwith%2Ba%2Bvba%2Bmacro%2Bin%2Bexcel.JPG" width="539" /></a></div>
<p class="MsoNormal">And that’s how you add conditional formatting with a macro. Let me know in the comments below if you have any questions.<o:p></o:p></p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com0tag:blogger.com,1999:blog-7826720847639113939.post-6513390045586635852021-06-16T19:30:00.000-07:002021-06-16T19:30:24.973-07:00How to Make a Drop Down List From a Macro<p>Drop down lists in Excel are super helpful when you want to
limit a user’s input to a set of predetermined choices. Normally, drop down
lists can be made by clicking on a cell, going to the data tab, then by using
the Data Validation function. Change Allow to List. </p><p>But what if you’re
importing data from another Excel file and want to automatically create a drop
down list? It’s possible to do using VBA. To create a drop down list from a
macro in cell A1, try this code in the VBA editor:</p><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><i>Sub ListCreator()</i></b></p><p class="MsoNormal"><b><i>Range("A1").Validation.Add
Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="OK,Maybe,No"<o:p></o:p></i></b></p><p class="MsoNormal"><b><i>End Sub</i></b></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Here, all the entries in the list will be created from the
macro. What if one of the entries needs to change? Only someone with VBA
experience will probably be able to figure out how to change it. So I like to
create the list somewhere within the spreadsheet where anyone can see it and
it’s obvious what it’s used for, then use the Name Manager to create a Named
Range to easily refer to the list. This way, if you decide to add entries later
the lists will automatically be updated as long as they used the Named Range. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-0W8qEFaiw40/YMqzJL9gnuI/AAAAAAAAH7w/NtvcACUtxS4FhXfef-m8zWW4YnCWTwEFgCLcBGAsYHQ/s584/name%2Bmanager%2Bin%2Bexcel%2Bexample%2Bfor%2Bdrop%2Bdown%2Blists%2Bvba.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="584" data-original-width="557" height="342" src="https://1.bp.blogspot.com/-0W8qEFaiw40/YMqzJL9gnuI/AAAAAAAAH7w/NtvcACUtxS4FhXfef-m8zWW4YnCWTwEFgCLcBGAsYHQ/w326-h342/name%2Bmanager%2Bin%2Bexcel%2Bexample%2Bfor%2Bdrop%2Bdown%2Blists%2Bvba.JPG" width="326" /></a></div><br /><p></p>
<p class="MsoNormal">In this example, I select my list within the Excel sheet
(column G), and give it the name “Status”. <o:p></o:p></p>
<p class="MsoNormal">To used a Named Range when you create a drop down list via
VBA, simply refer to the Named Range:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><i>Range("A2").Validation.Add
Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Formula1:="=Status"<o:p></o:p></i></b></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">To set a default value, simply set the value of the cell
after changing the data validation:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><i>Range("A2").Validation.Add
Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Formula1:="=Status"<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><o:p> </o:p></i></b></p>
<p class="MsoNormal"><b><i>Range("A2").Value = “OK”<o:p></o:p></i></b></p>
<p class="MsoNormal"><b><i><o:p> </o:p></i></b></p>
<p class="MsoNormal">It might be easier to understand by watching this short
video:<o:p></o:p></p>
<iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/lhU6Rs3r46c" title="YouTube video player" width="560"></iframe>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">That’s all there is to it! Now you know how to add a drop
down list from a macro in Excel. How often do you use drop down lists? I’d love
to know, so let me know in the comments below.<o:p></o:p></p><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com4tag:blogger.com,1999:blog-7826720847639113939.post-5102125102950113932021-03-15T19:12:00.000-07:002021-03-15T19:12:14.078-07:00Excel Tips from the Best 2021 March Madness Brackets<p>After a year hiatus it’s finally here, the college basketball March Madness brackets are back! Last year, I made a NFL Draft Game spreadsheet for the first time to try to compensate for the loss of the basketball brackets but it just wasn’t the same (<a href="https://excelspreadsheetshelp.blogspot.com/2021/03/2021-nfl-draft-game-spreadsheet-template.html" target="_blank">but I did still update it for 2021</a>). This year, the 2021 NCAA men's basketball tournament will be unlike any March Madness that has come before. All games will be played in Indiana, with most in Indianapolis. The schedule has also been change. The First Four would typically be played on Tuesday and Wednesday night with the first round being played on Thursday and Friday. </p><p>Here's the 2021 March Madness schedule:</p><p></p><ul style="text-align: left;"><li>First Four — 4 p.m. start on Thursday, March 18</li><li>First round — 12 p.m. start on Friday, March 19, and Saturday, March 20</li><li>Second round — 12 p.m. start on Sunday, March 21, and Monday, March 22</li><li>Sweet 16 — 2 p.m. start on Saturday, March 27, and 1 p.m. start on Sunday, March 28</li><li>Elite Eight — 7 p.m. start on Monday, March 29, and 6 p.m. start on Tuesday, March 30</li><li>Final Four — 5 p.m. start on Saturday, April 3</li><li>NCAA championship game — 9 p.m. Monday, April 5</li></ul><p></p><p>Once again, I will be using <a href="https://www.whistleblows.com/p/bracket.html" target="_blank">the best March Madness brackets in Excel</a>, created by David Tyler (and I will continue to use his until he decides to no longer update them). They’re very polished and easy to use. There are only 68 teams in the field but the spreadsheet is already setup to handle up to 128 teams, if they expand in the future. There are two sheets: the bracket and the pool manager. Instructions are included but its very intuitive. </p><a href="https://1.bp.blogspot.com/-7a_XMZ_uIXQ/YFAR7Zp8gnI/AAAAAAAAH1s/MkS2VD3SDMAj4fhsBcllwioaMTiw3tznwCLcBGAsYHQ/s905/2021%2Bmarch%2Bmadness%2Bbracket%2Bin%2Bexcel%2Bspreadsheet.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="march madness 2021 bracket spreadsheet template" border="0" data-original-height="810" data-original-width="905" height="392" src="https://1.bp.blogspot.com/-7a_XMZ_uIXQ/YFAR7Zp8gnI/AAAAAAAAH1s/MkS2VD3SDMAj4fhsBcllwioaMTiw3tznwCLcBGAsYHQ/w439-h392/2021%2Bmarch%2Bmadness%2Bbracket%2Bin%2Bexcel%2Bspreadsheet.JPG" width="439" /></a><br /><p><br /></p><h3 style="text-align: left;">The First 5 Things I Do When Examining Someone Else's Spreadsheet</h3><p>As I’ve said countless times before, you can learn a lot by looking at templates made by others. Here are 5 things I do when examining a new spreadsheet:</p><p>1. <b>Unhide hidden sheets, columns, and rows:</b> When you make a template others are going to be using, you want to make it look nice and clean and hide anything that could cause confusion to a first time user, which leads to hiding rows, columns, or even entire sheets in a workbook. So, the first thing I do when examining someone else’s template is look for the hidden data. Right click on the sheets tab and click “unhide”. I unhide all the hidden sheets if there are any to see what data is present. Look for any hidden columns or rows as well by seeing if any letters or numbers are skipped.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-VEokzSIoVuQ/YFASlnf1PeI/AAAAAAAAH14/4FQEfU96js0NYapOOhYfWCZyNTlgm4Y-ACLcBGAsYHQ/s1004/how%2Bto%2Bunhide%2Bsheets%2Bin%2Bexcel.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="396" data-original-width="1004" height="195" src="https://1.bp.blogspot.com/-VEokzSIoVuQ/YFASlnf1PeI/AAAAAAAAH14/4FQEfU96js0NYapOOhYfWCZyNTlgm4Y-ACLcBGAsYHQ/w496-h195/how%2Bto%2Bunhide%2Bsheets%2Bin%2Bexcel.png" width="496" /></a></div><br /><p><br /></p><p><b>2. Understand the NamedRanges:</b> Go To Formulas > Name Manager and examine what the named ranges are, what sheets and cells they refer to. Hopefully they're all named well, like in David's brackets.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-rBPKlYHy-xs/YFAS60aa4cI/AAAAAAAAH2E/0oKjQxvTwSkHIAh8FkeYjBfsi4OzO_cZACLcBGAsYHQ/s1521/how%2Bto%2Buse%2Bname%2Bmanager%2Bin%2Bexcel.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="829" data-original-width="1521" height="229" src="https://1.bp.blogspot.com/-rBPKlYHy-xs/YFAS60aa4cI/AAAAAAAAH2E/0oKjQxvTwSkHIAh8FkeYjBfsi4OzO_cZACLcBGAsYHQ/w421-h229/how%2Bto%2Buse%2Bname%2Bmanager%2Bin%2Bexcel.png" width="421" /></a></div><br /><p><br /></p><p><b>3. Look at conditional formatting rules:</b> On the Home tab, go to Conditional Formatting, click Manage Rules, then Show formatting rules for This Worksheet to view them all.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-VcEpelWzvTQ/YFATMd0dOfI/AAAAAAAAH2M/4uE3ZsDTZRYI4NiN5F-KYpJZ4Gd7h-ZUgCLcBGAsYHQ/s1920/how%2Bto%2Buse%2Bconditional%2Bformatting%2Bexcel.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1040" data-original-width="1920" height="230" src="https://1.bp.blogspot.com/-VcEpelWzvTQ/YFATMd0dOfI/AAAAAAAAH2M/4uE3ZsDTZRYI4NiN5F-KYpJZ4Gd7h-ZUgCLcBGAsYHQ/w426-h230/how%2Bto%2Buse%2Bconditional%2Bformatting%2Bexcel.png" width="426" /></a></div><br /><p><br /></p><p><b>4. Look through the formulas: </b>On the Formulas tab, click “show Formulas” to show if they were manually typed in or if there is a formula calculating the values</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-a8eu_Oye30o/YFATb89BEAI/AAAAAAAAH2U/gVXdtURzXegTBGjwdFtey5ZeQxq9RWmMgCLcBGAsYHQ/s1589/how%2Bto%2Bshow%2Bformulas%2Bin%2Bexcel.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="432" data-original-width="1589" height="130" src="https://1.bp.blogspot.com/-a8eu_Oye30o/YFATb89BEAI/AAAAAAAAH2U/gVXdtURzXegTBGjwdFtey5ZeQxq9RWmMgCLcBGAsYHQ/w478-h130/how%2Bto%2Bshow%2Bformulas%2Bin%2Bexcel.png" width="478" /></a></div><br /><p><br /></p><p><b>5. Look through the macros:</b> Hopefully, the person writing the code left lots of good comments so it’s easier to follow along with what each piece of code does</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-jL8beBvNiWA/YFASWRbw9cI/AAAAAAAAH10/nhgt1nUgLYEtLDIpgOu9APgiKehs_Aq_QCLcBGAsYHQ/s1920/march%2Bmadness%2B2021%2Bbracket%2Bvba.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1040" data-original-width="1920" height="231" src="https://1.bp.blogspot.com/-jL8beBvNiWA/YFASWRbw9cI/AAAAAAAAH10/nhgt1nUgLYEtLDIpgOu9APgiKehs_Aq_QCLcBGAsYHQ/w428-h231/march%2Bmadness%2B2021%2Bbracket%2Bvba.png" width="428" /></a></div><br /><p><br /></p><p>Watch me quickly walk through David's 2021 March Madness brackets going through the five points listed above:</p>
<iframe allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/M5MbAxolLfg" width="560"></iframe><br /><span><a name='more'></a></span><p>I'd be curious to know, what are your first thoughts <a href="https://excelspreadsheetshelp.blogspot.com/p/downloads.html" target="_blank">when you download</a> and open one of my templates? Let me know in the comments below or by joining my email list.</p><p><br /></p><p><br /></p><div><br /></div><div class="blogger-post-footer">Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!</div>Nickhttp://www.blogger.com/profile/15585454339530843458noreply@blogger.com1