tag:blogger.com,1999:blog-683683949693031582024-02-18T21:29:43.762-08:00MS Office TipsAnonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.comBlogger24125tag:blogger.com,1999:blog-68368394969303158.post-2477219467952360182015-03-19T07:41:00.001-07:002015-03-19T07:41:38.921-07:00Creating Graphical Indicator Fields in Microsoft Project<br />
Graphical indicator fields can be used to make it easier for people viewing the project plan, to know the status of a numerical field. Instead of displaying the value, a coloured icon is used as an indicator.<br />
<br />
By using graphical indicators, a field’s status can easily be viewed at a glance, in much the same way that Conditional Formatting is used on an Excel spreadsheet.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQe6ly8cGtj5HtFNVRW8zVEskrV3qfq0aUFi2hnYMnsZ-T1TEPkj1VPFJP1JfmR1ebL4mmnvz9eLPBjKr7DGIrkVB7tpCFw0r17Rvsfk281OUclCI5WkMxFkmil35ThsSBd4j9vcpp_iM/s1600/values_vs_graphics.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Values or graphical indicators" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQe6ly8cGtj5HtFNVRW8zVEskrV3qfq0aUFi2hnYMnsZ-T1TEPkj1VPFJP1JfmR1ebL4mmnvz9eLPBjKr7DGIrkVB7tpCFw0r17Rvsfk281OUclCI5WkMxFkmil35ThsSBd4j9vcpp_iM/s1600/values_vs_graphics.png" height="221" title="" width="400" /></a></div>
<br />
<br />
In this article, graphical indicators have been used to display cost variance (as shown in the image above). If we have overspent on a task then a red unhappy face is displayed, yet if we are on budget then a green happy face is shown.<br />
<br />
<h3>
Creating the Custom Field</h3>
<br />
The first step is to create the custom field. In this example, we want to create a custom field for the cost variance value.<br />
<br />
There is already a field for cost variance, but we want to create an additional one to the one that exists for the graphical indicator.<br />
<br />
<ol>
<li>When in Gantt Chart view, click the <strong>Format</strong> tab on the Ribbon and then <strong>Custom Fields</strong>.</li>
</ol>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuiD6bK1FmTQULZd-ZAfTLNwAgdSuHdIzrHw4pA6tZ1FwwtvKBcKP7MT_aSE850G_zgq4LWtj4N9SJ3vFnn1tasGsjPtsRAROH9M4vwBoUP96hnUkLIZT6Dx_lAoWESH4BmcgmQwtQJL4/s1600/custom_fields.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Completed custom fields dialog box" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuiD6bK1FmTQULZd-ZAfTLNwAgdSuHdIzrHw4pA6tZ1FwwtvKBcKP7MT_aSE850G_zgq4LWtj4N9SJ3vFnn1tasGsjPtsRAROH9M4vwBoUP96hnUkLIZT6Dx_lAoWESH4BmcgmQwtQJL4/s1600/custom_fields.png" height="400" title="" width="346" /></a></div>
<br />
<br />
<ol start="2">
<li>Click the <strong>Type</strong> list in the top right corner and select <strong>Cost</strong> as the field type. You can see that there are many other types you could use such as text, number and date.</li>
<li>Click <strong>Rename</strong> and enter a name for this new custom field. The name <em>On/Over Budget</em> has been used in this article.</li>
<li>Click the <strong>Formula</strong> button. We need to get this field to display the information from the <em>Cost Variance</em> field. Click the <strong>Fields</strong> button, then <strong>Cost</strong> and then <strong>Cost Variance</strong>.</li>
</ol>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEju-8J4jddJVPKwLcK_Qf8MIReQs3VBPQfpjO4ozM0Qa8K9bv5-XVHYgCWvb5GH18i2WW-Kxn7xDwFSibdOnCCxsjB270W8ZcSGAieg2rWERvH4rq1dFE22DkDUA0b4xu4BrfTMEHOoU4U/s1600/cost_variance.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Formula to display cost variance data" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEju-8J4jddJVPKwLcK_Qf8MIReQs3VBPQfpjO4ozM0Qa8K9bv5-XVHYgCWvb5GH18i2WW-Kxn7xDwFSibdOnCCxsjB270W8ZcSGAieg2rWERvH4rq1dFE22DkDUA0b4xu4BrfTMEHOoU4U/s1600/cost_variance.png" height="221" title="" width="400" /></a></div>
<br />
<br />
<h3>
Displaying the Graphical Indicators</h3>
<br />
Now we have our custom field, we need to set it to display the graphical indicators instead of the cost variance value.<br />
<br />
<ol>
<li><div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l2 level1 lfo2; text-indent: -18pt;">
Click the <strong>Graphical Indicators</strong> button.</div>
</li>
<li><div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l2 level1 lfo2; text-indent: -18pt;">
We need to set the criteria for them. Enter the criteria as displayed in the image below. Choose your own choice of indicator from the <strong>Image</strong> column.<span style="font-family: Chaparral Pro;"><o:p></o:p></span></div>
</li>
</ol>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0mkkC4mP_MuQQQvF9vkWPcjDXSJjp2MdcZ5wTjK7mFL1erg_6wIOtEowQONWEsS2-kBqR6_Mj1BaEitfkq7P3XYQZcFFAJhKOm0TFLHYiDQ6WSccyrHqt92d0OQ1J8g_3soGsmUMBH9A/s1600/setting_criteria_for_graphical_indicators.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Setting the criteria for the graphical indicators" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0mkkC4mP_MuQQQvF9vkWPcjDXSJjp2MdcZ5wTjK7mFL1erg_6wIOtEowQONWEsS2-kBqR6_Mj1BaEitfkq7P3XYQZcFFAJhKOm0TFLHYiDQ6WSccyrHqt92d0OQ1J8g_3soGsmUMBH9A/s1600/setting_criteria_for_graphical_indicators.png" height="243" title="" width="400" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;"><o:p></o:p></span> </div>
<br />
<div class="MsoListParagraph" style="margin: 0cm 0cm 10pt 36pt; mso-list: l2 level1 lfo2; text-indent: -18pt;">
<ol start="3">
<li>Click <strong>Ok</strong> to close both the Graphical Indicators and Custom Fields dialog.</li>
</ol>
</div>
<br />
<h3>
Inserting the Column</h3>
<br />
Now that the field has been created, you need to insert it into the table.<span style="font-family: Chaparral Pro;"><o:p></o:p></span><br />
<br />
<div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo3; text-indent: -18pt;">
<ol>
<li>Right click the column header to the right of where you want the field to appear in the table.</li>
<li>Select <strong>Insert Column</strong> and find the field in the list by beginning to type its name. In this example it is <em>On/Over Budget</em>.</li>
</ol>
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhac103dDeuD5GbjsjgSuWiF-GOCPLkMNg5Wswc8DBSCx6WfCHH2mVgWm5SwZwVbi8sKoNXxzHkCrs3fOpxZKTyXXYpZS_d7h9rIFEaL_flJKv7vOp2wXYS-8ThlYkLBJhMy1LILIH64Gg/s1600/graphical_indicators_on_table.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Graphical Indicators displayed on table" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhac103dDeuD5GbjsjgSuWiF-GOCPLkMNg5Wswc8DBSCx6WfCHH2mVgWm5SwZwVbi8sKoNXxzHkCrs3fOpxZKTyXXYpZS_d7h9rIFEaL_flJKv7vOp2wXYS-8ThlYkLBJhMy1LILIH64Gg/s1600/graphical_indicators_on_table.png" height="128" title="" width="400" /></a></div>
<br />
<br />
The graphical indicators are displayed in the column. Position your mouse pointer over the field to see the value.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSo7FR_yTaM_BEcM0Qj-6yatSRa5IuxQbo7SYlMKxOgou1BgiUeKk3YOp0Nt5olNwXLCT8k_c3kYe7W3CSAb3fqRP0RMDrNmx-m71ONyS_7wV9cy9Si3eFbAzTBpihQX1MQoBcVIQTips/s1600/show_value.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Showing the cost variance as a value" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSo7FR_yTaM_BEcM0Qj-6yatSRa5IuxQbo7SYlMKxOgou1BgiUeKk3YOp0Nt5olNwXLCT8k_c3kYe7W3CSAb3fqRP0RMDrNmx-m71ONyS_7wV9cy9Si3eFbAzTBpihQX1MQoBcVIQTips/s1600/show_value.png" title="" /></a></div>
<br />
<br />
<h3>
Watch the Video</h3>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/embed/rQCK-F6_q1Y" width="560"></iframe>
<br />
<br />
<h3>
More Microsoft Project Tutorials</h3>
<ul>
<li><a href="http://www.computergaga.com/blog/5-ms-project-settings-you-need-to-know/" target="_blank">5 Project settings you need to know</a></li>
<li><a href="http://alanmurray.blogspot.com/2013/03/assign-resources-to-tasks-ms-project.html" target="_blank">Assigning resources to a project plan</a></li>
<li><a href="http://www.computergaga.com/blog/multiple_fields_by_task_bars_in_gantt_chart/" target="_blank">Display multiple fields next to the task bars in the Gantt Chart</a></li>
<li><a href="http://www.computergaga.com/blog/ms-project-view-resource-workload-using-the-team-planner/" target="_blank">Viewing resource workload using the Team Planner</a></li>
</ul>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com1tag:blogger.com,1999:blog-68368394969303158.post-22175534642055582432014-11-02T08:51:00.002-08:002014-11-03T23:12:34.342-08:005 Advanced Lookup Formula Techniques<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">Lookup functions such as VLOOKUP are some of the most
commonly used functions in Excel. They are used to achieve many different Excel
tasks.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">If you are reading this blog post you have probably used
VLOOKUP or one of the other lookup functions before. This article looks at 5
advanced lookup techniques.</span><br />
<a name='more'></a><br />
</div>
<a href="https://www.blogger.com/null" name="multiple"></a><br />
<h3 class="MsoNormal" style="margin: 0cm 0cm 10pt;">
Lookup a Value using Multiple Conditions<o:p></o:p></h3>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">Lookup functions are used to look for a value using single
criteria. However, you can create lookup functions to search using multiple
conditions.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">In the image below a formula has been entered to return the
value where the department is <i style="mso-bidi-font-style: normal;">Training</i>
and the Region is <i style="mso-bidi-font-style: normal;">India</i>.<o:p></o:p></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLudNwz6NJDN4TVXoTUuDvdlB2fmCHEfToMPKiXnJA5cBxDMjjc43FtZZlbNeDnoaQC1j1baVxy_-4JvzsE9L5xXD5pJVWNdqWY5P8zng0CCS6XVL4knSLe9hCOOdN7fxLiqHgHa0ejL0/s1600/multiple_conditions.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Lookup data using multiple conditions" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLudNwz6NJDN4TVXoTUuDvdlB2fmCHEfToMPKiXnJA5cBxDMjjc43FtZZlbNeDnoaQC1j1baVxy_-4JvzsE9L5xXD5pJVWNdqWY5P8zng0CCS6XVL4knSLe9hCOOdN7fxLiqHgHa0ejL0/s1600/multiple_conditions.png" height="288" title="" width="400" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;"><o:p></o:p></span> </div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">The formula below has been used to create this lookup using
multiple conditions. This is an array formula so you need to press Ctrl + Shift
+ Enter to run the formula. Do not type the curly braces manually. They appear
when the formula is entered.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">{=INDEX(C2:C17,MATCH(1,(A2:A17=F1)*(B2:B17=F2),0))}<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;"><iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/OQt61reAsIo" width="420"></iframe><o:p></o:p></span><br /></div>
<br />
<h3 class="MsoNormal" style="margin: 0cm 0cm 10pt;">
Use VLOOKUP to Look Across Multiple Sheets<o:p></o:p></h3>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">What if you need a VLOOKUP function to look for a value in
more than one table? Well fear not because you can get your VLOOKUP to check
across multiple sheets, or tables, for a value.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">The formula below uses the IFERROR function to get VLOOKUP
to look in another table if it cannot find the value. This formula searches
across 3 different tables for an Employee ID.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">If the employee cannot be found then the text “Not found” is
returned.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">=IFERROR(VLOOKUP(A2,Region1!A1:B4,2,false),IFERROR(VLOOKUP(A2,Region2!A1:B4,2,false),IFERROR(VLOOKUP(A2,Region3!A1:B4,2,false),”Not
found”)))<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;"><iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/noS_cGrcZFs" width="420"></iframe><o:p></o:p></span><br /></div>
<br />
<h3 class="MsoNormal" style="margin: 0cm 0cm 10pt;">
Lookup a Picture in a List<o:p></o:p></h3>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">Lookup functions are normally used to return a value, but
they can also be used to look for and return a picture. To create a picture
lookup you cannot write a lookup function in a cell the way that you would to
return a value.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">You will need to create a defined name and write the lookup
function as its reference. The picture you want to return is then linked to the
defined name.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">Watch the video below to see how to create a picture lookup.
In this example the picture of a flag is returned dependent upon what country
is selected from a list.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;"><iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/0xJ22YLLy9M" width="560"></iframe><o:p></o:p></span><br /></div>
<br />
<h3 class="MsoNormal" style="margin: 0cm 0cm 10pt;">
Compare Two Lists for Missing Records<o:p></o:p></h3>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">A popular use of lookup functions is to compare two lists. For
example, you may want to compare one list with another and highlight the
missing items.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">In this example the MATCH function is combined with
Conditional Formatting to highlight the rows of the missing records.<o:p></o:p></span></div>
<br />
<ol>
<li><div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Chaparral Pro;">Select the table excluding the headings (the whole table is selected so that the entire row is highlighted).</span></div>
</li>
<li><div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="mso-bidi-font-family: "Chaparral Pro"; mso-fareast-font-family: "Chaparral Pro";"><span style="mso-list: Ignore;"><span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"></span></span></span><span style="font-family: Chaparral Pro;">Click the Home tab and then Conditional Formatting.</span></div>
</li>
<li><div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="mso-bidi-font-family: "Chaparral Pro"; mso-fareast-font-family: "Chaparral Pro";"><span style="mso-list: Ignore;"><span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"></span></span></span><span style="font-family: Chaparral Pro;">Select New Rule and then Use a Formula to determine which cells to format.</span></div>
</li>
<li><div class="MsoListParagraphCxSpLast" style="margin: 0cm 0cm 10pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="mso-bidi-font-family: "Chaparral Pro"; mso-fareast-font-family: "Chaparral Pro";"><span style="mso-list: Ignore;"><span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"></span></span></span><span style="font-family: Chaparral Pro;">Enter the formula below into the box provided and choose the formatting you wish to apply.<o:p></o:p></span></div>
</li>
</ol>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">=ISNA(MATCH($A2,$F$2:$F$19,0))<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">The MATCH function looks for a value in column A (beginning
with A2 as the first row of data). It looks for the value in another table that
begins from column F. <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">If the MATCH function cannot find the value then the #N/A
error is returned. The ISNA function is used to detect this and get Conditional
Formatting to change the colour of the cells in response. <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;"><iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/_suKZLHzkj4" width="560"></iframe><o:p></o:p></span><br /></div>
<br />
<h3 class="MsoNormal" style="margin: 0cm 0cm 10pt;">
Return the Address of a Value<o:p></o:p></h3>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">You may need to return the cell address of a value in a
range. You can determine the cell address by using the ADDRESS function with
the MATCH function.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">The image below shows a snapshot of a list of customers. Let’s
say you wanted to return the cell address of the city for the customer you are
looking for in this list.<o:p></o:p></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcE3uUxtROloE-zFOamE07-yUkXfLnQsvNXQJG7t-giitNIiXBdNaOieLiMCwiTei7TwAxBKPv1JJUEHxTCy5Q09oyMA5HHmjirzKabsDnWYDtNeWg1xQ8DHs7erH2rpNBg0OP9VFxtVI/s1600/return_address.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Returning a cell address with a lookup" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcE3uUxtROloE-zFOamE07-yUkXfLnQsvNXQJG7t-giitNIiXBdNaOieLiMCwiTei7TwAxBKPv1JJUEHxTCy5Q09oyMA5HHmjirzKabsDnWYDtNeWg1xQ8DHs7erH2rpNBg0OP9VFxtVI/s1600/return_address.png" height="180" title="" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;"><o:p></o:p></span> </div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">The formula below can be used to achieve this where cell A2
contains the customer ID you are looking for, and the number 5 specifies the
column you want to return (column E). <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">=ADDRESS(MATCH(A2,Customers!A:A,0),5)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">By default, the cell address is returned as an absolute cell
reference. There are extra optional arguments to the ADDRESS function allowing
you to specify the type of cell address (relative or absolute), and also to
include a sheet name in the address.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;"><iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/TKcdtSmZvyo" width="560"></iframe><o:p></o:p></span><br /></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">The Lookup and Reference functions of Excel are an
incredibly powerful and useful category of functions. They are used heavily in large
spreadsheets to automatically return data and dynamically link different
sheets.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Chaparral Pro;">This article covers 5 advanced lookup techniques. These functions
have a lot to offer an Excel user. <o:p></o:p></span></div>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-6763851976767227062014-08-04T12:05:00.002-07:002014-08-04T12:05:37.406-07:00The Pie of Pie Chart in Excel DemystifiedPie charts are used to display data points as a percentage of the whole value. Excel provides a few different variations of pie chart to choose from. One with a difference is the pie of pie chart.<br />
<br />
The pie of pie chart is used to display the smaller values of a pie chart in a secondary pie to make them more visible.<br />
<br />
Take the pie chart below for instance. The loyalty coupon, offline and referrals values make up such a small percentage of the pie chart that they are difficult to read and understand.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAZeykRU-CpQ-VcGncbJFRz7KvE97OtnMtlm-DFLOF2qhBXtKxaNEDa19eLIrDG2zyGOhRCbg8hHcxLcdCOfxvyI2v78FG2aAo5qztSvLv8VUI6W-pjZr2aYXHsPIF1PdQNFJhmKxMmyQ/s1600/pie_chart.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Pie chart struggling to handle minor values" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAZeykRU-CpQ-VcGncbJFRz7KvE97OtnMtlm-DFLOF2qhBXtKxaNEDa19eLIrDG2zyGOhRCbg8hHcxLcdCOfxvyI2v78FG2aAo5qztSvLv8VUI6W-pjZr2aYXHsPIF1PdQNFJhmKxMmyQ/s1600/pie_chart.png" height="230" title="" width="400" /></a></div>
<br />
<br />
Let’s see the same data set using a pie of pie chart. The primary pie displays the smaller values making up 5% of the whole value. These values are then displayed in a secondary pie giving much more attention to the detail.<br />
<br />
The smaller data points retain their percentages as a contribution to the whole and lines are used to visualise the enhanced segment easily.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ5KPX-q0UlGoEdpET-tvSs1XURXOEDP4v5PkHFSUFELVTzQzS53NqjAFQIffb63lADONVvjuYTiqnamKLJCDIk1msv7pJ90xayIzs7X_ABXDrB_3bb91U3_P0QKiWHM_jzvnOg2lUomE/s1600/pie_of_pie_chart.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Pie of Pie chart in Excel" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ5KPX-q0UlGoEdpET-tvSs1XURXOEDP4v5PkHFSUFELVTzQzS53NqjAFQIffb63lADONVvjuYTiqnamKLJCDIk1msv7pJ90xayIzs7X_ABXDrB_3bb91U3_P0QKiWHM_jzvnOg2lUomE/s1600/pie_of_pie_chart.png" height="241" title="" width="400" /></a></div>
<br />
<a name='more'></a><br />
<h3>
Creating a Pie of Pie Chart</h3>
<br />
The pie charts above were created using the data set below. Let’s look at how we can create a pie of pie chart from this data.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUSUpiAN_JxG15mtYxSiMercD5ZVY7em_lmRQnvkt0D5vsMXBSbOLr4So74xgp2EKbHhDxXBw0RiZAA0A5xaQfriFT4LuDjbbpCJGAW87lH-h-GaeUF5ao7Eiy3oQcH1NM4MCBXNgxvQ8/s1600/table_of_data.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Table of sales data to chart" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUSUpiAN_JxG15mtYxSiMercD5ZVY7em_lmRQnvkt0D5vsMXBSbOLr4So74xgp2EKbHhDxXBw0RiZAA0A5xaQfriFT4LuDjbbpCJGAW87lH-h-GaeUF5ao7Eiy3oQcH1NM4MCBXNgxvQ8/s1600/table_of_data.png" title="" /></a></div>
<br />
<br />
1. Select the data range you want to chart. In this example that is range A1:B9. The last three values are used for the secondary pie so you may need to sort your table first.<br />
<br />
2. Select the <strong>Insert</strong> tab, <strong>Pie</strong> and then the <strong>Pie of Pie chart</strong>.<br />
<br />
The chart is created. Further formatting can be applied to improve the look of the chart.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhr3KNU7jI1-MEGfPJgP3aUqqhQrdRiCZN5hHCYRWllS045lc7sCFmASfOPGcOr-ZxkhP6W9EVGc57iES9KwrqI4YmybNnwofxDCxmffaWyXTSIh920EA_VpIKGM1grWwXOdldiwilJQMc/s1600/pie_of_pie1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Creating a Pie of Pie chart" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhr3KNU7jI1-MEGfPJgP3aUqqhQrdRiCZN5hHCYRWllS045lc7sCFmASfOPGcOr-ZxkhP6W9EVGc57iES9KwrqI4YmybNnwofxDCxmffaWyXTSIh920EA_VpIKGM1grWwXOdldiwilJQMc/s1600/pie_of_pie1.png" height="213" title="" width="400" /></a></div>
<br />
<br />
I’m a big fan of displaying the percentage and category name on each data point of my pie charts. Let’s do this and also remove the legend.<br />
<br />
3. Click on the legend and press the <strong>Delete</strong> key on the keyboard to remove it.<br />
<br />
4. Click the <strong>Layout</strong> tab on the Ribbon and then <strong>Data Labels</strong>. Select <strong>More Data Labels Options</strong> from the list.<br />
<br />
5. Check the <strong>Category</strong> and <strong>Percentage</strong> boxes, uncheck the <strong>Value</strong> box and click <strong>Close</strong>.<br />
<br />
6. Resize the chart if necessary to improve how the data labels fit in with the chart slices.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ5KPX-q0UlGoEdpET-tvSs1XURXOEDP4v5PkHFSUFELVTzQzS53NqjAFQIffb63lADONVvjuYTiqnamKLJCDIk1msv7pJ90xayIzs7X_ABXDrB_3bb91U3_P0QKiWHM_jzvnOg2lUomE/s1600/pie_of_pie_chart.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Finished pie of pie chart" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ5KPX-q0UlGoEdpET-tvSs1XURXOEDP4v5PkHFSUFELVTzQzS53NqjAFQIffb63lADONVvjuYTiqnamKLJCDIk1msv7pJ90xayIzs7X_ABXDrB_3bb91U3_P0QKiWHM_jzvnOg2lUomE/s1600/pie_of_pie_chart.png" height="241" title="" width="400" /></a></div>
<br />
<br />
<h3>
Formatting the Secondary Pie</h3>
<br />
There are some specific formatting techniques that can be applied to the secondary pie in the chart. These include changing which data points are displayed in the secondary pie, and also changing the size in relation to the main pie.<br />
<br />
<h4>
Changing the Data Points Shown in the Secondary Pie</h4>
<br />
By default, the pie of pie chart displays the last 3 values in the secondary pie. To change which values are displayed;<br />
<br />
1. Double click the secondary pie, or right mouse click and choose <strong>Format Data Series</strong>.<br />
<br />
2. The Format Data Series dialog box appears. Specify the number of values to show in the <strong>Second plot contains the last 3 values</strong> option.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVD2obYnS4-CLbQqHAqo1NAV_QhZzW15oOF72xS0ghl0vziqh7JrvrzW9VBlEhXn9Q5aGUUObOPaW3r-JnhEoRnXqNohV667nIuNd9VdP542PlH3xjEF17_nFIcK6y1Mt6aJFUiSyAByA/s1600/secondary_pie_values.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Changing the values shown in the secondary pie" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVD2obYnS4-CLbQqHAqo1NAV_QhZzW15oOF72xS0ghl0vziqh7JrvrzW9VBlEhXn9Q5aGUUObOPaW3r-JnhEoRnXqNohV667nIuNd9VdP542PlH3xjEF17_nFIcK6y1Mt6aJFUiSyAByA/s1600/secondary_pie_values.png" height="400" title="" width="361" /></a></div>
<br />
<br />
3. Alternatively, you can change the <strong>Split Series By</strong> option to change how Excel decides which values to show. For example, the list can be changed to <strong>Percentage value</strong> and the Second plot contains all values less than changed to 15%.<br />
<br />
<h4>
Changing Size of the Secondary Pie</h4>
<br />
The size of the second pie can also be changed.<br />
<br />
1. Double click the secondary pie, or right mouse click and choose <strong>Format Data Series</strong>.<br />
<br />
2. Increase the percentage of the <strong>Second plot size</strong>. The percentage is related to the size of the main pie.<br />
<br />
<h3>
Watch the Video</h3>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/J06V_kBlhSo" width="560"></iframe><br />Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-9842506081874762082014-07-13T23:16:00.000-07:002014-11-03T14:08:45.423-08:00Create Conditional Hyperlinks in ExcelTake your hyperlinks to another level with the HYPERLINK function. This function can be used to create conditional hyperlinks that only display when required. The hyperlinks are also dynamic and can change their link location or anchor text automatically.<br />
<br />
In this blog post we will look at both of these techniques.<br />
<a name='more'></a><h3>
<br />Create Conditional Hyperlinks</h3>
<br />
In column E below a hyperlink has been used to link to the PO. This link is automatically generated when somebody states that a PO has been received by selecting yes in column D.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifIBzwuO46BZawK4VVoC1M-10eMCo40u0snLHIbhy4s3q1psZlhDO_PO2iiuL1wyaYAGdWawzVkGYmAwGBnbOB98q1MnoDixGLVcQzDta4cz4rtaD0-rx1H47INu2UV22qIZfwGnSnzLY/s1600/create_hyperlink_dependent_upon_condition.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Hyperlink dependent upon condition" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifIBzwuO46BZawK4VVoC1M-10eMCo40u0snLHIbhy4s3q1psZlhDO_PO2iiuL1wyaYAGdWawzVkGYmAwGBnbOB98q1MnoDixGLVcQzDta4cz4rtaD0-rx1H47INu2UV22qIZfwGnSnzLY/s1600/create_hyperlink_dependent_upon_condition.png" height="115" title="" width="400" /></a></div>
<br />
<br />
The following formula has been used in column E.<br />
<br />
=IF(D2="Yes",HYPERLINK(CONCATENATE("G:\Purchase Orders\",C2,".pdf"),"View PO"),"")<br />
<br />
An IF function has been used to check if a PO has been received or not and runs the HYPERLINK function if the value is true.<br />
<br />
The HYPERLINK function concatenates a link location together using a static file path and a reference to column C. In this example all PO’s are saved using the booking ref as a filename.<br />
<br />
The blue and underline text formatting has been done manually.<br />
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/-WezTrJZ7Dc" width="560"></iframe><br />
<br />
<h3>
Create a Table of Contents that uses Dynamic Hyperlinks</h3>
<br />
In large workbooks a table of contents can be created to provide an easy way to jump to the required sheet.<br />
<br />
<strong>Note:</strong> Check out this blog post on <a href="http://www.computergaga.com/blog/automatically-create-table-contents-excel/" target="_blank">using a macro to automatically create table of contents in Excel</a>.<br />
<br />
In the image below, the following HYPERLINK function has been used in cell C3. This function uses the text from cell B3 to build the link location.<br />
<br />
=HYPERLINK(CONCATENATE("#",B3,"!A1"),"Go to page")<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLvTIv-LVkp4dFPpSXOjsaSTIezdklIij_PxHCDIM3HrCA678Qoa-yTR6U_bffvLxhCZf32T0duxdV4_yCrPONujC3C7c_r937FOCZPO0RXvrm2nVxbEisZIjGRjzT0MZlHSxX-Nk0GfM/s1600/table_of_contents.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Hyperlink function used for a table of contents" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLvTIv-LVkp4dFPpSXOjsaSTIezdklIij_PxHCDIM3HrCA678Qoa-yTR6U_bffvLxhCZf32T0duxdV4_yCrPONujC3C7c_r937FOCZPO0RXvrm2nVxbEisZIjGRjzT0MZlHSxX-Nk0GfM/s1600/table_of_contents.png" height="263" title="" width="320" /></a></div>
<br />
<br />
<h3>
Watch the Video on Creating Dynamic Hyperlinks</h3>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/f4666Ae55u0" width="560"></iframe>
<br />
<div class="AW-Form-1903973396">
</div>
<script type="text/javascript">(function(d, s, id) {
var js, fjs = d.getElementsByTagName(s)[0];
if (d.getElementById(id)) return;
js = d.createElement(s); js.id = id;
js.src = "http://forms.aweber.com/form/96/1903973396.js";
fjs.parentNode.insertBefore(js, fjs);
}(document, "script", "aweber-wjs-855j47xhx"));
</script><br />Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com1tag:blogger.com,1999:blog-68368394969303158.post-27331383340779989232014-06-30T23:24:00.000-07:002014-06-30T23:24:15.515-07:00Forecasting Future Values in ExcelWhile Excel is not able to predict the future, it is a capable of forecasting future values using existing data.<br />
<br />
Excel provides many functions that are capable of predicting future values that reflect past data. These different functions cater for the different ways that your data may be plotted. For example, if your data has a steady growth or if it fluctuates dramatically.<br />
<br />
They include FORECAST, TREND, LINEST and GROWTH to name a few.<br />
<br />
However in this tutorial we will focus our attention on adding a trendline to a chart to forecast future values.<br />
<br />
<h2>
Adding a Trendline to a Chart</h2>
<br />
For this example we will be using the sales data displayed below. We would like to forecast the sales for the next 3 periods (9 days) based on the sales from the previous 6 periods (18 days).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOikr26AHzm0vncEDoVCrH4vbm1fDc7ArJQXDA4bvPCerjrTwdgaGLvM1fMpad3mTTmIqV7XLwpdg20rp2WKSuirKltdLtNWKCoNm5SjBgzGJRnj8BCGkBzaUDHPAxqtc1DQ7-AHVumvw/s1600/sales_data.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Previous sales data" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOikr26AHzm0vncEDoVCrH4vbm1fDc7ArJQXDA4bvPCerjrTwdgaGLvM1fMpad3mTTmIqV7XLwpdg20rp2WKSuirKltdLtNWKCoNm5SjBgzGJRnj8BCGkBzaUDHPAxqtc1DQ7-AHVumvw/s1600/sales_data.png" title="" /></a></div>
<br />
<br />
Click the <strong>Layout</strong> tab on the Ribbon and then <strong>Trendline</strong> (This option has changed a little over time. This example is for Excel 2007 and 2010).<br />
<br />
Select <strong>More Trendline Options</strong>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGF-RCiZ77RvupEktovC5nzcbJerOfPNsGLHVUsmCZm7F6dV1SRgHY7EhgMQ2i3ycJQfffUSCG7FQ3Q-D4SqiojhMupqQ_j59JktDdCok79eIJvhhVn_ROcMSLIO8XKrfESESnIPWokPI/s1600/trendline.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Add a trendline to a chart" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGF-RCiZ77RvupEktovC5nzcbJerOfPNsGLHVUsmCZm7F6dV1SRgHY7EhgMQ2i3ycJQfffUSCG7FQ3Q-D4SqiojhMupqQ_j59JktDdCok79eIJvhhVn_ROcMSLIO8XKrfESESnIPWokPI/s1600/trendline.png" height="400" title="" width="347" /></a></div>
<br />
<br />
There are 6 different types of trendline to choose from for your chart. The list below explains how you can decide which one is best for your data.<br />
<br />
<strong>Linear</strong> – Used for values that increase or decrease at a steady rate.<br />
<br />
<strong>Logarithmic</strong> – Used for values that increase or decrease quickly before levelling out.<br />
<br />
<strong>Polynomial</strong> – Used for values that fluctuate.<br />
<br />
<strong>Power</strong> – Used for values that increase or decrease at a specific rate.<br />
<br />
<strong>Exponential</strong> – Used for values that increase or decrease at increasingly higher rates.<br />
<br />
<strong>Moving Average</strong> – Averages out the high and low points smoothing any fluctuations.<br />
<br />
Select the trendline that best suits your data from the options provided.<br />
<br />
In the <strong>Forecast Forward</strong> field, enter the number of periods in the future that you wish the trendline to display for.<br />
<br />
Before we add the trendline, another useful option in this dialog box is the <strong>Display R-squared value</strong> on chart checkbox. The R-squared value represents the accuracy of the trendline.<br />
<br />
This value is a number between 0 and 1. The closer to 1 that the value is, the more accurate the trendline.<br />
<br />
The image below shows a linear trendline added to the chart. It has an R-squared value of 0.8236, which is fairly accurate.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPP-wGmWAYOsmlCC10Zjvx_5zHyaDoeWF3bYkLwmKLkDpIfmDpfXW0vqdZN9DRUiXYs1Z42jrNDOBlviW5ECo5jH71MOP9MXhWd71ZA9yh5waIvFMmNHtQzxQjUGWGQJs-YlWY3YdbsBU/s1600/linear_trendline.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Excel chart with a linear trendline" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPP-wGmWAYOsmlCC10Zjvx_5zHyaDoeWF3bYkLwmKLkDpIfmDpfXW0vqdZN9DRUiXYs1Z42jrNDOBlviW5ECo5jH71MOP9MXhWd71ZA9yh5waIvFMmNHtQzxQjUGWGQJs-YlWY3YdbsBU/s1600/linear_trendline.png" height="243" title="" width="400" /></a></div>
<br />
<br />
However our sales appear to be levelling out.<br />
<br />
The image below shows a logarithmic trendline being used on the chart instead. An R-squared value of 0.9186 indicate that this trendline is a better reflection on the past data.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipyrKwH7l4cVjFk231I2lCQGDozcqzlTYi2L3heG7T21E9ieAWar7GJ6-YPFuI9-YrL_Vmq6bCyuFoPeFNDQy_zONwwjeH4C1XcaQePqju7eGfbDFxSZ1WxOxZtjFw13AAP5TOBogIVWs/s1600/logarithmic_trendline.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Logarithmic trendline that better reflects sales data" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipyrKwH7l4cVjFk231I2lCQGDozcqzlTYi2L3heG7T21E9ieAWar7GJ6-YPFuI9-YrL_Vmq6bCyuFoPeFNDQy_zONwwjeH4C1XcaQePqju7eGfbDFxSZ1WxOxZtjFw13AAP5TOBogIVWs/s1600/logarithmic_trendline.png" height="241" title="" width="400" /></a></div>
<br />
<br />
<h2>
Watch the Video</h2>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/l6a7y3k3Wtk" width="560"></iframe><br />Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-35190516447713129662014-06-17T00:28:00.000-07:002014-06-17T00:28:51.137-07:00Calculate a Future Date in ExcelWhen working in Excel you may need to calculate a future date. This may be for example, when somebody’s work probation period ends, or the date a rented car should be returned.<br />
<br />
<br />
In this article we explore a few different techniques for calculating the future date. These include how to exclude certain dates such as weekends and holidays.<br />
<a name='more'></a><br />
<br />
<br />
<h3>
The Simple Formula</h3>
<br />
<br />
If you just need to calculate the date a specified number of days in the future, you can simply add this number to the necessary date.<br />
<br />
<br />
The image below shows this technique being used to add 90 days onto a start date.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJ1lH3zRaxJopYesqOZLu7wSmdvH1qwpJBdD0_YeukSakXdgqMbTuUqwu0fHtFNunhd-c0RgoahyphenhyphenbK3_YfecEsLZzuX0tXzJBwhiD_NmQWx3d4AONzdLqfssgV5jsDnElB5SMpjaXo9UM/s1600/simple_formula.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Calculate future date a specified number of days" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJ1lH3zRaxJopYesqOZLu7wSmdvH1qwpJBdD0_YeukSakXdgqMbTuUqwu0fHtFNunhd-c0RgoahyphenhyphenbK3_YfecEsLZzuX0tXzJBwhiD_NmQWx3d4AONzdLqfssgV5jsDnElB5SMpjaXo9UM/s1600/simple_formula.png" height="200" title="" width="400" /></a></div>
<br />
<br />
<br />
<h3>
Excluding Weekends from the Formula</h3>
<br />
<br />
If you want to calculate the date a specified number of work days in the future, then you need the WORKDAY function.<br />
<br />
<br />
The image below demonstrates how to find the date in 90 work days.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdO30O75SnLFPWmRaBonUPjc692Unvd8XhfNhwcqRwgPU1STqGcFBTFqiz3WWfGysH3kb-YcYkKL9aMY_CUArmxu40eTzpPalsil848eozLtRizBah6wAG-jEM7vdUGbeRQCutoDlXM1M/s1600/workday.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Excluding weekends from formula" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdO30O75SnLFPWmRaBonUPjc692Unvd8XhfNhwcqRwgPU1STqGcFBTFqiz3WWfGysH3kb-YcYkKL9aMY_CUArmxu40eTzpPalsil848eozLtRizBah6wAG-jEM7vdUGbeRQCutoDlXM1M/s1600/workday.png" height="201" title="" width="400" /></a></div>
<br />
<br />
<br />
<h3>
Excluding Holidays with the WORKDAY Function</h3>
<br />
<br />
The WORKDAY function can also be used to exclude holiday dates from your calculation if required.<br />
<br />
<br />
For this to work, you will first need to list the dates you want to exclude somewhere on a worksheet. This can then be referenced from within the formula like below.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEjgy_9ceoQe9r0evs73ggD4A9Vu1IjWmjPL9RpSNQddz_nX56g7_l_pPJoOlY42o7yAosuKgLluTzFiiHrFnKBlCnGwPKqN29dD41LMY8MJmeE5Ywnd1zwsB9BMyt2Xy1fSdDODm7CaA/s1600/excluding_holidays.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Adding holidays to the WORKDAY function" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEjgy_9ceoQe9r0evs73ggD4A9Vu1IjWmjPL9RpSNQddz_nX56g7_l_pPJoOlY42o7yAosuKgLluTzFiiHrFnKBlCnGwPKqN29dD41LMY8MJmeE5Ywnd1zwsB9BMyt2Xy1fSdDODm7CaA/s1600/excluding_holidays.png" height="131" title="" width="400" /></a></div>
<br />
<br />
<br />
<h3>
Setting Custom Weekend Parameters with WORKDAY.INTL</h3>
<br />
<br />
The aforementioned WORKDAY function uses Saturday and Sunday as the weekend dates. It may be that this is not the case for you.<br />
<br />
<br />
Fortunately the WORKDAY.INTL function was born in Excel 2010 and enables you to set custom weekend parameters.<br />
<br />
<br />
The image below show this function providing a list of different weekend examples to pick from.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6IuLGGFVc-Y8VCcIw08FknRPzLZrCbw4ZVW3ohhhHJZjgyE5QXP9QXqynP0jquFr4d_AlOnKnV9iBpMvPC_XOeTgpH2pJlSxWK4JDbm8vAFIoYH0pNfPIEVTU7SJ-8T5QxxHAwjbT0-o/s1600/workday.intl.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Using the WORKDAY.INTL function" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6IuLGGFVc-Y8VCcIw08FknRPzLZrCbw4ZVW3ohhhHJZjgyE5QXP9QXqynP0jquFr4d_AlOnKnV9iBpMvPC_XOeTgpH2pJlSxWK4JDbm8vAFIoYH0pNfPIEVTU7SJ-8T5QxxHAwjbT0-o/s1600/workday.intl.png" height="243" title="" width="640" /></a></div>
<br />
<br />
<br />
If one of these weekend parameters meets your requirements then great. However, if not you can enter a string that specifies which days of the week are working days, and which ones are non-working.<br />
<br />
<br />
This is done by entering a 0 for a working day and a 1 for a non-working day. The image below uses a string to set the weekend as Friday, Saturday and Sunday.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiKwEmvWSbLRgZxB_2ax7N0Jn5kk15ElgsA0hmW7pa09gYd4P3_bYDBzJRao5hmFurtt02Fhz32tQwQTXBgNv90ZybD4hiJXDJtHZg32RAc0GkMn2h0tvTWhqKKsjNjN4gBmx4VUGKfnA/s1600/custom_weekend.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Setting custom weekend parameters" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiKwEmvWSbLRgZxB_2ax7N0Jn5kk15ElgsA0hmW7pa09gYd4P3_bYDBzJRao5hmFurtt02Fhz32tQwQTXBgNv90ZybD4hiJXDJtHZg32RAc0GkMn2h0tvTWhqKKsjNjN4gBmx4VUGKfnA/s1600/custom_weekend.png" height="125" title="" width="400" /></a></div>
<br />
<br />
<h3>
Watch the Video</h3>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/EoIzNBsiT6g" width="560"></iframe>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-11989773326943272682014-06-07T06:23:00.000-07:002014-06-07T13:22:52.537-07:00Return the Cell Reference instead of the Value from a LookupIn Excel, Lookup functions such as Vlookup or Index and Match can be used to find a record and return a value. But what if you want to return the cell reference, or address, instead of the value.<br />
<br />
In this example we will look for a Customer using its ID and return the address of the cell that contains the customer’s city.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3bQ_eCNSxCAN16M9qU7Vck8r4IIlzTSYrLpDHPXCGUg1EcL_lnMrSQn1xxbAh5QZSMRh1UyqaPaE7M6aQ-w1mvBhGLlRG59JRXQWPSYQIAIN2fc1W1zj2k7mKqQbN0n0t4AKBvkUL1Xo/s1600/customerList.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3bQ_eCNSxCAN16M9qU7Vck8r4IIlzTSYrLpDHPXCGUg1EcL_lnMrSQn1xxbAh5QZSMRh1UyqaPaE7M6aQ-w1mvBhGLlRG59JRXQWPSYQIAIN2fc1W1zj2k7mKqQbN0n0t4AKBvkUL1Xo/s1600/customerList.png" height="112" width="400" /></a></div>
<br />
<a name='more'></a><br />
<h3>
Formula to Return the Cell Reference</h3>
<br />
The formula below can be used to return the address of the cell that contains the city for the customer you are looking for on the Customers sheet.<br />
<br />
<em>=ADDRESS(MATCH(A1,Customers!A:A,0),5)</em><br />
<br />
For example, if E1016 was entered into cell A1, then $E$7 would be returned.<br />
<br />
<h3>
The Explanation</h3>
<br />
The MATCH function is used for the lookup part of the formula. The MATCH function will look for the customer and return the row number the ID is in. <br />
<br />
It is important to note that the VLOOKUP function (the most popular lookup function in Excel) will not work in this example, as it cannot be combined with the ADDRESS function.<br />
<br />
The <a href="http://www.computergaga.com/excel/functions/address.html" target="_blank">ADDRESS function</a> is then used to create a cell reference as text from the info returned by the MATCH function. <br />
<br />
The 5 on the end of the ADDRESS function is the column number to use in the reference. Another MATCH function could be used in this argument to find the column number if necessary.<br />
<br />
The cell address is returned as an absolute cell reference by default. The ADDRESS function contains more arguments for you to specify different parameters if needed. <br />
<br />
The example below shows a 4 being added to the ADDRESS function in the formula above to return the cell address as a relative cell reference. <br />
<br />
<em>=ADDRESS(MATCH(A1,Customers!A:A,0),5,4)</em><br />
<em></em><br />
<h3>
Watch the Video</h3>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/TKcdtSmZvyo" width="560"></iframe><br />
<br />
Check out this list of the most <a href="http://www.computergaga.com/excel/functions/" target="_blank">common Excel functions</a> to learn more and expand your Excel formula skills.Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-66431706198389003722014-06-02T23:16:00.000-07:002014-06-02T23:16:37.308-07:00Fixing Formula ErrorsWorking with formulas is the building block of creating powerful and dynamic Excel spreadsheets. However when there is a problem, it is not always that easy to diagnose what the problem with your formulas is.<br />
<div>
Fortunately there a few tools provided with Excel to assist you in fixing formula errors on your spreadsheet.</div>
<div>
</div>
<h3>
Formula Error Messages</h3>
<div>
</div>
<div>
When an error occurs you will be shown an error message.</div>
<div>
</div>
<div>
Formula error messages begin with a number sign (#) and are then followed by some text that gives an indication as to the type of problem you encountered.</div>
<div>
</div>
<div>
Understanding these error messages can help you solve your formula problems.</div>
<div>
</div>
<div>
Some common formula error messages include;</div>
<ul>
<li><strong>#NAME?</strong> – Excel does not recognise the text you entered as a reference to a range, table or function.</li>
<li><strong>#REF!</strong> – Excel cannot find the cell that you referenced within the formula.</li>
<li><strong>#VALUE!</strong> – Normally a formatting issue. For example, a cell your formula is referencing may contain text instead of a number.</li>
<li><strong>#DIV/0!</strong> – Occurs when you try and divide by 0 or an empty cell.</li>
<li><strong>#NUM!</strong> – Normally occurs when you try and pass invalid data to an argument like text rather than a number.</li>
<li><strong>########</strong> - Either the number is too large for the cell that is displaying it, or it contains a date before 01/01/1900.</li>
</ul>
<h3>
Tracing Precedents and Dependents</h3>
<div>
<br />
When trying to trace the cause of a formula error, the <strong>Trace Precedents</strong> and <strong>Trace Dependents</strong> buttons will illustrate the cells that the formula is dependent on (precedents), and those affected by the formula result (dependents).<br />
</div>
<div>
These buttons can be found on the <strong>Formulas</strong> tab of the Ribbon.<br />
</div>
<div>
The image below shows the <strong>Trace Precedents</strong> button identifying the cells that the formula in B17 is dependent upon. It highlights that the issue is caused by the contents of cell B5.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFcdPRrwxxgI9eliNq6DBN-HLxhYQ1YsOaeKLkJYFRXhdc4L3EHOhAsRWYhXV1glNL_KeCjhEyav6acsS3rZTv1xKXxQdX7LPSy6JFI2_H1jtj_pcrwb6d04j7Gf4A0jP5xkAMpo_ea-4/s1600/trace_precedents.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Trace precedents of a formula" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFcdPRrwxxgI9eliNq6DBN-HLxhYQ1YsOaeKLkJYFRXhdc4L3EHOhAsRWYhXV1glNL_KeCjhEyav6acsS3rZTv1xKXxQdX7LPSy6JFI2_H1jtj_pcrwb6d04j7Gf4A0jP5xkAMpo_ea-4/s1600/trace_precedents.png" height="197" title="" width="400" /></a></div>
<div>
</div>
<h3>
Displaying Formulas<div>
</div>
</h3>
By default Excel displays the value (or formula result) in a cell and the formula in the Formula Bar. This means that you need to select a cell to view the underlying formula.<br />
<div>
</div>
If you are trying to diagnose a large formula that references other cells (which may also contain formulas), this can get irritating.<br />
<br />
<div>
Fortunately you can toggle the display of formulas on and off. Making them visible in the cell can make them easier to troubleshoot.<br />
</div>
<div>
Click the <strong>Formulas</strong> tab on the Ribbon, and then <strong>Show Formulas</strong> in the Formula Auditing group.</div>
<div>
</div>
<h3>
Show Calculation Steps and Error Checking</h3>
<div>
<br />
When a formula is quite large you may have trouble locating where the error is occurring.</div>
<div>
</div>
Good news is that Excel will show you the calculation steps of the formula. This means that it will run the formula and stop at the point just prior to the error occurring. It even underlines the problem part.<br />
<br />
<div>
Click the smart tag icon next to the cell containing the error, and select <strong>Show Calculation Steps</strong> from the menu.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEic0Wyy6PrqtD1ekl27AMlWAb5LlXjYMQXO4oTaoGtP_Mfkt0-JV4g6yEYIyO1-eENDYUzdv_ChMhj7kPaNjKt9c4Y8dF0pFsy4aWqFH2_-vozAwtVztM55GiNubP9LQv-QaXNIGioTd5I/s1600/smart_tag.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Error checking options for a formula" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEic0Wyy6PrqtD1ekl27AMlWAb5LlXjYMQXO4oTaoGtP_Mfkt0-JV4g6yEYIyO1-eENDYUzdv_ChMhj7kPaNjKt9c4Y8dF0pFsy4aWqFH2_-vozAwtVztM55GiNubP9LQv-QaXNIGioTd5I/s1600/smart_tag.png" title="" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div>
Excel steps through the formula, displaying the values used and stops when it cannot go any further without producing the error.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAUy23VyPvH5MwI7ZtzrM19-dE8JbTEMGYjMt58SrDiyJuM-MMbRnLFInWCW8qzMLUkAOeYvGBaylH0ZfsdQha_OBZPKUC98W_biUT_0ZO_2mAHGklxmEnMLADFnIBrQTyhceg4Fwn9bA/s1600/show_calculation_steps.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Show calculation steps to find source of error" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAUy23VyPvH5MwI7ZtzrM19-dE8JbTEMGYjMt58SrDiyJuM-MMbRnLFInWCW8qzMLUkAOeYvGBaylH0ZfsdQha_OBZPKUC98W_biUT_0ZO_2mAHGklxmEnMLADFnIBrQTyhceg4Fwn9bA/s1600/show_calculation_steps.png" height="170" title="" width="320" /></a></div>
<div>
<br />
This example highlights that the issue was that a cell contained text. A basic example but it demonstrated using these features to find the fault.<br />
</div>
<div>
Let’s look at a stronger example. There is an error in the formula below.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWvFyFGL-hvRcCV18PZSIHGg7ZCV5p-jKf_2Ng5Mw2GGMPeqsVAnVGl6WH_sHC9-TfiKAYYh8BXXJrewkefJkWKfzm_2n4ZkwOkGYy7UDyA_G3R00N6kkUQwKwZvVbEha5EI6gItuO0kU/s1600/stronger_example.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="#REF error in large formula" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWvFyFGL-hvRcCV18PZSIHGg7ZCV5p-jKf_2Ng5Mw2GGMPeqsVAnVGl6WH_sHC9-TfiKAYYh8BXXJrewkefJkWKfzm_2n4ZkwOkGYy7UDyA_G3R00N6kkUQwKwZvVbEha5EI6gItuO0kU/s1600/stronger_example.png" height="48" title="" width="320" /></a></div>
<div>
</div>
<div>
Click the <strong>Error Checking</strong> button on the <strong>Formulas</strong> tab. This provides an explanation of the suspected problem. It mentions that a cell may have moved, or that a function is returning a reference error.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlv9GmnR10Zz4_2c2Rvn4O6JMCTa_FYloK9eJS417pFz1JXqpgcGmKOgHGKYxh80-u_XtM_m3SeZPdoPzZy4uI02JmwnW0lUnEJspA-jBYXStTeeEFjULrXBOKoowHf_6XcV_oVd5TGoI/s1600/error_checking.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Error Checking dialog box with explanation of problem" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlv9GmnR10Zz4_2c2Rvn4O6JMCTa_FYloK9eJS417pFz1JXqpgcGmKOgHGKYxh80-u_XtM_m3SeZPdoPzZy4uI02JmwnW0lUnEJspA-jBYXStTeeEFjULrXBOKoowHf_6XcV_oVd5TGoI/s1600/error_checking.png" height="139" title="" width="320" /></a></div>
<div>
</div>
<div>
Click the <strong>Show Calculation Steps</strong> button to step into the formula. The first Vlookup function is underlined highlighting the problem function.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg76926pAkqzm8WYdCattJLIR_20B-DFFqTE7xZT0YUCeuKNT305OstvQL19yh4xcPwjYF-CVNF-4HupWyRmWykixllnOB0EOlYI30Tz6T1hw7PnlmCffZchHd_Ogjln5OH1_dbVTAtrrU/s1600/evaluate_formula.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Evaluate formula highlighting the problem area" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg76926pAkqzm8WYdCattJLIR_20B-DFFqTE7xZT0YUCeuKNT305OstvQL19yh4xcPwjYF-CVNF-4HupWyRmWykixllnOB0EOlYI30Tz6T1hw7PnlmCffZchHd_Ogjln5OH1_dbVTAtrrU/s1600/evaluate_formula.png" height="170" title="" width="320" /></a></div>
<div>
</div>
<div>
You now have the source of the problem from a formula that used 4 different functions. Click the <strong>Evaluate</strong> button to see the error occur.</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimUVDvg7VhhbAVkmZsWhh1krYPboeU81U4q91nHBgeGk9VDyYr-5jSbR8JM-Msc6GGabRFY-Jizr1nB34s4yUQVV-xUG6yUHRTdEZUtty4dGwu7C1BXL3acplLHVm5DPTKDZ8nH3TvGhA/s1600/evaluate_formula2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Formula error caused by the Vlookup function" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimUVDvg7VhhbAVkmZsWhh1krYPboeU81U4q91nHBgeGk9VDyYr-5jSbR8JM-Msc6GGabRFY-Jizr1nB34s4yUQVV-xUG6yUHRTdEZUtty4dGwu7C1BXL3acplLHVm5DPTKDZ8nH3TvGhA/s1600/evaluate_formula2.png" height="170" title="" width="320" /></a></div>
<div>
</div>
<div>
The actual problem with this formula is that we have asked to return data from column 11, but the table array for the Vlookup is only 10 columns wide.<br />
</div>
Boost your Excel formula skills to superhero status, <a href="https://www.udemy.com/excel-2010-superhero-course/?couponCode=Excel2010Superhero" target="_blank">sign up for our Excel Superhero course</a>.<br />
<br />
<h3>
More Tips for Troubleshooting Formula Errors</h3>
<br />
<div>
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/7c065Lljebo" width="560"></iframe><br /></div>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-47109028409411047472014-05-03T13:16:00.001-07:002014-05-03T13:16:19.755-07:005 Awesome Excel 2013 Flash Fill Examples<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
</o:OfficeDocumentSettings>
</xml><![endif]--><br />
Flash Fill is one of the biggest new features to be released with Excel 2013. In fact let me re-phrase that. Flash Fill is one of the biggest new features to ever be released with Excel.<br />
<br />
It can do so much with your data, the first time I saw its true power it nearly knocked me off my chair.<br />
<br />
It can be used to extract text and values from a cell, concatenate data, change its case and even reverse data.<br />
<br />
<h3>
How to use Flash Fill in Excel 2013</h3>
<br />
Probably the best thing about this powerful new tool is how easy it is to use.<br />
<br />
To use Flash Fill;<br />
<br />
<ol>
<li>Click in a cell and type in an example of how you want your data to look.</li>
<li>Press the Ctrl + E keys, or select the range of cells including the example and press the <b>Flash Fill</b> button on the <b>Data </b>tab.</li>
</ol>
<br />
<a name='more'></a><br />
<h3>
5 Awesome Flash Fill Examples</h3>
<br />
Watch the video below to see what Flash Fill can do for you in your spreadsheets. The examples covered in the video include; <br />
<ul>
<li>Separate firstname and lastname from a cell</li>
<li>Extract a name from an email address</li>
<li>Reverse and concatenate text from a cell</li>
<li>Extract the year from a date</li>
<li>Extract text between two characters</li>
</ul>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/WoRL8heTaOw" width="420"></iframe><br />Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-4213116006556304452014-04-22T05:28:00.000-07:002014-04-27T23:23:57.736-07:00Understanding Section Breaks in Microsoft WordSection breaks are one of the most useful features of Microsoft Word, especially when working with long documents.<br />
<br />
They enable you to apply page formatting to parts of a page, or to specific pages of a document.<br />
This article will look at two awesome uses of section breaks in Microsoft Word. <br />
<br />
First we look at using them to insert a landscape page in the middle of a portrait orientated document. We then look at using them to start page numbering in a document from a specific page.<br />
<br />
<br />
<h2>
<a name='more'></a>Insert One Landscape Page in the Middle of a Document</h2>
To achieve this we will need two section breaks. One before the landscape page, and one after it. By sectioning of a page like this we can then apply the necessary page formatting (in this case page orientation), to it without affecting the other pages of the document.<br />
<br />
1. Position the insertion point in the document where you want the section to start.<br />
<br />
2. Click the <b>Page Layout</b> tab on the Ribbon and then the <b>Breaks</b> button. Select <b>Next Page</b> from the list of section breaks available. This will start a new section and a new page.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje5d0wX0lv0D9IDF_Z8vQ551M_Ni7YuXmjyYYNHVWceR1ltlpn7BmOxckeWIXDkQ-zNlQXFjvdGZGcQuSznhZwqttkydVxed4D6Ow6r3BVMqBOXpFjEmbrPBB-4KvYxwrqkUDMn-Zm5ak/s1600/insert_section_break.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Insert section break in MS Word" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje5d0wX0lv0D9IDF_Z8vQ551M_Ni7YuXmjyYYNHVWceR1ltlpn7BmOxckeWIXDkQ-zNlQXFjvdGZGcQuSznhZwqttkydVxed4D6Ow6r3BVMqBOXpFjEmbrPBB-4KvYxwrqkUDMn-Zm5ak/s1600/insert_section_break.png" height="400" title="" width="246" /></a></div>
<br />
<br />
<br />
3. Repeat this action to insert another next page section break. Our document is now broken into three different sections.<br />
<br />
4. Position the insert point between the two section breaks (or directly before the second one) to ensure you are on section 2. Tip: Click the <b>Show/Hide</b> button on the <b>Home</b> tab to make the section breaks visible.<br />
<br />
5. Click the <b>Orientation</b> button and select <b>Landscape</b>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7L_MGxplZUANKBVHC3MTM6n4BC_LvDKaBAyrsy7sjdqU15Me5fvXaxAKwXh4mnMZaVYrPRvKYncqNZx3aNhLTW5RPSp8BnKh1xXV3dDWvO4l8sJw_ybGZ_NkCyhcc9_UXWYIyG4UQhes/s1600/landscape_page.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Landscape page in middle of document" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7L_MGxplZUANKBVHC3MTM6n4BC_LvDKaBAyrsy7sjdqU15Me5fvXaxAKwXh4mnMZaVYrPRvKYncqNZx3aNhLTW5RPSp8BnKh1xXV3dDWvO4l8sJw_ybGZ_NkCyhcc9_UXWYIyG4UQhes/s1600/landscape_page.png" height="164" title="" width="400" /></a></div>
<br />
<h3>
<br />Watch the Video</h3>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/NfBsIuglKLA?rel=0" width="560"></iframe><br />
<h2>
<br />Start Page Numbering from Page 3</h2>
<br />
Probably the most common use of section breaks in MS Word is for headers and footers. They can be used to apply specific header and/or footer content to certain pages or chapters in a document.<br />
For example, we will look at using section breaks to start page numbering from page 3 in a document.<br />
<br />
1. Position the insertion point at the bottom of page 2. If there is a page break already there then remove it.<br />
<br />
2. Click the <b>Breaks</b> button on <b>Page Layout</b> and select the <b>Next Page</b> section break.<br />
<br />
3. Double click inside the top margin of page 3 to open up the header area of the page. Scroll down to the bottom and click inside the footer area and position the insert point where you would like the page number to appear.<br />
<br />
4. Before we insert the page number, we will need to break the link to the previous section. Click the <b>Link to Previous</b> button on the <b>Design</b> tab under Header & Footer Tools. This step is the key difference between using section breaks with header and footers and other page formatting techniques.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZtM9UpucACRiU2Y95rbtRLuTFqUNV6mhT8A8wh4le2rUpILNqbLLxjINBpvh34IPOoS0oZVURqaV9uNiDipgWi-SirZq9ZnY_UPFH9yyo1IqJGjMCfVI6JdlUYu4LAliHLRHJo3Z-yJM/s1600/break_link_to_previous.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Break the Link to Previous on the footer" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZtM9UpucACRiU2Y95rbtRLuTFqUNV6mhT8A8wh4le2rUpILNqbLLxjINBpvh34IPOoS0oZVURqaV9uNiDipgWi-SirZq9ZnY_UPFH9yyo1IqJGjMCfVI6JdlUYu4LAliHLRHJo3Z-yJM/s1600/break_link_to_previous.png" height="180" title="" width="400" /></a></div>
<br />
<br />
<br />
5. Click the <b>Page Number</b> button, <b>Current Position</b> and select the page number style of your choice.<br />
<br />
<strong>Watch the Video</strong><br />
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/y9EjNg-ltOQ" width="560"></iframe><br />
<br />
<br />
There are many great uses of section breaks. This article explains two common scenarios. Go explore and see what section breaks can do for your documents.Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-91714249678473382922014-02-08T14:05:00.000-08:002014-04-20T00:58:03.251-07:00Calculate Median and Mode Using Multiple Conditions This tutorial looks at how you can create conditional median and mode formulas.<br />
<br />
When calculating averages based on multiple conditions, Excel provides a function called AVERAGEIFS. This is great for calculating the mean of a set of numbers. However they do not provide an equivalent for calculating the median and mode using multiple conditions.<br />
<br />
To achieve this we will need to create an array formula by nesting the IF function within the median and mode functions.<br />
<br />
For this example we will be using a list of real estate prices.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTx_s8VvX2Lwi0YpuIE8KavZSH3XI8-AqybIMTYxUjxDQzqm643gux1PtRcVzSP1x9t49VGevYqCfKc-Dndrbyt0Uu3fGdDTMWspUUHnRQYGwcVeybndSSapTibi_ETP3zvklEy8DLig0/s1600/real_estate_prices.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Real estate prices" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTx_s8VvX2Lwi0YpuIE8KavZSH3XI8-AqybIMTYxUjxDQzqm643gux1PtRcVzSP1x9t49VGevYqCfKc-Dndrbyt0Uu3fGdDTMWspUUHnRQYGwcVeybndSSapTibi_ETP3zvklEy8DLig0/s1600/real_estate_prices.png" height="160" title="" width="400" /></a></div>
<h3>
</h3>
<h3>
Conditional Median Formula</h3>
The MEDIAN function is used to calculate the middle value from a set of values. The formula below, entered in cell H3, calculates the median real estate price for a specific town.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjq5OiT8TFxY1c2OR5tJLtN4H8ZL79YV47FhpQPFnl3K9djLFVa0BCVAhAzQwhH9Zx6-ontG88MvAeeBdINApUlzvUTnjwvDXU4v3D-Nm7ndP5I-Z7T6EE1DOf1IysjVcSJiuv1gnw5aMo/s1600/conditional_median.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Conditional median formula" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjq5OiT8TFxY1c2OR5tJLtN4H8ZL79YV47FhpQPFnl3K9djLFVa0BCVAhAzQwhH9Zx6-ontG88MvAeeBdINApUlzvUTnjwvDXU4v3D-Nm7ndP5I-Z7T6EE1DOf1IysjVcSJiuv1gnw5aMo/s1600/conditional_median.png" height="207" title="" width="400" /></a></div>
<br />
<br />
<a name='more'></a>The IF function is used to apply a condition. The condition in this case being that the town must be equal to the contents of cell G3, currently St Ives.<br />
<br />
When creating an array formula you need to press Ctrl + Shift + Enter as opposed to just Enter. This will place curly braces at each end of the formula. You do not type these curly braces, Excel will put them in.<br />
<br />
The conditional median formula returns £230,000.<br />
<br />
This is because the values 190,000; 195,000; 210,000; 220,000; 240,000; 240,000; 245,000; 300,000 meet the condition of being in the town of St Ives. <br />
<br />
There are 8 values, an even number, and the mean of the middle two is 230,000.<br />
<br />
(220,000 + 240,000)/2 = 230,000<br />
<br />
<h3>
Conditional Mode Formula</h3>
The MODE function is used to return the value that occurs most frequently within a set of values. The formula below, enter in cell I3, calculates the modal real estate price for a specific town.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxFCE9y8ACmhZ5bCeodqUOPUKk_tfxwZmMPJKZlhgn6qhhexcAuQnP41LJOiAKF0zHqmdWrbYUVnEKYU9r8t-1jmstUlKcZVY8a_ABW_U_KcWbsjq9GRPapkk8yLf3OecMBJqS9TTtfYA/s1600/conditional_mode.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Conditional mode formula" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxFCE9y8ACmhZ5bCeodqUOPUKk_tfxwZmMPJKZlhgn6qhhexcAuQnP41LJOiAKF0zHqmdWrbYUVnEKYU9r8t-1jmstUlKcZVY8a_ABW_U_KcWbsjq9GRPapkk8yLf3OecMBJqS9TTtfYA/s1600/conditional_mode.png" height="207" title="" width="400" /></a></div>
<br />
<br />
The price that occurs the most is £240,000.<br />
<br />
<h3>
Calculate Median and Mode Using Multiple Conditions</h3>
To add further conditions to our median and mode formulas, we would have to enclose each condition within parentheses and separate them with the * (used to determine AND logic between conditions).<br />
<br />
The formula below was entered in cell I6 to return the median value for the St Ives office (G6) and greater than 3 bedrooms (H6).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_AqHuV_vvyKNMkvalCGraFqtj5Ve8TFYikHVzf0SDQtWFcdTJ14uGVzy5YdgTfkdMLSCSZqh1U-1sd3dZid7N_ttAQjhgrUazDxwPZtDVWp5nw66_ZHEKwid3oyCEDpPmoBmxpabSW3c/s1600/median_multiple_conditions.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Median formula with multiple conditions" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_AqHuV_vvyKNMkvalCGraFqtj5Ve8TFYikHVzf0SDQtWFcdTJ14uGVzy5YdgTfkdMLSCSZqh1U-1sd3dZid7N_ttAQjhgrUazDxwPZtDVWp5nw66_ZHEKwid3oyCEDpPmoBmxpabSW3c/s1600/median_multiple_conditions.png" height="207" title="" width="400" /></a></div>
<br />
<br />
Change the function name to mode to return the modal value based on multiple conditions. Test the formula out by changing the town and number of bedrooms.<br />
<br />
<h3 class="MsoNormal">
Watch the Video</h3>
<div class="MsoNormal">
<br /></div>
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/B3OENJFAe30?rel=0" width="560"></iframe>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-49728823129416514432013-10-07T23:49:00.002-07:002014-12-02T23:48:20.731-08:00Create an Excel Soccer League Table Generator<br />
Need a league table generator for a sports league you compete in or manage?<br />
<br />
I love sport and Excel to merge the two is enjoyable for me. I have created a league table generator in Excel so that when results are entered for a fixture, the league table will calculate and rank each team accordingly.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQaDiVd3QjtKsozMJrB6EhNJfHvFlJpSyrhBZp7nvoT-CAvh7BGFNvDNjgaegRA7z93sRHpc5m_ve6rpsjNLBtYKX7GGg8HHhlVyMyXCc55rVJnB78QUPQps_4543TPZZtatLHPEoY9z4/s1600/excel_league_table.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Excel soccer league table generator" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQaDiVd3QjtKsozMJrB6EhNJfHvFlJpSyrhBZp7nvoT-CAvh7BGFNvDNjgaegRA7z93sRHpc5m_ve6rpsjNLBtYKX7GGg8HHhlVyMyXCc55rVJnB78QUPQps_4543TPZZtatLHPEoY9z4/s400/excel_league_table.png" height="162" title="" width="400" /></a></div>
<br />
<br />
You don't need to do a thing. After setup just enter the results and let the league table take care of the rest.<br />
<br />
<a data-mce-href="http://www.computergaga.com/excel/files/league_table_with_tables.xlsx" href="http://www.computergaga.com/excel/files/league_table_with_tables.xlsx" title="Excel league table generator">Download the Excel league table generator</a> <br />
<br />
To use the spreadsheet;<br />
<ol>
<li>Enter the team names onto the Teams sheet</li>
<li>Enter the fixtures onto the fixtures sheet (<a href="http://www.computergaga.com/blog/excel-fixture-list-creator/" target="_blank">check out my Excel fixture list creator</a>) </li>
<li>Enter the results as they happen</li>
</ol>
Check out the <a href="https://www.udemy.com/excel-league-tables-and-tournaments/?couponCode=Sports19" target="_blank">Create sports league tables and tournament in Excel</a> online course and learn how you can set up your own league tables now.
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 5pt;">
</div>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com41tag:blogger.com,1999:blog-68368394969303158.post-47630041469856877472013-08-09T08:17:00.004-07:002013-08-09T08:17:38.993-07:00Calculate Length of Service in Excel<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="MsoNormal">
If you are using Excel to store data about employees in a
company, you may at some point need to calculate their length of service. Let’s
say we want to return how many years and months an employee has worked for our company.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The DATEDIF function in Excel is used to calculate the
difference between two dates. The difference can be returned as the number of
years, months or days.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
As we wish to return the number of years and months that
they have worked we will concatenate two DATEDIF functions together.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<a name='more'></a><br />
<div class="MsoNormal">
<b><br /></b></div>
<h2>
Calculate Length of Service
Formula</h2>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
Using the data in the spreadsheet below we can calculate the
length of service for each employee in column E by using the following formula.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<i>=DATEDIF(C2,D2,”y”)&”
years, “ & DATEDIF(C2,D2,”ym”)&”months”<o:p></o:p></i></div>
<div class="MsoNormal">
<i><br /></i></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsDvOHGDb4K_0ISrH-d-QAtdAIOKbZgJ2ZdVtvfWMu7J1k3NJaCUq0AeVR1mItkzr7Mz_oT6xRHphlDdUot41lFiebgSoe4AcGYViIO-Hc3BToxWn6HWgFEvkluiFC0glnNeMN8xxw-1I/s1600/length+of+service.png" imageanchor="1"><img alt="Calculate length of service using the DATEDIF function" border="0" height="56" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsDvOHGDb4K_0ISrH-d-QAtdAIOKbZgJ2ZdVtvfWMu7J1k3NJaCUq0AeVR1mItkzr7Mz_oT6xRHphlDdUot41lFiebgSoe4AcGYViIO-Hc3BToxWn6HWgFEvkluiFC0glnNeMN8xxw-1I/s400/length+of+service.png" title="" width="400" /></a></div>
<div class="MsoNormal">
<i><br /></i></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
It is important that columns C and D are formatted as dates.
If you receive an error for your formula this is a good place to check first
for mistakes.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
If you do not have an end date yet for the employee, the
TODAY() function can be inserted into the formula in place of D2. This will
calculate the length of service up to the current date.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<h2>
The DATEDIF Function</h2>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
The DATEDIF function is not documented in Excel (for some
strange reason) so when you type it into a cell no information is displayed.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Its syntax however is;<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<i>=DATEDIF(Start Date,
End Date, Interval)<o:p></o:p></i></div>
<div class="MsoNormal">
<i><br /></i></div>
<div class="MsoNormal">
In our formula we wrote one that returned the number of year
served, another for the number of months remaining after years is calculated
and joined them together.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The & is also used to concatenate, or join, the years
and months text into our answer.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-GB; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">If you like this
check out these <a href="http://www.computergaga.com/blog/2013/08/5-awesome-date-functions-in-excel/" target="_blank">5 awesome date functions in Excel</a>.</span><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-GB; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<h3>
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-GB; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">Watch the Video</span></h3>
<br />
Watch the <iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/l2K0kRUlBow" width="560"></iframe>Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com7tag:blogger.com,1999:blog-68368394969303158.post-5809480135123705782013-08-05T01:16:00.003-07:002013-09-08T23:00:42.105-07:00Excel VBA: List All the Excel Files in a folder<div class="module moduleText color0" id="mod_22675382" style="background-color: white; clear: left; color: #333333; font-family: Arial, Helvetica, sans-serif; font-size: 14px; line-height: 20.6875px; margin: 0px 0px 1.4em; padding: 0px;">
<div class="txtd" id="txtd_22675382" style="margin: 0px; padding: 0px; word-wrap: break-word;">
<div style="margin-bottom: 0.75em; padding: 0px;">
Using Excel VBA you can get a list of all the files in a folder and enter them on a worksheet. We can also retrieve certain information about the file if required. This macro will list the file names and the date they were last modified.</div>
</div>
</div>
<div class="module moduleText color0" id="mod_22675485" style="background-color: white; clear: left; font-family: Arial, Helvetica, sans-serif; font-size: 14px; line-height: 20.6875px; margin: 0px 0px 1.4em; padding: 0px;">
<h2 class="subtitle" style="color: #333333; font-family: arial, helvetica, sans-serif; font-size: 1.2em; line-height: normal; margin: 0px 0px 0.6em; padding: 0px;">
How does it Work?</h2>
<div class="txtd" id="txtd_22675485" style="margin: 0px; padding: 0px; word-wrap: break-word;">
<div style="color: #333333; margin-bottom: 0.75em; padding: 0px;">
The folder picker dialog box is used to make it easier for the user to select the folder they want to return files from. An If statement has been included to ensure that the user did select a folder.</div>
<div style="color: #333333; margin-bottom: 0.75em; padding: 0px;">
The Dir function will be used to return each filename from the folder or directory. This function will then be used to iterate through each file in turn (<a href="http://alanmurray.blogspot.co.uk/2013/05/excel-vba-dir-function.html" style="color: #551a8b; outline: 0px; text-decoration: none;">Learn more about the Dir function in Excel VBA</a>).</div>
<div style="color: #333333; margin-bottom: 0.75em; padding: 0px;">
The macro in this tutorial will list all Excel files in a folder to the worksheet. This has been specified by using *.xls in the Dir function. The wildcard and extension can be altered to list the files you want, or omitted entirely to list all files in the folder.</div>
<div style="color: #333333; margin-bottom: 0.75em; padding: 0px;">
The FileDateTime method has been used to capture the date the files were last created or modified.<br />
<br />
<a name='more'></a><br /></div>
<h2 style="color: #333333;">
The Code</h2>
<div style="color: #333333; margin-bottom: 0.75em; padding: 0px;">
<i>Sub ImportFileList()</i></div>
<div class="MsoNormal">
<i>Dim MyFolder As String <span style="color: #38761d;">'Store the folder
selected by the using</span></i></div>
<div class="MsoNormal">
<i><o:p></o:p></i></div>
<div class="MsoNormal">
<i>Dim FiletoList As String </i><i><span style="color: #38761d;">'store the name of
the file ready for listing</span></i></div>
<div class="MsoNormal">
<i>Dim NextRow As Long <span style="color: #38761d;">'Store the row to write the filename to</span></i></div>
<div class="MsoNormal">
<i><br /></i>
<i>On Error Resume Next</i></div>
<div class="MsoNormal">
<i><br /></i>
<i>Application.ScreenUpdating = False</i></div>
<div class="MsoNormal">
<i><span style="color: #38761d;"><br /></span></i>
<i><span style="color: #38761d;">'Display the folder picker dialog box for user selection of
directory</span><o:p></o:p></i></div>
<div class="MsoNormal">
<i>With Application.FileDialog(msoFileDialogFolderPicker)<o:p></o:p></i></div>
<div class="MsoNormal">
<i> .Title =
"Please select a folder"<o:p></o:p></i></div>
<div class="MsoNormal">
<i> .Show<o:p></o:p></i></div>
<div class="MsoNormal">
<i> .AllowMultiSelect
= False<o:p></o:p></i></div>
<div class="MsoNormal">
<i> If .SelectedItems.Count
= 0 Then<o:p></o:p></i></div>
<div class="MsoNormal">
<i> MsgBox
"You did not select a folder"<o:p></o:p></i></div>
<div class="MsoNormal">
<i> Exit Sub<o:p></o:p></i></div>
<div class="MsoNormal">
<i> End If<o:p></o:p></i></div>
<div class="MsoNormal">
<i> MyFolder =
.SelectedItems(1) & "\"<o:p></o:p></i></div>
<div class="MsoNormal">
<i>End With</i></div>
<div class="MsoNormal">
<i><span style="color: #38761d;"><br /></span></i>
<i><span style="color: #38761d;">'Dir finds the first Excel workbook in the folder</span><o:p></o:p></i></div>
<div class="MsoNormal">
<i>FiletoList = Dir(MyFolder & "*.xls")<o:p></o:p></i></div>
<div class="MsoNormal">
<i>Range("A1").Value = "Filename"<o:p></o:p></i></div>
<div class="MsoNormal">
<i>Range("B1").Value = "Date Last Modified"<o:p></o:p></i></div>
<div class="MsoNormal">
<i>Range("A1:B1").Font.Bold = True</i></div>
<div class="MsoNormal">
<i><span style="color: #38761d;"><br /></span></i>
<i><span style="color: #38761d;">'Find the next empty row in the list</span><o:p></o:p></i></div>
<div class="MsoNormal">
<i>NextRow = Application.CountA(Range("A:A")) + 1</i></div>
<div class="MsoNormal">
<i><span style="color: #38761d;"><br /></span></i>
<i><span style="color: #38761d;">'Do whilst the dir function returns an Excel workbook</span><o:p></o:p></i></div>
<div class="MsoNormal">
<i>Do While FiletoList <> ""</i></div>
<div class="MsoNormal">
<i> Cells(NextRow,
1).Value = FiletoList <span style="color: #38761d;">'Write the filename into the next available cell</span><o:p></o:p></i></div>
<div class="MsoNormal">
<i> Cells(NextRow,
2).Value = FileDateTime(MyFolder & FiletoList) <span style="color: #38761d;">'Write the date the cell
was last modified</span></i></div>
<div class="MsoNormal">
<i> NextRow = NextRow
+ 1 <span style="color: #38761d;">'Move to next row</span></i></div>
<div class="MsoNormal">
<i> FiletoList = Dir
<span style="color: #38761d;">'Dir returns the next Excel workbook in the folder</span><o:p></o:p></i></div>
<div class="MsoNormal">
<i>Loop</i></div>
<div class="MsoNormal">
<i><br /></i>
<i>Application.ScreenUpdating = True</i></div>
<div class="MsoNormal">
<i><br /></i>
<i>End Sub</i><o:p></o:p></div>
<h2>
See Also</h2>
<div>
<div class="txtd" id="txtd_22675485" style="margin: 0px; padding: 0px; word-wrap: break-word;">
<div style="color: #333333; margin-bottom: 0.75em; padding: 0px;">
<ul>
<li><a href="http://www.computergaga.com/blog/2013/04/loop-through-all-files-in-a-folder-using-vba/" target="_blank">Loop through all the files in a folder using Excel VBA</a></li>
<li><a href="http://www.computergaga.com/blog/2011/12/use-the-ontime-method-in-excel-vba/" target="_blank">Use the OnTime method in Excel VBA</a></li>
</ul>
</div>
</div>
</div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com2tag:blogger.com,1999:blog-68368394969303158.post-86520811734394741662013-08-01T02:45:00.000-07:002013-08-04T02:22:53.302-07:00Check for Odd Numbers in ExcelData accuracy is essential in Excel. If data entered is
incorrect then your skills with PivotTables and Filtering data become almost
irrelevant.<br />
<div>
<br />
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
In this post we look at preventing the entry of odd numbers
in a range, or just highlighting them for further interrogation.<o:p></o:p></div>
<a name='more'></a><h2>
</h2>
<h2>
Prevent Odd Numbers in a Range<o:p></o:p></h2>
<div class="MsoNormal">
To prevent the entry of odd numbers in a range we will need
Data Validation. We will also need a formula to identify the odd numbers.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The formula we will use is the MOD function. This function
returns a remainder when a number is divided by a divisor.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
For us this means that if we divide the number in the cell
we are checking by 2, and the remainder is 0 then the number is even, and if it
returns 1 then the number is odd.</div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">1. Select the
range you want to apply the Data Validation rule to.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">2. Click the <b>Data </b>tab on the Ribbon and then the
<b>Data Validation</b> button.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">3. Select <b>Custom </b>from the <b>Allow </b>drop list. We need
to use custom as we are entering a formula.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">4. In the Formula box enter <i>=MOD($A2,2)=0</i>. Replace
$A2 with the first cell of the range you are validating i.e. if you selected
column C in step 1 then use $C1 in your formula.</span></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEib4FVVlVadXCjeSdL_bv18lWpslWmrqeFyckaJNBVDARIrkNhzuX02rXA7m_L4DMYBXe-rQ0hx2s__uY2gX88Eqx1Gi8Y2WSztDqbp5nEAQZhSe-pGE9uWnSA6rV_2kqIsMf7lh3NtJSg/s1600/mod+function.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Preventing odd numbers in a range using the MOD function" border="0" height="254" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEib4FVVlVadXCjeSdL_bv18lWpslWmrqeFyckaJNBVDARIrkNhzuX02rXA7m_L4DMYBXe-rQ0hx2s__uY2gX88Eqx1Gi8Y2WSztDqbp5nEAQZhSe-pGE9uWnSA6rV_2kqIsMf7lh3NtJSg/s320/mod+function.png" title="" width="320" /></a></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">5. Click the <b>Error Alert</b> tab. We will create a
customised error message so that users know that entering odd numbers is wrong.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">6. Click in the <b>Title </b>box and enter a title for
your message box, then enter your message in the <b>Error message</b> box.</span></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhb5IHhiYKF0gty-GIAyTFszcbCjuBipzyJWSwFXwq3nMUxmZcJNuivg2yd2jMRw3_oSBV3fQLTbbixyx3Q4VcZP0jubnevX3slI4vorZdQKafpqmf44l1OoR81njnSYXBsSt9c9XJnSwQ/s1600/error+alert.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Creating an error alert for our validation rule" border="0" height="254" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhb5IHhiYKF0gty-GIAyTFszcbCjuBipzyJWSwFXwq3nMUxmZcJNuivg2yd2jMRw3_oSBV3fQLTbbixyx3Q4VcZP0jubnevX3slI4vorZdQKafpqmf44l1OoR81njnSYXBsSt9c9XJnSwQ/s320/error+alert.png" title="" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: 7pt; text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">7. Click <b>Ok </b>and your validation is set and ready to
go.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">Try entering an odd number to see your error
alert returned, then try and even number to check that it is accepted.</span></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYdnxAa1Jtx8M7RPzX6_nrFA98Fcn_b2PJn1X4zzAGPH6wauYGCKyWwfI4h_fobvw8KZBMv3eXLUUPhEEoCLH1dxzFHw_a3BeEvdmHH8HmNrUFBWUdqwkGfSXr9W9kUhomeG2QV-cAGiE/s1600/prevent+odd+numbers.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Error message explaining the users mistake" border="0" height="148" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYdnxAa1Jtx8M7RPzX6_nrFA98Fcn_b2PJn1X4zzAGPH6wauYGCKyWwfI4h_fobvw8KZBMv3eXLUUPhEEoCLH1dxzFHw_a3BeEvdmHH8HmNrUFBWUdqwkGfSXr9W9kUhomeG2QV-cAGiE/s320/prevent+odd+numbers.png" title="" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<h3>
Watch the Video</h3>
<div>
<br /></div>
<iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/R4iH-r7AJpw" width="560"></iframe>
<br />
<h2>
</h2>
<h2>
Highlight the Odd Numbers in a List<o:p></o:p></h2>
<div class="MsoNormal">
Instead of preventing the entry of odd numbers, you may wish
to highlight them. You can then work with the odd numbers by sorting and filtering.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
For this task we will combine the Conditional Formatting
tool with the ISODD function. The ISODD function will return true if the number
is<i> </i>odd.<o:p></o:p></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">1. Select the range of cells that you want to
format.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">2. Click the <b>Home </b>tab on the Ribbon and the <b>Conditional
Formatting</b> button.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">3. Select <b>New Rule</b> from the list.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">4. Select <b>Use a formula to determine which cells to
format</b>.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">5. Enter the formula <i>=ISODD($B2)</i> in the box
provided. Replace $B2 in the formula with the first cell in the range of cells
you selected in step 1.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l1 level1 lfo2; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipacvfLq7bkZjElbHtI8AfkgtYBeHHYzAEEAto1ekpV0HGo7XijFHRgYMuVsKXxbQAkN-MlLg5ApbQsjWWo6Chj2iDNzk64CXInaoS2oVCCCe1FrWSlSgm2_DJ03gCa0WR1nuPCCUf_is/s1600/isodd.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Using the ISODD function to highlight odd numbers in a list" border="0" height="309" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipacvfLq7bkZjElbHtI8AfkgtYBeHHYzAEEAto1ekpV0HGo7XijFHRgYMuVsKXxbQAkN-MlLg5ApbQsjWWo6Chj2iDNzk64CXInaoS2oVCCCe1FrWSlSgm2_DJ03gCa0WR1nuPCCUf_is/s320/isodd.png" title="" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">6. Click the <b>Format </b>button and choose the
formatting you wish to apply.</span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;">7. Click <b>Ok </b>to close down all boxes and the odd
numbers in your range should be highlighted. </span></div>
<div class="MsoNormal">
<span style="text-indent: -18pt;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgW8SLBkOrFwRg0-kJBFqXwmHBNz4K16KoafWtijYJE4RRurFhOyU7IVGmLIrky9HadbO3NPaAc37nYG1NPJKwrcVt0YVH_Kdv0ZL_IbeoRvzpdf4EN9mbG0uirrXQBaEqsffwNQHMEOTk/s1600/odd+highlighted.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Odd Numbers highlighted in a range" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgW8SLBkOrFwRg0-kJBFqXwmHBNz4K16KoafWtijYJE4RRurFhOyU7IVGmLIrky9HadbO3NPaAc37nYG1NPJKwrcVt0YVH_Kdv0ZL_IbeoRvzpdf4EN9mbG0uirrXQBaEqsffwNQHMEOTk/s1600/odd+highlighted.png" title="" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<h3>
Watch the Video</h3>
<div>
<br /></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l1 level1 lfo2; text-indent: -18.0pt;">
<o:p></o:p></div>
</div>
<br />
<br />
<h3>
Read More</h3>
<br />
<ul>
<li><a href="http://www.computergaga.com/blog/2012/03/highlight-dates-over-30-days-old/" target="_blank">Highlight dates over 30 days old</a></li>
<li><a href="http://www.computergaga.com/blog/2012/01/validate-an-email-address-in-excel/" target="_blank">Validate an email address in Excel</a></li>
<li><a href="http://www.computergaga.com/blog/2011/11/prevent-duplicates-in-excel/" target="_blank">Prevent duplicates in Excel</a></li>
</ul>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-51837270190042451662013-07-25T13:33:00.000-07:002013-07-25T13:33:26.112-07:00Use Comments for Storing Large Amounts of Text<div class="MsoNormal">
Excel was built to handle records of data that contain labels,
values and formulas. Storing large amounts of text is not its priority meaning
your spreadsheets can become long, wide and awkward.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Typically to accommodate bulk text columns such as for descriptions
and notes you will make the column wider, apply wrap text to the cells, or a
bit of both. These features can help, but another option available to you is to
store the text in a comment.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
By storing the text in a comment box it is only visible when
you want it. By not storing it in a cell your spreadsheet remains easy to
navigate and use daily.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<a name='more'></a><h2>
Storing Text in a Comment</h2>
<span style="text-indent: -18pt;">1. Select the cell you want to insert the comment
to.</span><br />
<span style="text-indent: -18pt;"><br /></span>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
2. Click the <b>Review </b>tab on the Ribbon and then <b>New
Comment</b>.<o:p></o:p><br />
<br /></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
3. Delete the username from the box if you wish,
and enter or paste the text you want to use.<br />
<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYL3RwP7eLEm6nxynGUP4as3Dlq-iVOk4FMpz9K_kpl6ODIVVHX01fa9YAzm-PbkJDsZ3xbZOO1KADFpanoLQfQUz2h82RLjFKKSqyEhGmOz_IUKbOLChqIXhPeSp6zmiocjvgkznmx-E/s1600/insert_comment.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Insert text into a comment in Excel" border="0" height="167" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYL3RwP7eLEm6nxynGUP4as3Dlq-iVOk4FMpz9K_kpl6ODIVVHX01fa9YAzm-PbkJDsZ3xbZOO1KADFpanoLQfQUz2h82RLjFKKSqyEhGmOz_IUKbOLChqIXhPeSp6zmiocjvgkznmx-E/s400/insert_comment.png" title="" width="400" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
4. Click outside of the comment box to apply it.<o:p></o:p></div>
<div class="MsoNormal">
<br />
The comment is not visible, but can be seen by hovering your
mouse over the cell containing it. A red triangle appears in the corner of the cell
indicating the presence of a comment.<o:p></o:p><br />
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAshtZ7LGb6rlAR-CZd4TnOF1Dkv2sh1QsKrFwFvvzqkDhoxNUIv2Zc5STbAubKzYENirKzX3NhuiaQhvD4PXDqdoKNQVs9BfKdECgIMvj6iG8UeCVRtH9XCFiASc3HaTaVac5H9RtC5k/s1600/triangle_for_comment.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Triangle indicating a cell comment" border="0" height="116" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAshtZ7LGb6rlAR-CZd4TnOF1Dkv2sh1QsKrFwFvvzqkDhoxNUIv2Zc5STbAubKzYENirKzX3NhuiaQhvD4PXDqdoKNQVs9BfKdECgIMvj6iG8UeCVRtH9XCFiASc3HaTaVac5H9RtC5k/s400/triangle_for_comment.png" title="" width="400" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Comments can also be used to store pictures. Pictures can
also bulk your spreadsheets up making them cumbersome to use (<a href="http://www.computergaga.com/blog/2013/07/insert-a-picture-into-a-comment/" target="_blank">Read how toinsert a picture into a comment box</a>).<o:p></o:p></div>
<br />
<div class="MsoNormal">
Comments can be viewed by using the <b>Next </b>and <b>Previous
</b>buttons in the Comments group of the Review tab. This will take you through
them one by one. The <b>Show All Comments</b> button can also be used to here to
display them permanently.<o:p></o:p></div>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com1tag:blogger.com,1999:blog-68368394969303158.post-17153031922501620272013-07-18T04:54:00.000-07:002013-07-18T04:54:20.959-07:00Netball League Table Generator in ExcelI recently helped a reader out by creating a netball league table generator in Excel. <br />
<br />
She wanted to be able to enter the results onto a sheet and have the league table automatically update to rank teams appropriately.<br />
<br />
The spreadsheet allows 5 points for a win and 3 for a draw. 1 point is earned if the losing team scores half the points of the winning team. It also provides the ability deduct penalty points from a team.<a name='more'></a><br />
<br />
Download the <a href="http://www.computergaga.com/excel/files/league_table_netball.xlsx" target="_blank">Netball league table spreadsheet</a>.<br />
<br />
The spreadsheet demonstrates some awesome Excel skills that are worth adding to your existing skillset. These include;<br />
<ul>
<li>The use of Tables for intelligent referencing within formulas, and to the make the ranges dynamic.</li>
<li>The <a href="http://www.computergaga.com/excel/2007/intermediate/more_advanced_functions/vlookup.html" target="_blank">Vlookup function</a> to retrieve data for the league table.</li>
<li>The Sumproduct function to perform multiple conditional testing.</li>
</ul>
<br />Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-48493240615656589112013-06-17T00:41:00.000-07:002014-04-22T02:05:18.878-07:00Import Data from the Web into Excel<div class="MsoNormal">
You can import data from the web into Excel for analysis and
reporting. When data is imported from the Internet, a connection is
established. This means that the link can be updated, and you will not have to
repeat this import process every time the data is needed.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
For this example, we will look at importing data from <a href="http://www.soccerstats.com/" rel="nofollow" target="_blank">www.soccerstats.com</a>. This is a favourite
site of mine. It contains up to date statistics from soccer leagues all over
the world. We will import the La Liga top scorers table.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<a name='more'></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<h2>
Import Data from the
Internet into Excel</h2>
1. Click the <b>Data</b>
tab on the Ribbon and then the <b>From Web</b>
button.<br />
<br />
2. The New Web Query dialog box appears. Enter the
address of the website containing the data you want to import into the <b>Address</b> field and click <b>Go</b>.<br />
<br />
<br />
<o:p>3. </o:p>Use the links on the webpage as normal to
navigate to the required page if necessary.<o:p></o:p><br />
<br />
4. When on the page you want, Excel will display
small black arrows inside yellow boxes by the parts of the page that you can
import (This depends how the page was structured and may not meet your needs).<o:p></o:p></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuKeqnMkHw-5Q2ukxplfpTH5kxzFbXSd2IwB0k9T8wYKaUJA5MI-5gziDBWvByLN_DxP-cCHI0sJvi00HZiJOy0iQWI03ksqiRvDL7Y72FfyvcqydQsqxJZsE83ZguTvuhI8QYtS1hb-s/s1600/import_from_web.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Import data from web query window" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuKeqnMkHw-5Q2ukxplfpTH5kxzFbXSd2IwB0k9T8wYKaUJA5MI-5gziDBWvByLN_DxP-cCHI0sJvi00HZiJOy0iQWI03ksqiRvDL7Y72FfyvcqydQsqxJZsE83ZguTvuhI8QYtS1hb-s/s400/import_from_web.jpg" height="285" title="" width="400" /></a></div>
<div class="MsoNormal">
5. Click inside the yellow box for the section of
the page you want to import. It will change to a black tick inside a green box
to signify your selection.<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtYfX32XapboawDK34FqddgtGJIrDbFj2eje-zkCj3K8CYX8j1sPy0E9tSVyqnvefGCxy1VBFvaELvByu3grc8MO-exeFryopVhTHxv8zWB5DaFrK0sJnUuC36qWBnZ0Egf-0UaT7RqkQ/s1600/select_table.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Select the section of the page for import" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtYfX32XapboawDK34FqddgtGJIrDbFj2eje-zkCj3K8CYX8j1sPy0E9tSVyqnvefGCxy1VBFvaELvByu3grc8MO-exeFryopVhTHxv8zWB5DaFrK0sJnUuC36qWBnZ0Egf-0UaT7RqkQ/s400/select_table.jpg" height="285" title="" width="400" /></a></div>
<div class="MsoNormal">
<br />
6. Click the <b>Import</b>
button.<o:p></o:p><br />
<br />
7. The Import Data dialog box is shown prompting
you for the destination of your import. Select the required cell of the
spreadsheet and click <b>Ok</b>.<o:p></o:p></div>
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCqcRY3X9adPEGQPltsymq-x7WTQr8L_iMi8HWd3Tfy3BvYgnEKgG0Dmze58yi-d0z2y1xI584-S18JOTQs7jP_haAB7BosT82qbk2K7IbM7sTvQdAGh4cOl36N7tJsBpUspqvwKkwvT8/s1600/import_data.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Select destination for imported data" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCqcRY3X9adPEGQPltsymq-x7WTQr8L_iMi8HWd3Tfy3BvYgnEKgG0Dmze58yi-d0z2y1xI584-S18JOTQs7jP_haAB7BosT82qbk2K7IbM7sTvQdAGh4cOl36N7tJsBpUspqvwKkwvT8/s1600/import_data.jpg" title="" /></a><br />
8. The imported data may take a few seconds to
appear.<br />
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjqjUMcDo2iiPguGhDaClwudXbR-HdPaZr8lk88m1peKfr6Cd2TVColv0mWdX_C8-JbUGneutbz5uFNoZtNBLHaAbRLSswOqUd8xwbVtMWLi-1QUMyEvARlOep-PTJXeMCzbKEnI0SBNU/s1600/web_data_imported.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Web data imported into Excel" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjqjUMcDo2iiPguGhDaClwudXbR-HdPaZr8lk88m1peKfr6Cd2TVColv0mWdX_C8-JbUGneutbz5uFNoZtNBLHaAbRLSswOqUd8xwbVtMWLi-1QUMyEvARlOep-PTJXeMCzbKEnI0SBNU/s400/web_data_imported.jpg" height="181" title="" width="400" /></a></div>
<br /></div>
<div class="MsoNormal">
The imported data will quite often require a little
formatting and tidying up. If this connection is updated regularly then this
can become a very repetitive task. Therefore the formatted and tidying of
imported data is a very common task for a macro (<span style="color: #5b9bd5; mso-themecolor: accent1;"><a href="http://www.computergaga.com/excel/2007/advanced/macros/record_a_macro.html" target="_blank">Find out more about macros</a></span>).<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Now that your data has been imported a connection was
created between the spreadsheet and that section of the webpage. You can manage
that connection by using the commands in the Connections group of the Data tab.<o:p></o:p><br />
<br /></div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpXpLJ8RXgCl-JiVZFtxsm1pXFIUc4GaG7nqgh7XZZ-p3li2QOCChr18KB_-nMevwtT9rx43hVamiMjx18xWRmEXTHt9n9o1rWW5aLivIKlskGkKlqzLfOQgOphYq19m8W-otfIt1ljGY/s1600/connections.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="External data connections for your spreadsheet" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpXpLJ8RXgCl-JiVZFtxsm1pXFIUc4GaG7nqgh7XZZ-p3li2QOCChr18KB_-nMevwtT9rx43hVamiMjx18xWRmEXTHt9n9o1rWW5aLivIKlskGkKlqzLfOQgOphYq19m8W-otfIt1ljGY/s1600/connections.jpg" title="" /></a></div>
</div>
<br />
<div class="MsoNormal">
The <b>Refresh</b>
button is used to update the connection or link. You will not have to import
the data again as the existing connection can just be refreshed whenever it
needs updating.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The <b>Properties</b>
button can be used to modify the properties of the connection. Popular
properties to modify include the connections name, which in the example below
looks quite unfriendly, and the refresh control options.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhk23pJ0OJK3VdrIQvwExr4BFxKBaLcjVYzI6H1c3kWplRXWDI6-M3UnU0EOTxDNxqoREHAOgnUvBn7Z5G5W9CO-wylQnHtTveLHn2Sft62-GiRVyYQf3eTSos4n5jTqSE9x5TQ2xEc9GU/s1600/external_data_properties.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Modify external data properties" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhk23pJ0OJK3VdrIQvwExr4BFxKBaLcjVYzI6H1c3kWplRXWDI6-M3UnU0EOTxDNxqoREHAOgnUvBn7Z5G5W9CO-wylQnHtTveLHn2Sft62-GiRVyYQf3eTSos4n5jTqSE9x5TQ2xEc9GU/s320/external_data_properties.jpg" height="320" title="" width="234" /></a></div>
<div class="MsoNormal">
<br /></div>
<script type="text/javascript"><!--
google_ad_client = "ca-pub-3784551161756980";
/* Blog image */
google_ad_slot = "1194262260";
google_ad_width = 250;
google_ad_height = 250;
//</script>
<script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">
</script>
<script type="text/javascript"><!--
google_ad_client = "ca-pub-3784551161756980";
/* Blog image */
google_ad_slot = "1194262260";
google_ad_width = 250;
google_ad_height = 250;
//</script>
<script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">
</script>Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-43580322359475616282013-05-02T07:56:00.001-07:002013-05-02T07:58:34.314-07:00Excel VBA: The Dir Function<br />
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
In Microsoft Excel VBA, the Dir function is
used to return the first filename from a specified directory, and list of
attributes. The filename is returned as a string. <o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
The Dir function can then be entered without
any arguments to return the next filename from that directory.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
The most common use of the Dir function is to
loop through all the files in a folder to perform an action on each one. Other
common uses include checking if a file or a directory exists, or to look for a
specific file such as the latest one.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
</div>
<h2>
<b>Dir Function Syntax</b></h2>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
When entering the Dir function into the
Visual Basic Editor the quick info list should appear and prompt for the
following.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
Dir( [<i>PathName</i>],
[<i>Attributes</i> As VbFileAttribute =
vbNormal]) As String<o:p></o:p><br />
<br />
<a name='more'></a><br /></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<b>Pathname:</b> The directory
or folder that you want to use. This can be entered as a string, or a variable
that contains one. It should include the backslash (\) at the end.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
The filename can be entered if you are
looking for a specific file. Wildcard characters can also be used to identify
types of files, such as (*.xlsx) for a workbook.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<b>Attributes:</b> A list of
attributes is shown in the table below. This is optional and a combination can
be used. Attributes can be included for a more targeted match. <o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
For example the vbDirectory attribute can be
used to specify that you want to return a directory and not a filename.<o:p></o:p></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 90.45pt;" valign="top" width="121"><div class="MsoNormal">
<b>Attribute<o:p></o:p></b></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 2.0cm;" valign="top" width="76"><div class="MsoNormal">
<b>Value<o:p></o:p></b></div>
</td>
<td style="border-left: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 120.5pt;" valign="top" width="161"><div class="MsoNormal">
<b>Description<o:p></o:p></b></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 90.45pt;" valign="top" width="121"><div class="MsoNormal">
vbNormal<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 2.0cm;" valign="top" width="76"><div class="MsoNormal">
0<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 120.5pt;" valign="top" width="161"><div class="MsoNormal">
Normal
(Default)<o:p></o:p></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 90.45pt;" valign="top" width="121"><div class="MsoNormal">
vbReadOnly<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 2.0cm;" valign="top" width="76"><div class="MsoNormal">
1<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 120.5pt;" valign="top" width="161"><div class="MsoNormal">
Read
only<o:p></o:p></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 90.45pt;" valign="top" width="121"><div class="MsoNormal">
vbHidden<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 2.0cm;" valign="top" width="76"><div class="MsoNormal">
2<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 120.5pt;" valign="top" width="161"><div class="MsoNormal">
Hidden<o:p></o:p></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 90.45pt;" valign="top" width="121"><div class="MsoNormal">
vbSystem<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 2.0cm;" valign="top" width="76"><div class="MsoNormal">
4<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 120.5pt;" valign="top" width="161"><div class="MsoNormal">
System
file<o:p></o:p></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 90.45pt;" valign="top" width="121"><div class="MsoNormal">
vbVolume<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 2.0cm;" valign="top" width="76"><div class="MsoNormal">
8<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 120.5pt;" valign="top" width="161"><div class="MsoNormal">
Volume
label<o:p></o:p></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 90.45pt;" valign="top" width="121"><div class="MsoNormal">
vbDirectory<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 2.0cm;" valign="top" width="76"><div class="MsoNormal">
16<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 120.5pt;" valign="top" width="161"><div class="MsoNormal">
Directory
or folder<o:p></o:p></div>
</td>
</tr>
<tr>
<td style="border-top: none; border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 90.45pt;" valign="top" width="121"><div class="MsoNormal">
vbAlias<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 2.0cm;" valign="top" width="76"><div class="MsoNormal">
64<o:p></o:p></div>
</td>
<td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt 0cm 5.4pt; width: 120.5pt;" valign="top" width="161"><div class="MsoNormal">
Filename
is an alias<o:p></o:p></div>
</td>
</tr>
</tbody></table>
<h2>
<b><br /></b></h2>
<h2>
<b>Examples</b></h2>
<div>
<h3>
<b>Example 1 – Loop
Through the Files in a Folder</b></h3>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;"><br /></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Sub</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> AllFiles()<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Dim</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> MyFolder <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">As String</span>
<span style="color: #00b050;">‘Path containing the files for looping<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Dim</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> MyFile <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">As String </span><span style="color: #00b050;">‘Filename obtained by Dir function<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;">MyFolder
= “C:\ExcelFiles” <span style="color: #00b050;">‘Assign directory to MyFolder
variable</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;">MyFile
= Dir(MyFolder) <span style="color: #00b050;">‘Dir gets the first file of the
folder</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #00b050; font-family: "Courier New"; font-size: 10.0pt;">‘Loop through all files until Dir cannot find anymore<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Do While</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> MyFile <>
""<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <i>The
statements you want to run on each file<o:p></o:p></i></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> MyFile = Dir <span style="color: #00b050;">‘Dir
gets the next file in the folder<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Loop<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">End Sub<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
</div>
<h3>
<b>Example 2 –
List the Files from a Folder on a Worksheet</b></h3>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;"><br /></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Sub </span><span style="font-family: "Courier New"; font-size: 10.0pt;">ListFiles()<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Dim</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> MyDirectory <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">As String </span><span style="color: #00b050;">‘Folder containing the files</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Dim</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> MyFile <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">As String </span><span style="color: #00b050;">‘The filename to enter on the worksheet<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Dim</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> NextRow <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">As Long </span><span style="color: #00b050;">‘The row for the next filename in list</span><span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;">MyDirectory
= “C:\ExcelFiles” <span style="color: #00b050;">‘Assign directory to MyDirectory
variable</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;">MyFile
= Dir(MyDirectory) <span style="color: #00b050;">‘Dir gets the first file in the
folder</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #00b050; font-family: "Courier New"; font-size: 10.0pt;">‘Find the next empty row in the list and store in NextRow
variable<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;">NextRow
= Application.CountA(Range(“A:A”)) + 1<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Do Until</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> MyFile = “”<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> Cells(NextRow, 1).value = MyFile<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> NextRow = NextRow + 1 <span style="color: #00b050;">‘Move to the next row<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> MyFile = Dir <span style="color: #00b050;">‘Dir
gets the name of next file in the folder</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Loop<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">End Sub<o:p></o:p></span></div>
<h3>
<b>Example 3 – Check
if a Files Exists</b></h3>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;"><br /></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Sub</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> FileExists()<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Dim</span> TheFolder <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">As string</span> <span style="color: #00b050;">‘Location of the file</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Dim</span> FiletoCheck <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">As String</span> <span style="color: #00b050;">‘Name of the file you want to check<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;">TheFolder
=</span> <span style="font-family: "Courier New"; font-size: 10.0pt;">“C:\ExcelFiles” <span style="color: #00b050;">‘Assign directory to TheFolder variable<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #00b050; font-family: "Courier New"; font-size: 10.0pt;">‘Capture the name of file to check for using an input box<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;">FiletoCheck
= InputBox(“Enter the name of the file you want to look for”, “Enter file
name”)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #00b050; font-family: "Courier New"; font-size: 10.0pt;">‘If FiletoCheck is an empty string then file not found<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">If</span><span style="font-family: "Courier New"; font-size: 10.0pt;"> FiletoCheck = “” <span style="color: #365f91; mso-themecolor: accent1; mso-themeshade: 191;">Then<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> Msgbox “Oh no, the file does not exist”<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">Else<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> Msgbox “Yes, the file exists.”<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">End If<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #365f91; font-family: "Courier New"; font-size: 10.0pt; mso-themecolor: accent1; mso-themeshade: 191;">End Sub<o:p></o:p></span></div>
</div>
<br />
<a href="http://ipmurray.behelit777.hop.clickbank.net/"><img src="http://www.gamingjobsonline.com/images/banner/banner250x250.jpg" /></a>
<script type="text/javascript"><!--
google_ad_client = "ca-pub-3784551161756980";
/* Blog image */
google_ad_slot = "1194262260";
google_ad_width = 250;
google_ad_height = 250;
//</script>
-->
<script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">
</script>Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com5tag:blogger.com,1999:blog-68368394969303158.post-63082477120069208752013-04-25T13:36:00.000-07:002013-04-30T07:17:16.866-07:00Create a Speedometer Chart in Excel<br />
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
Speedometer charts are used to
measure performance. As a speedometer can be found on any car dashboard they
are instantly recognisable, and will add a certain style to your Excel
dashboards.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
Excel does not provide one as a
default chart type, so to create a speedometer chart in Excel we will need a
donut chart, a pie chart and a little ingenuity.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
The Donut chart will be used
for the speedometer background and the Pie chart will be positioned on top to
create the needle.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
Being a big fan of the UFC, I thought
I would create a speedometer chart to display the percentage of victories by
UFC Welterweight champion Georges St. Pierre that have come via by stoppage (submission,
KO or TKO).<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4cgyIdfZR6B8_ZDP4WM86tycrXBHlEK5h7fwQ_8NUL30cWLEVTwZTswG8odejSr8B_YG4wp8Cf6hxBEkXiuLnEVW6zFjWsZmacHp2biAQz8JXkN2swn0PRm7L686T0VpK5aWuqGYFUQ8/s1600/speedometer_chart.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Create a speedometer chart in Excel" border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4cgyIdfZR6B8_ZDP4WM86tycrXBHlEK5h7fwQ_8NUL30cWLEVTwZTswG8odejSr8B_YG4wp8Cf6hxBEkXiuLnEVW6zFjWsZmacHp2biAQz8JXkN2swn0PRm7L686T0VpK5aWuqGYFUQ8/s400/speedometer_chart.jpg" title="" width="400" /></a></div>
<br />
<br />
<a name='more'></a><h2>
Enter the Data</h2>
</div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
The first task at hand is to
enter the data that we want to use for our chart. This data is displayed below
with formulas shown.<o:p></o:p></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmQedviKsYFCksxOyLacyn_nYjx36PjizOpbB4f7eQYAuGpydh84TU0tCLBAh79Nx3niH_cxEO0dOkvVoXodzRjsFQn1fJ63zxsgO5NEBR7ICH7PigWi-r9W7UyJJOXdMhFh3kLAzsAKo/s1600/speedometer_chart_data.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Enter the data for the speedometer chart" border="0" height="197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmQedviKsYFCksxOyLacyn_nYjx36PjizOpbB4f7eQYAuGpydh84TU0tCLBAh79Nx3niH_cxEO0dOkvVoXodzRjsFQn1fJ63zxsgO5NEBR7ICH7PigWi-r9W7UyJJOXdMhFh3kLAzsAKo/s400/speedometer_chart_data.jpg" title="" width="400" /></a></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<br />
<h2>
Create a Donut Chart</h2>
<o:p></o:p>
<br />
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->1.<span style="font-size: 7pt;">
</span><!--[endif]-->Select cells A2:A5.<o:p></o:p></div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->2.<span style="font-size: 7pt;">
</span><!--[endif]-->Click the <b>Insert</b>
tab on the Ribbon. Click the <b>Other Chart</b>s
button and select the first <b>Donut</b>
from the list.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPJUJEJ26pDL9-MS-s1aAJ6jMsPdEnTXE6cFDI1z22BO21m7ZAxYGfBPM5T9yhLhGujjrXSdULZz7oVLNGDG17qY9dYvEx4TohEmOz7hfmbLFOMg4PCZPtAxd3BeSnEgOVcS5D0Ipngho/s1600/donut_chart.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Insert a Donut chart" border="0" height="243" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPJUJEJ26pDL9-MS-s1aAJ6jMsPdEnTXE6cFDI1z22BO21m7ZAxYGfBPM5T9yhLhGujjrXSdULZz7oVLNGDG17qY9dYvEx4TohEmOz7hfmbLFOMg4PCZPtAxd3BeSnEgOVcS5D0Ipngho/s400/donut_chart.jpg" title="" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->3.<span style="font-size: 7pt;">
</span><!--[endif]-->Right mouse click on the donut and select <b>Format Data Series</b>. Enter 90 degrees as
the angle of first slice. This will position the 180 degree side of the donut
at the bottom.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->4.<span style="font-size: 7pt;">
</span><!--[endif]-->Remove the Legend on the right by selecting it and
pressing <b>Delete</b><o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->5.<span style="font-size: 7pt;">
</span><!--[endif]-->Make the bottom half of the donut disappear. Click on
the bottom slice twice to select only that slice. Then right mouse click and
select <b>Format Data Point</b>. Select <b>Fill</b> and then the <b>No</b> <b>fill</b> option.<o:p></o:p></div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->6.<span style="font-size: 7pt;">
</span><!--[endif]-->Change the colour of the other 3 slices if you wish.
The finished donut below has been formatted to red, yellow and green slices. To
do this follow the steps as before but choose a <b>Solid fill</b> for your colour.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilmqhyh7RMznnMgKvCkLcgGXehYWyEDzju318T9XsT1FcDGhfUhir_IxB8fH3zkSIaG9iYpDMMg5ut9jo3k3ZnSngV7jL5Q3gFQwEuX1B43pCM8MQVBOrK9n1MyZzntQGGc1zek55xmn8/s1600/finished_donut.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Finished Donut chart" border="0" height="252" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilmqhyh7RMznnMgKvCkLcgGXehYWyEDzju318T9XsT1FcDGhfUhir_IxB8fH3zkSIaG9iYpDMMg5ut9jo3k3ZnSngV7jL5Q3gFQwEuX1B43pCM8MQVBOrK9n1MyZzntQGGc1zek55xmn8/s400/finished_donut.jpg" title="" width="400" /></a></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<h2>
Create a Pie Chart</h2>
<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
The pie chart will be created
to form the needle of the speedometer chart.<o:p></o:p></div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<!--[if !supportLists]-->1.<span style="font-size: 7pt;">
</span><!--[endif]-->Select cells B2:B5.<o:p></o:p></div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<!--[if !supportLists]-->2.<span style="font-size: 7pt;">
</span><!--[endif]-->Click the <b>Insert</b>
tab on the Ribbon. Click <b>Pie</b> and
select the first chart in the list.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_d-1zuwvITkTEwR9lmCd7UPBj0UaVLqVWaPcjXLiLOC5fDimxGlAMDHF_a75Nz7UBJsFp_cTBtwqdPJGQIXa3o4hhDgJMLDhegVwk3Ie7w5LuSNZgoQABFXxoDqMxew4VOWvk148A2yw/s1600/pie.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Inserted Pie chart" border="0" height="243" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_d-1zuwvITkTEwR9lmCd7UPBj0UaVLqVWaPcjXLiLOC5fDimxGlAMDHF_a75Nz7UBJsFp_cTBtwqdPJGQIXa3o4hhDgJMLDhegVwk3Ie7w5LuSNZgoQABFXxoDqMxew4VOWvk148A2yw/s400/pie.jpg" title="" width="400" /></a></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<!--[if !supportLists]-->3.<span style="font-size: 7pt;">
</span><!--[endif]-->As before, right mouse click on the pie chart and
select <b>Format Data Series</b>. Change
the angle of the first slice to 90 degrees. Again this will position the 180
degree slice at the bottom.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<!--[if !supportLists]-->4.<span style="font-size: 7pt;">
</span><!--[endif]-->Remove the legend from the chart.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<!--[if !supportLists]-->5.<span style="font-size: 7pt;">
</span><!--[endif]-->Make all the pie slices except for the needle
transparent. Do this by right clicking on the slice and selecting <b>Format Data Point</b>. Select <b>Fill</b> and then <b>No fill</b>.<o:p></o:p></div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -18.0pt;">
<!--[if !supportLists]-->6.<span style="font-size: 7pt;">
</span><!--[endif]-->Repeat this for the Chart Area. This will make the
chart background transparent. The needle should be correctly positioned on top
of the donut.<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWsFbSKLD5g625G6oTrOjaixe-HUari4EBr74Y0wYZ1SyW-1QRDzEoDGKGPiSYvH6Gv3YASpTu5H7Mvw-6BkO1gMRTmCFZe8uHaTAwmJz_Rp2pe9x2rx7hM2LSju1gcz3FV23XNUdEwkI/s1600/needle.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Needle displayed over the Donut chart" border="0" height="215" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWsFbSKLD5g625G6oTrOjaixe-HUari4EBr74Y0wYZ1SyW-1QRDzEoDGKGPiSYvH6Gv3YASpTu5H7Mvw-6BkO1gMRTmCFZe8uHaTAwmJz_Rp2pe9x2rx7hM2LSju1gcz3FV23XNUdEwkI/s400/needle.jpg" title="" width="400" /></a></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<br />
<h2>
Add Text Boxes for Labels</h2>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
The labels on the chart were
created by inserting text boxes.<o:p></o:p></div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->1.<span style="font-size: 7pt;">
</span><!--[endif]-->Click the <b>Insert</b>
tab on the Ribbon and then the <b>Text Box</b>
button.<o:p></o:p></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->2.<span style="font-size: 7pt;">
</span><!--[endif]-->Click where you would like to position the label.<o:p></o:p></div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]-->3.<span style="font-size: 7pt;">
</span><!--[endif]-->Enter the required text and then format as necessary.<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
To save time in the future, you
can save the 2 charts as chart templates. Next time you need a speedometer
chart, the task would be as easy as selecting the data and inserting both
charts (Find out more on <a href="http://www.computergaga.com/excel/2007/intermediate/charts/chart_templates.html" target="_blank">how to use chart templates</a>).<o:p></o:p></div>
</div>
<div class="MsoNormal" style="margin-bottom: 11.0pt;">
<o:p></o:p></div>
<a href="http://ipmurray.paiddraw.hop.clickbank.net/">
<img border="0" src="http://dxt8z9w4f93hl.cloudfront.net/creatives/250x250_ani_2.gif" /></a>
<script type="text/javascript"><!--
google_ad_client = "ca-pub-3784551161756980";
/* Blog image */
google_ad_slot = "1194262260";
google_ad_width = 250;
google_ad_height = 250;
//</script>
-->
<script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">
</script> </div>
<h2>
Watch the Video</h2>
<iframe allowfullscreen="" frameborder="0" height="315" src="http://www.youtube.com/embed/UtEn2VlGU9A" width="560"></iframe>Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-55093606379956357922013-04-17T05:54:00.000-07:002013-04-18T00:00:14.707-07:00Add a Running Total Column to your Spreadsheet<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">A running total column can be added to a
spreadsheet to maintain a cumulative total for a series of values. This could
be used to track a list of inventory, or a list of account transactions.</span><br />
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<span style="font-family: 'Courier New'; font-size: 10pt;">In the example below I want
to add a running total of column B into column C.</span><br />
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDfZ0ZERy5D-M5gXN8IFY5_WS_WULKohWsUdzVuISX5l4tTtN8MC4m2IooLksinASdYIkEnl0EXrgzR0hIYlro1D3_eO3hGsm0IDLZHEezAO8CMA_vLu5Z6nGHjzGghem2-jcu87iuWNc/s1600/example.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="List of values" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDfZ0ZERy5D-M5gXN8IFY5_WS_WULKohWsUdzVuISX5l4tTtN8MC4m2IooLksinASdYIkEnl0EXrgzR0hIYlro1D3_eO3hGsm0IDLZHEezAO8CMA_vLu5Z6nGHjzGghem2-jcu87iuWNc/s1600/example.jpg" title="" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<a name='more'></a><br />
<br />
<h2>
Add a Running Total Column</h2>
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<span style="font-family: 'Courier New'; font-size: 10pt;">Click in cell C2 and enter
the formula =SUM($B$2:$B2).</span><br />
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">This formula uses an absolute reference for the
beginning of the range and then a relative reference to the row number at the
end of the range. This will ensure that the number of rows added will change as
the formula is copied down (</span><span style="font-family: 'Courier New'; font-size: 10pt;"><a href="http://www.computergaga.com/excel/2007/foundation/formulas/rel_abs_references.html" target="_blank">Read more about relative and absolute cell references</a>).</span><span style="font-family: 'Courier New'; font-size: 10pt;"> </span><br />
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<span style="font-family: 'Courier New'; font-size: 10pt;">As more values are added to
column B, this formula can be copied down to keep the running total going.</span><br />
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">After copying the formula down you may encounter
a warning message like the one shown below.</span><br />
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghdKpcHrWv7UQoOVdJdE2-85YoOur3bt4W9MPtc7fpKpuw-NJNw2qhKFyoyzLnCJTLj3AfshldnMeu6KwM4jB_fIeu2qPC3IWMcTTwUH-TjUygU4j_-yaCiPtBnjCUi-ZHXI0FYvrxHXo/s1600/additional+numbers.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Warning about formula with additional values" border="0" height="138" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghdKpcHrWv7UQoOVdJdE2-85YoOur3bt4W9MPtc7fpKpuw-NJNw2qhKFyoyzLnCJTLj3AfshldnMeu6KwM4jB_fIeu2qPC3IWMcTTwUH-TjUygU4j_-yaCiPtBnjCUi-ZHXI0FYvrxHXo/s320/additional+numbers.jpg" title="" width="320" /></a></div>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">This message warns us that the formula does not
include all the values from column B and that we may have made a mistake. Do
not worry if this message does not appear. It can be disabled which we will
look at now.</span><br />
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<br />
<h2>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">Modify the Error Checking Rules</span></h2>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">Excel will display an alert if it believes you
have made a mistake in a formula. It has a variety of alerts to handle common
formula errors. These alerts can be disabled and enabled at your discretion.</span><br />
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">1. Click the <b>File </b>tab
on the Ribbon and select <b>Options</b></span><br />
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">2. Select the <b>Formulas </b>category on the left</span><br />
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">3. </span><span style="font-family: 'Courier New'; font-size: 10pt;">Uncheck the <b>Formulas which omit
cells in a region</b> checkbox</span><br />
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<span style="font-family: 'Courier New'; font-size: 10pt;">4. </span><span style="font-family: 'Courier New'; font-size: 10pt;">You may recognise some of the other error
checking options available. Customise as required.</span><br />
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-DD_kR2H9ZCz5SfeOdo-4vgiWE5S3SaUhjIosd-Q8wekY48ymmcxt-_BrePb8eU3cwJ4bBM1jGA13f7zdn8tD8X4R2ebhzsL9zw_wNq4p8e2FP7clHeyGz3Lc6oNeQ9jDNoufHEbeFZc/s1600/options.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Formula error checking options" border="0" height="260" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-DD_kR2H9ZCz5SfeOdo-4vgiWE5S3SaUhjIosd-Q8wekY48ymmcxt-_BrePb8eU3cwJ4bBM1jGA13f7zdn8tD8X4R2ebhzsL9zw_wNq4p8e2FP7clHeyGz3Lc6oNeQ9jDNoufHEbeFZc/s320/options.jpg" title="" width="320" /></a></div>
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<span style="font-family: 'Courier New'; font-size: 10pt;"><br /></span>
<span style="font-family: 'Courier New'; font-size: 10pt;">5. </span><span style="font-family: 'Courier New'; font-size: 10pt;">Click <b>Ok </b>to save
and close the Excel Options dialog box</span><br />
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-ansi-language: EN-GB; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><br /></span>
<a href="http://ipmurray.behelit777.hop.clickbank.net/"><img src="http://www.gamingjobsonline.com/images/banner/banner250x250.jpg" /></a><script type="text/javascript"><!--
google_ad_client = "ca-pub-3784551161756980";
/* Blog image */
google_ad_slot = "1194262260";
google_ad_width = 250;
google_ad_height = 250;
//</script>
-->
<script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">
</script>
<br />
<br />
<h2>
Watch the Video</h2>
<iframe allowfullscreen="" frameborder="0" height="315" src="http://www.youtube.com/embed/2OIF03wVAGk" width="560"></iframe>Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com1tag:blogger.com,1999:blog-68368394969303158.post-34235749679835856152013-04-16T15:04:00.000-07:002013-04-22T01:35:00.116-07:00Compare Two Lists to Highlight Missing Items<br />
<div class="MsoNormal" style="line-height: 150%; margin-bottom: 11.0pt;">
<span style="font-family: "Arial","sans-serif";">It is a common task in Excel to have
to compare two lists and highlight those items that appear in one list and not
on the other. <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: 11.0pt;">
<span style="font-family: "Arial","sans-serif";">To do this we can use the Vlookup
function within the Conditional Formatting tool. The Vlookup function can be
used to lookup an item from one list in the other. The Conditional Formatting
tool can then be used to highlight the missing items.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: 11.0pt;">
<span style="font-family: "Arial","sans-serif";"></span></div>
<a name='more'></a><br />
<div class="MsoNormal" style="line-height: 150%; margin-bottom: 11.0pt;">
<span style="font-family: "Arial","sans-serif";">The example below shows a list of members. We want to check this list against the same list (minus 2 records) on another sheet.</span></div>
<br />
<br />
<div class="MsoNormal" style="margin-bottom: 11pt;">
<div class="separator" style="clear: both; line-height: 150%; text-align: center;">
</div>
<div class="separator" style="clear: both; line-height: 150%; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzClMSLShhFHBQAFQakDmBlJWjvTqpck_f59GopMoU4JiqRavybHyQntme81_jSoYLKOEyaL3lTz67KM99dpg3e1N_DlIxuSWCWW1R1fEy2O7G4aM7U3CuRIldEtZP2X6O8fpxWh43P4M/s1600/member_list.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzClMSLShhFHBQAFQakDmBlJWjvTqpck_f59GopMoU4JiqRavybHyQntme81_jSoYLKOEyaL3lTz67KM99dpg3e1N_DlIxuSWCWW1R1fEy2O7G4aM7U3CuRIldEtZP2X6O8fpxWh43P4M/s1600/member_list.jpg" /></a></div>
<div style="line-height: 150%;">
<br /></div>
<div style="line-height: 150%;">
<span style="font-family: Arial, sans-serif;">1. Select the range of cells you want to format. In the example above this would be A2:D21.</span></div>
<div style="line-height: 150%;">
<span style="font-family: Arial, sans-serif;"><br /></span></div>
<div style="line-height: 150%;">
<span style="font-family: Arial, sans-serif;">2. Click the </span><b style="font-family: Arial, sans-serif;">Conditional Formatting</b><span style="font-family: Arial, sans-serif;"> button on
the </span><b style="font-family: Arial, sans-serif;">Home</b><span style="font-family: Arial, sans-serif;"> tab of the Ribbon</span></div>
<div style="line-height: 150%;">
<span style="font-family: Arial, sans-serif;"><br /></span></div>
<div style="line-height: 150%;">
<span style="font-family: Arial, sans-serif;">3. Select
</span><b style="font-family: Arial, sans-serif;">New Rule</b><span style="font-family: Arial, sans-serif;"> from the menu</span></div>
<div style="line-height: 150%;">
<span style="font-family: "Arial","sans-serif";"><br /></span></div>
<span style="font-family: "Arial","sans-serif"; line-height: 150%;">4. Click
<b>Use a formula to determine which cells
to format</b></span><br />
<span style="font-family: Arial, sans-serif;"><br /></span>
<span style="font-family: Arial, sans-serif;"><span style="line-height: 24px;">5.<b> </b></span>Enter
the formula below into the field provided</span><br />
<span style="font-family: Arial, sans-serif;"><br /></span>
<span style="font-family: Arial, sans-serif;">The Vlookup function is looking for a match in column A. If there is a match then the ID is returned. If not the error #N/A is returned. The ISNA function will return true if a match is not found, meaning the cells will be formatted (</span><span style="font-family: Arial, sans-serif;">You can also </span><a href="http://www.computergaga.com/blog/2013/04/compare-two-lists-to-highlight-matched-items/" style="font-family: Arial, sans-serif;" target="_blank">compare two lists for matched items</a><span style="font-family: Arial, sans-serif;"> using the Match function)</span><span style="font-family: Arial, sans-serif;">.</span></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 150%; margin-bottom: 11.0pt; mso-add-space: auto;">
<span style="font-family: "Arial","sans-serif";">=ISNA(VLOOKUP($A2,'Members
2'!$A$2:$A$19,1,FALSE))</span><br />
<span style="font-family: "Arial","sans-serif";"><br /></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: 11.0pt;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj496ykefp8GTWEObioqHXHla_sAW042cD_8Kz24bJBiQBlKkoV9txkI9b-TwXcrw6ICXCpUwZvXTitrKh9sU7QVhAMS13UszO1llrpD9R5wQPnSsYCW3qssyH-0JQbhOT-Ni-Z-an1RrE/s1600/vlookupinCF.jpg" imageanchor="1"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj496ykefp8GTWEObioqHXHla_sAW042cD_8Kz24bJBiQBlKkoV9txkI9b-TwXcrw6ICXCpUwZvXTitrKh9sU7QVhAMS13UszO1llrpD9R5wQPnSsYCW3qssyH-0JQbhOT-Ni-Z-an1RrE/s1600/vlookupinCF.jpg" width="288" /></a></div>
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="line-height: 150%; margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<span style="font-family: "Arial","sans-serif";">6. Click
the <b>Format</b> button and select the
formatting you wish to apply<o:p></o:p></span><br />
<span style="font-family: "Arial","sans-serif";"><br /></span></div>
<div class="MsoListParagraphCxSpLast" style="line-height: 150%; margin-bottom: 11.0pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<span style="font-family: "Arial","sans-serif";">7. Click
<b>Ok</b><o:p></o:p></span><br />
<span style="font-family: "Arial","sans-serif";"><b><br /></b></span></div>
<div class="MsoNormal" style="line-height: 150%; margin-bottom: 11.0pt;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjT-8SIXUW985-Wi0RyIpPsmh7kwbsAqgWtdmXk3cuHvKDKJqRA-mwqn9NH8VsJCIo5hXWNmbAsrd2qWaZdjR_XCOikclM1X3Cz3xYadusU2FStt6E-metCrOfxY2VQXdypvo_RAeQEE4c/s1600/highlight_missing_items.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjT-8SIXUW985-Wi0RyIpPsmh7kwbsAqgWtdmXk3cuHvKDKJqRA-mwqn9NH8VsJCIo5hXWNmbAsrd2qWaZdjR_XCOikclM1X3Cz3xYadusU2FStt6E-metCrOfxY2VQXdypvo_RAeQEE4c/s320/highlight_missing_items.jpg" width="280" /></a></div>
<br />
<a href="http://ipmurray.behelit777.hop.clickbank.net/"><img src="http://www.gamingjobsonline.com/images/banner/banner468x60.jpg" /></a>
<br />
<br />
<h3>
Related Tutorials</h3>
<br />
<a href="http://www.computergaga.com/blog/2013/03/essential-conditional-formatting-tricks/" target="_blank">Great Conditional Formatting tricks</a><br />
<br />
<a href="http://www.computergaga.com/excel/2007/intermediate/more_advanced_functions/vlookup.html" target="_blank">Learn more about the Vlookup function</a><br />
<br />
<h3>
Watch the Video</h3>
<br />
<iframe allowfullscreen="" frameborder="0" height="315" src="http://www.youtube.com/embed/_suKZLHzkj4" width="560"></iframe></div>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com0tag:blogger.com,1999:blog-68368394969303158.post-80251049202843904442013-03-15T00:13:00.000-07:002013-03-15T00:18:27.510-07:00Assign Resources to tasks - MS Project 2010<span style="font-family: 'Courier New'; line-height: 115%;">Once resources have been set up in a
project, they can then be assigned to work. This process can differ slightly
for work, material and cost resources.</span><br />
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;">There are many ways to assign resources
to tasks, but the Assign Resources dialog box is probably the most efficient.
The method used though is really down to personal preference.<o:p></o:p></span></div>
<div class="MsoNormal">
<b><span style="font-family: 'Courier New'; line-height: 115%;"><br /></span></b></div>
<h3>
<span style="font-family: 'Courier New'; line-height: 115%;">Assigning resources to tasks</span></h3>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: 12.0pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<ol>
<li><span style="font-family: 'Courier New'; line-height: 115%;">From the Gantt Chart, select a task in the task list.</span></li>
<li><span style="font-family: 'Courier New'; line-height: 115%;">Click the </span><b style="font-family: 'Courier New'; line-height: 115%;">Resource</b><span style="font-family: 'Courier New'; line-height: 115%;"> tab
on the Ribbon and then click the </span><b style="font-family: 'Courier New'; line-height: 115%;">Assign
Resources</b><span style="font-family: 'Courier New'; line-height: 115%;"> button in the Assignments group.<a name='more'></a></span></li>
</ol>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgX0KFhhF3rCWAXaCw6ziUdzzwnmiTbVzFWGS_FfPQdbsnBV5HOPG0POk8ejpSBMrLXwJ8CmIdEsuTHBeWhgX6NlyX8QITmNpsx2nVvhXCHLA9pizFlcrBf1Jgk0k04EiKvZXXo1stU71I/s1600/assign_resources.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Assign resources dialog box" border="0" height="274" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgX0KFhhF3rCWAXaCw6ziUdzzwnmiTbVzFWGS_FfPQdbsnBV5HOPG0POk8ejpSBMrLXwJ8CmIdEsuTHBeWhgX6NlyX8QITmNpsx2nVvhXCHLA9pizFlcrBf1Jgk0k04EiKvZXXo1stU71I/s320/assign_resources.jpg" title="" width="320" /></a></div>
<br />
<ol start="3">
<li><span style="font-family: 'Courier New'; line-height: 115%;">Select the resource you want to assign and click the </span><b style="font-family: 'Courier New'; line-height: 115%;">Assign</b><span style="font-family: 'Courier New'; line-height: 115%;"> button.</span></li>
</ol>
<span style="font-family: 'Courier New'; line-height: 115%;">To assign multiple resources, hold down the </span><b style="font-family: 'Courier New'; line-height: 115%;">Ctrl</b><span style="font-family: 'Courier New'; line-height: 115%;"> key and select the required resources.</span><br />
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span>
<span style="font-family: 'Courier New'; line-height: 115%;">Specify the number of work units you need for the task. A work
resource will default to 100%. If this resource is working on two tasks
concurrently, the units can be changed to 50%.</span><br />
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span>
<span style="font-family: 'Courier New'; line-height: 115%;">You also need to specify how many units you require for a material
resource when assigning, and the costs of a cost resource.</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxVniG3k2lD8qgteVcpsKL7hDx-XwfzkLZWD3X9ySoFmfGYxtD5_vN2a1TJ1dLbov9m80g9jzVFAUf37f6Y24-FbOiY_CO5lYWK6UWfx6Nui97sV1OdezyfJ-FRmt8K7qYdMU0Z-fEvmA/s1600/assigned_resources.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Work and cost resources assigned to a task" border="0" height="274" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxVniG3k2lD8qgteVcpsKL7hDx-XwfzkLZWD3X9ySoFmfGYxtD5_vN2a1TJ1dLbov9m80g9jzVFAUf37f6Y24-FbOiY_CO5lYWK6UWfx6Nui97sV1OdezyfJ-FRmt8K7qYdMU0Z-fEvmA/s320/assigned_resources.jpg" title="" width="320" /></a></div>
<ol start="4">
<li><span style="font-family: 'Courier New'; line-height: 115%;">Select another task from the task list and repeat steps 3.</span></li>
<li><span style="font-family: 'Courier New'; line-height: 115%;">Click the </span><b style="font-family: 'Courier New'; line-height: 115%;">Close</b><span style="font-family: 'Courier New'; line-height: 115%;"> button
when you have finished assigning.</span></li>
</ol>
</div>
<h3>
<span style="font-family: 'Courier New'; line-height: 115%;">Assigning Multiple Individuals</span></h3>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span>
<span style="font-family: 'Courier New'; line-height: 115%;">You will need to be careful when
assigning multiple work resources to a task. Multiple individuals would need to
be assigned at the same time (the initial assignment). If an individual is
assigned to a task, and then another individual is assigned to a task in a
second assignment, Project will perform some calculations which may seem a
little strange.</span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;">The changes MS Project will make depend
on the <a href="http://almurray.hubpages.com/hub/MS-Project-Task-Type-and-Effort-Driven-Scheduling" target="_blank">effort driven scheduling and tasktype settings</a> for that task.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;">In MS Project 2010 the default action is
the increase the total work of the task and keep duration constant. In older
versions the default action was to reduce the duration of the task. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;">Any changes made to a tasks duration,
total work or an individual’s work units after the initial assignment will
trigger this calculation.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;">The image below shows this happening to
a task and the icon that appears allowing me to change the behaviour and reduce
the duration of a task, or reduce the hours the resources work per day instead.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimkmSnlIo9Xn8IpD1yHqbFgBsvgz63i61cMb2WADZIcc2fCuv8jl2zTDqWtPDvBZTUxJeavVVUkACg18849jS4vwEyH_75QFoBQcda0Th0Fem6OlaJA1SCKGAvLI6UbElTxGdIamBe_zg/s1600/increase_total_work.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Increase in work due to assignment of more resources" border="0" height="110" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimkmSnlIo9Xn8IpD1yHqbFgBsvgz63i61cMb2WADZIcc2fCuv8jl2zTDqWtPDvBZTUxJeavVVUkACg18849jS4vwEyH_75QFoBQcda0Th0Fem6OlaJA1SCKGAvLI6UbElTxGdIamBe_zg/s400/increase_total_work.jpg" title="" width="400" /></a></div>
<br /></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;">To avoid this issue remember to assign
multiple resources at the same time, or change the default behaviour of that
task in advance by editing the effort driven scheduling and task type settings.</span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span>
<br />
<h3>
<span style="font-family: 'Courier New'; line-height: 115%;">Watch the Video</span></h3>
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen='allowfullscreen' webkitallowfullscreen='webkitallowfullscreen' mozallowfullscreen='mozallowfullscreen' width='320' height='266' src='https://www.youtube.com/embed/r5y99HhZyEo?feature=player_embedded' frameborder='0'></iframe></div>
<span style="font-family: 'Courier New'; line-height: 115%;"><br /></span></div>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com1tag:blogger.com,1999:blog-68368394969303158.post-44052793156201118362013-03-08T00:16:00.000-08:002013-04-04T00:23:08.374-07:00Sum a Dynamic RangeA common issue for Excel users is the ability to sum a range
of cells that is constantly changing in height. As new rows are added to the
data set, you require the sum formula to include these in its calculation.<br />
<br />
<div class="MsoNormal">
Creating this dynamic range is an awesome skill that can
then be applied to charts, PivotTables and other formulas. It ensures that these
Excel features automatically adjust as new rows are added, or even removed from
the data set.<br />
<br /></div>
<div class="MsoNormal">
Let’s look at a couple of solutions to this problem.<br />
<a name='more'></a><br />
<br /></div>
<div class="MsoNormal">
<h2>
Use OFFSET in the SUM
Formula</h2>
</div>
<div class="MsoNormal">
The OFFSET function can be used in Excel to return a
reference based on dynamic criteria. It is one of the <a href="http://www.computergaga.com/blog/2013/04/3-special-excel-functions-that-will-amaze-you/" target="_blank">great Excel functions</a> that is worth knowing. By nesting it inside of the SUM function
it will pass the reference to the formula.<br />
<br /></div>
<div class="MsoNormal">
The OFFSET function needs to be told a starting reference,
how many rows and columns to move, and also how high and wide the range of
cells to return is. Armed with this information it can calculate the size of
the range for you. <br />
<br /></div>
<div class="MsoNormal">
This information is entered as below;<br />
<br /></div>
<div class="MsoNormal">
=OFFSET(reference, rows, cols, [height], [width])<br />
<br /></div>
<div class="MsoNormal">
For example, we have a list of orders with the order amount
stored in column G. We want to total the range from G2 to whatever the last
order in that column is.<br />
<br /></div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn3URoM_74teUkWWYP_7xe_nbM-wmT5sho4dnhyphenhyphenE0nkK7KYSidePHFaRYC3TMI1amuAVXuTCtUW-vRGTTJVTdlBn8MA3Suj1YCVlXk8n40iUhfX49-4pQawH41abODF3hzfYBO7jpfnsA/s1600/dynamic_sum.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="List of orders to sum" border="0" height="95" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn3URoM_74teUkWWYP_7xe_nbM-wmT5sho4dnhyphenhyphenE0nkK7KYSidePHFaRYC3TMI1amuAVXuTCtUW-vRGTTJVTdlBn8MA3Suj1YCVlXk8n40iUhfX49-4pQawH41abODF3hzfYBO7jpfnsA/s400/dynamic_sum.JPG" title="" width="400" /></a></div>
<br /></div>
<div class="MsoNormal">
The formula below shows the OFFSET function returning the
range of cells for SUM. The COUNTA function has been used to find how many rows
high the list of data is. This creates the dynamic nature of the range. <br />
<br /></div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEK1cYYVcUDgf9Snk-MRfIr9tevKSv34AO0R-cmcOw7v87CN9I_AwyGeoWNHlYcJEpcmGae8cBM3-SFbHrQp8AjsduAJokg6aaUIkm7Dn9D0Gdw-OUzlR7S76po5hZ8Kbmx3Hs6wUXk2g/s1600/offset_in_sum.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="OFFSET function nested with SUM formula" border="0" height="131" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEK1cYYVcUDgf9Snk-MRfIr9tevKSv34AO0R-cmcOw7v87CN9I_AwyGeoWNHlYcJEpcmGae8cBM3-SFbHrQp8AjsduAJokg6aaUIkm7Dn9D0Gdw-OUzlR7S76po5hZ8Kbmx3Hs6wUXk2g/s400/offset_in_sum.JPG" title="" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
</div>
<div class="MsoNormal">
<h2>
Create a Dynamic Range
Name</h2>
</div>
<div class="MsoNormal">
Nesting the OFFSET function inside the SUM formula is great.
Another alternative though is to <a href="http://www.computergaga.com/blog/2011/11/make-a-named-range-grow-automatically/" target="_blank">create a dynamic range name</a> which you can then
use in the SUM.<br />
<br /></div>
<div class="MsoNormal">
This defined name can then also be used in other formulas
without the need to rewrite the OFFSET function. These names also make your
formulas more meaningful and easier to read for users of the spreadsheet.<br />
<br /></div>
<div class="MsoNormal">
<ol>
<li>Click the <b>Formulas</b>
tab on the Ribbon.</li>
<li>Click the <b>Define Name</b>
button.</li>
<li>Enter a name for the defined name. This cannot includes
spaces, begin with a number or be a cell reference.</li>
<li>In the <b>Refers to:</b>
field enter the formula below.</li>
</ol>
=OFFSET($G$2,0,0,COUNTA($G:$G),1)
<br />
<ol start="5">
<li>Click <b>Ok</b>.</li>
</ol>
</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<iframe allowfullscreen="" frameborder="0" height="315" src="http://www.youtube.com/embed/VZaySAQs-Wg" width="420"></iframe></div>
Anonymoushttp://www.blogger.com/profile/17928183855719776190noreply@blogger.com1