tag:blogger.com,1999:blog-315192712024-03-05T20:05:55.071+00:00JJSurf IT Training BlogA collection of all things helpful for MS Office applications users, from a wandering IT Trainer.
www.jjsurf.co.ukJJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.comBlogger165125tag:blogger.com,1999:blog-31519271.post-83498687052706325542015-04-24T20:37:00.002+01:002015-04-24T20:37:44.990+01:00it's been (ahem) a while ....It's been ahem a while, I am now full time with Activia training so I don't have chance to publish anything on my Blogsite, more is the pity. anyway go visit us on <a href="http://www.activia.co.uk/">www.activia.co.uk</a><br />
<br />
Jamie JJJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-83263154707851569072013-06-28T21:50:00.001+01:002013-06-28T21:50:25.661+01:00Shortcuts to copy a filtered or sub totalled selection of dataOnce you have setup a Subtotal sheet you are ready to capture the data you are looking at using copy but it copies ALL the data not just the results from your subtotal.<br />
<br />
In Excel 2007 and 2010 its hard to find a way to copy visible data only<br />
<br />
Use the QAT menu dropdown to customise the menu and find the command called SELECT Visible Cells, add it to your toolbar.<br />
<br />
Or use the shortcut below:<br />
<br />
ALT +; to select the data then CTRL + C then go to Sheet 2 then CTRL + V to paste.JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-82622321920118102572012-07-26T23:06:00.001+01:002012-07-26T23:06:06.935+01:00Blogging from Windows Live Writer<p>Using Live writer to create a blog post.</p> <p> </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtpsBjgLtEksoUeHLXzWvRXAsJdIuC9nWqMdFn8iJ97OG88bS4GcAzWtOVJMJBHQToIBJTqKi-G-MXbcuswPQe-f4Tmtu0WJu7GyeZWpiseNzVsrsjqZ51BbJ1eFlmGzMi3Zs5/s1600-h/yodadog%25255B2%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="yodadog" border="0" alt="yodadog" src="http://lh6.ggpht.com/-Ewi2V6JTLGg/UBG_SZ2P80I/AAAAAAAAAYE/tAXgD_AwU20/yodadog_thumb.jpg?imgmax=800" width="244" height="240"></a></p> <p>Adding pictures is a breeze !<img style="border-bottom-style: none; border-left-style: none; border-top-style: none; border-right-style: none" class="wlEmoticon wlEmoticon-thumbsup" alt="Thumbs up" src="http://lh6.ggpht.com/-TqvhZce3BEM/UBG_TOQMF7I/AAAAAAAAAYQ/IqmY81P_E3s/wlEmoticon-thumbsup%25255B2%25255D.png?imgmax=800"></p> JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-53810967766101124522012-07-26T21:10:00.000+01:002012-07-26T21:10:19.605+01:00Office 365 / 13 is here for preview install<a href="http://www.microsoft.com/office/preview/en">http://www.microsoft.com/office/preview/en</a><br />
<br />
Based on Office 2010 but Cloud integrated its Microsoft's new toy for users.<br />
<br />
Install is quick and easy so go for it and see what it can do for you !!<br />
<br />
<strong>J</strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-36802999034293650112012-07-26T21:07:00.003+01:002012-07-26T21:07:34.959+01:00Windows 8 Release Preview is here to download<a href="http://windows.microsoft.com/en-US/windows-8/download/">http://windows.microsoft.com/en-US/windows-8/download/</a><br />
<br />
GULP over 3GB for the 64 bit version, I hope you have fast broadband, I get Infinity on the 17th Aug.<br />
<br />JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-33155184020471022132012-07-26T21:00:00.001+01:002012-07-26T21:00:40.457+01:00Experts Exchange<a href="http://www.experts-exchange.com/">http://www.experts-exchange.com/</a><br />
<br />
Have a look at this useful website , experts exchange ideas and post solutions.JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-29144802178291963142012-01-20T16:10:00.002+00:002012-01-20T16:10:32.327+00:00wow 10,273 page views, thanks everyone !!<br />
<br />
Jamie<br />
<br />JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-40875210474692665832012-01-20T16:02:00.001+00:002012-01-20T16:02:48.700+00:00Catch me on LinkedInHi folks,<br />
You can keep up with me on LinkedIn also follow the link below:<br />
<br />
<a href="http://uk.linkedin.com/in/jjsurf">http://uk.linkedin.com/in/jjsurf</a>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-25686687185306885762011-11-18T12:34:00.001+00:002011-11-18T12:35:09.653+00:00wikiversity .. a place to learn for free and its fun<a href="http://en.wikiversity.org/wiki/Introduction_to_Programming/About_Programming">http://en.wikiversity.org/wiki/Introduction_to_Programming/About_Programming</a><br />
<br />
this link takes you to page 1 of the programming course on wikiversityJJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-54543709358148742452011-11-18T12:30:00.001+00:002011-11-18T12:31:06.342+00:00That pipe key defined<a href="http://en.wikipedia.org/wiki/Vertical_bar">http://en.wikipedia.org/wiki/Vertical_bar</a><br />
<br />
Rarely used but much loved the pipe key, I use it in Excel to reveal formulas with CTRL + `<br />
<br />
press again to reveal data.<br />
<br />
thanks<br />
<strong>J</strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-39565301521349485482011-11-18T12:27:00.001+00:002011-11-18T12:28:33.654+00:00donate for wiki<a href="https://wikimediafoundation.org/w/index.php?title=L11_1114_WMUK_Jimmy/GB&utm_source=B11_1114_Foundation_WMUK_Jimmy&utm_medium=sitenotice&utm_campaign=C11_1114_WMUK&language=en&uselang=en&country=GB&referrer=http%3A%2F%2Fen.wikipedia.org%2Fw%2Findex.php%3Ftitle%3DSpecial%253ASearch%26search%3Dpipe%2Bkey">https://wikimediafoundation.org/w/index.php?title=L11_1114_WMUK_Jimmy/GB&utm_source=B11_1114_Foundation_WMUK_Jimmy&utm_medium=sitenotice&utm_campaign=C11_1114_WMUK&language=en&uselang=en&country=GB&referrer=http%3A%2F%2Fen.wikipedia.org%2Fw%2Findex.php%3Ftitle%3DSpecial%253ASearch%26search%3Dpipe%2Bkey</a><br />
<br />
<br />
the above gibberish takes you to a link to donate to <strong>wikipedia</strong>, please consider it to maintain a great service.<br />
<strong>J</strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-22209195698821367812011-11-18T12:25:00.001+00:002011-11-18T12:25:48.358+00:00The # key and its history<a href="http://en.wikipedia.org/wiki/Number_sign">http://en.wikipedia.org/wiki/Number_sign</a><br />
<br />
Did you know some people call it the pound key ? also the Octothorp ????<br />
<br />
<strong><em>weird !</em></strong><br />
<br />
<strong><em>:)</em></strong><br />
<strong><em>J</em></strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-3780314027835159632011-11-18T12:23:00.001+00:002011-11-18T12:23:08.835+00:00Whats that key for again ??<a href="http://www.computerhope.com/keys.htm">http://www.computerhope.com/keys.htm</a><br />
<br />
the above link simply explains what all the keys on your keyboard are called and what they typically do for you !<br />
<br />
<strong><em>Simples</em></strong> <br />
<br />
<strong>JJ</strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-46031191749530110182011-11-16T20:49:00.001+00:002011-11-16T20:51:54.898+00:00Grr how do you show Percentage ChangesThis is not an obvious one but I was aksed how can I show a percentage CHANGE between two values,<br />
eg.<br />
Sales in 2009 were 75K and by 2010 had risen to 125K what is the % change ?<br />
<br />
The Formula you can use is as below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHLyAZY13FVkAEEfopyHEKgLNGvVT4EcqZ_4VejgY494X_c30mS_MJNyI7JyBGU4Z4VM3z16XwFCKyp6isuKsayLZ4j4yFGFnkQowlOmGKtJg7gMX_glbXqXPt8-Pgij_4zNNB/s1600/percentage+incr.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHLyAZY13FVkAEEfopyHEKgLNGvVT4EcqZ_4VejgY494X_c30mS_MJNyI7JyBGU4Z4VM3z16XwFCKyp6isuKsayLZ4j4yFGFnkQowlOmGKtJg7gMX_glbXqXPt8-Pgij_4zNNB/s400/percentage+incr.png" width="400" /></a></div>
<br />
Thus returning a value of <strong>67%</strong><br />
<br />
<strong>nice one !!!</strong><br />
<br />
<strong>JJ</strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-88106033715058862372011-11-16T20:46:00.001+00:002011-11-16T20:47:15.418+00:00Charts and MORE Charts<a href="http://processtrends.com/downloads.htm">http://processtrends.com/downloads.htm</a><br />
<br />
Take a look at the above link for lots of specialist Chart types not normally available in Excel.<br />
<br />
thanks<br />
<br />
<strong><em>JJ</em></strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-86250472412193771772011-11-11T16:02:00.001+00:002011-11-11T16:02:55.287+00:00Another IF ISERROR example<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkrDitY2EWUUsRf_G8pkMgomtGeBqBLwMqQ65Xly-Xyhg-2fSoyS6uT9rz6CwSNbCUxvzLIPpPp8Nx_8UmpWxJC24ddAEPIXxlATQ-XeoQIUimWJS5Srpk6wLtS0rrQCXRM3vU/s1600/iferror.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="252" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkrDitY2EWUUsRf_G8pkMgomtGeBqBLwMqQ65Xly-Xyhg-2fSoyS6uT9rz6CwSNbCUxvzLIPpPp8Nx_8UmpWxJC24ddAEPIXxlATQ-XeoQIUimWJS5Srpk6wLtS0rrQCXRM3vU/s640/iferror.png" width="640" /></a>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-17394966311237000832011-11-11T15:50:00.001+00:002011-11-11T15:54:28.402+00:00IF ISERROR and VLOOKUPs<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhycBowJGjxiWPi22PZ1QubxmoGGQJmB9bUPGS80NisnAaA8VLRd0Xdta7NBGlW3ilrQgqvp4NwdyKns7cFIvazLfztmwVCh-zpGr6H-MKwo3r_X52_ZApIljwCnocjv-IbF7Ob/s1600/ifiserror.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhycBowJGjxiWPi22PZ1QubxmoGGQJmB9bUPGS80NisnAaA8VLRd0Xdta7NBGlW3ilrQgqvp4NwdyKns7cFIvazLfztmwVCh-zpGr6H-MKwo3r_X52_ZApIljwCnocjv-IbF7Ob/s400/ifiserror.png" width="400" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
=IF(ISERROR(VLOOKUP(E6,CostTable,2,FALSE)*E6),0,VLOOKUP(E6,CostTable,2,FALSE)*E6)<br />
<br />
use the above to solve your Excel 2003 Vlookup error requirements, this enables SUM calcs to still give you an answer and replaces error cells with zeros.<br />
<br />
JJJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-16822596544499662042011-11-11T15:31:00.001+00:002011-11-11T15:31:57.397+00:00dynamic searching with VLOOKUPS, MATCH and INDEX<a href="http://office.microsoft.com/en-us/excel-help/dynamic-searching-using-vlookup-match-and-index-HA001154902.aspx">http://office.microsoft.com/en-us/excel-help/dynamic-searching-using-vlookup-match-and-index-HA001154902.aspx</a>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-6075907719700720602011-11-11T15:29:00.001+00:002011-11-11T15:29:50.599+00:00IF ISERROR VLOOKUP<a href="http://www.exceltip.com/images/screenshots/810.gif">http://www.exceltip.com/images/screenshots/810.gif</a><br />
<br />
Another problem was how to use an IF ISERROR combo in Excel 2003 as this was much simplified<br />
in Excel 2007/10 with the IFERROR Function.<br />
<br />
see the screen shot above for a solution.<br />
<br />
<strong>JJ</strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-77611002757005631982011-11-11T15:05:00.001+00:002011-11-11T15:06:52.462+00:00Waterfall Charts, wet your appetite !A recent question about waterfall charts in Excel got me thinking "How do they do that ?"<br />
so after a little searching here is a great link to a fix for it in Excel.<br />
<br />
<a href="http://chandoo.org/wp/2009/08/10/excel-waterfall-charts/">http://chandoo.org/wp/2009/08/10/excel-waterfall-charts/</a><br />
<br />
PS<br />
Download the template to play with it for yourself.<br />
<br />
<strong>Jamie</strong>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-64467533967279641102011-09-18T22:50:00.001+01:002011-09-18T22:50:22.329+01:00Pivot Tables ( Consolidating multiple sheets)Pivot Tables can be tricky to new users but probably THE MOST POWERFUL thing in Excel.<br />
This week I was stumped on how to consolidate using Excel 2010 when creating a Pivot.<br />
When you go to Insert Pivot, there are now NO OPTIONS to get your data from a set of multiple worksheets. So unless you consolidate first it seemed a no go .<br />
<br />
However upon researching it the only way to get the Excel 2003 options for Pivot Wizard is to use a magic shortcut :<br />
<br />
ALT D then P, then you get the typical Pivot Table Wizard screen with mutiple sheet options, HEY PRESTO !<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsSZf0jPYgYLqMUV89Scp3Ot_UNqquAxtYw3Gs4BPjbkrj1eT6sPUaE5ZhZ8Z4Vd4lPwQeAVKGH3hM8iaqOgvPAxHWtVpW2PDDEe26VAe70WMOwr-ww-emACrWAdGTZQIkS01C/s1600/pivot+multi.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="256" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsSZf0jPYgYLqMUV89Scp3Ot_UNqquAxtYw3Gs4BPjbkrj1eT6sPUaE5ZhZ8Z4Vd4lPwQeAVKGH3hM8iaqOgvPAxHWtVpW2PDDEe26VAe70WMOwr-ww-emACrWAdGTZQIkS01C/s320/pivot+multi.png" width="320" /></a></div>
<br />
Insert Pivot only gives you these options in 2007 and 2010:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuyPWkJafHa6qnsWFTiEdc5YTEvu7tWY2snzsVh6wv-z3Gp4qHAe_aKknR9nzP4Onor0v80awuZAAC_mdxhnC6n-YTvKruPbw2ZSF8di_JIG-mEyWqo29c08J5V5IfUOBwWdgq/s1600/Insert+Pivot.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="235" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuyPWkJafHa6qnsWFTiEdc5YTEvu7tWY2snzsVh6wv-z3Gp4qHAe_aKknR9nzP4Onor0v80awuZAAC_mdxhnC6n-YTvKruPbw2ZSF8di_JIG-mEyWqo29c08J5V5IfUOBwWdgq/s320/Insert+Pivot.png" width="320" /></a></div>
thanks !<br />
<br />
JJ<br />
<br />
JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-40950098626859498962011-09-18T22:42:00.002+01:002011-09-18T22:42:38.485+01:00Testing out the NEW Blogger Interface and it seems very strange but useful !!JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-70640842434769484042011-08-08T12:52:00.002+01:002011-08-08T12:52:46.800+01:00Just for fun !!try:<br />
<br />
<a href="http://www.selfcontrolfreak.com/">www.selfcontrolfreak.com</a> for a bit of graphical fun, the mouse controls what happens within the video.<br />
Try number 1 he swats the mouse away, try and touch his nose.<br />
<br />
JJJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-24808804683979432672011-08-08T12:46:00.000+01:002011-08-08T12:46:59.612+01:00Apple iCloud Seminar VideoHave a look at this new seminar on the Apples approach to the Cloud<br />
<span class="Apple-style-span" style="color: blue;"><br />
</span><br />
<span class="Apple-style-span" style="color: blue;">http://events.apple.com.edgesuite.net/11piubpwiqubf06/event/</span><br />
<span class="Apple-style-span" style="color: blue;"><br />
</span><br />
<span class="Apple-style-span" style="color: blue;"><br />
</span><br />
<span class="Apple-style-span" style="color: blue;">JJ</span>JJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0tag:blogger.com,1999:blog-31519271.post-59449187476501289972011-07-28T11:29:00.001+01:002011-07-28T11:29:16.154+01:00J Walks legendary Excel/VB help site<a href="http://spreadsheetpage.com/">http://spreadsheetpage.com/</a><br />
<br />
enjoy !!!<br />
JJJJSurfhttp://www.blogger.com/profile/05398795972429066541noreply@blogger.com0