Office [75 post(s)]

30 January 2011 - 13:18

Full outer join in Access

During a course a participant informed about Access' possibility to create a full outer join.
If you've gone into the topic of joins, you might have realised too there isn't really a feature doing that - there's an inner join, a left and right join, but no way in one go to get a full outer join.

Of course I didn't needed more to get my curiosity taking off, and I found a really well written and clarifying article from the hand of Gregory A. Larsen.

His simple device is: if it can't be done in one time, do it in 3 + one time: create the 3 distinct queries that are possible and with a 4th union query you just combine those results.
Cool !

Here's another example from the Northwind Database:

SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers INNER JOIN Employees ON Customers.City = Employees.City

UNION ALL

SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers LEFT JOIN Employees ON Customers.City = Employees.City
WHERE (((Employees.City) Is Null))

UNION ALL

SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers RIGHT JOIN Employees ON Customers.City = Employees.City
WHERE (((Customers.City) Is Null))
(1) Comments ·
29 January 2011 - 01:48

Handling multi-selection lists in Access

One of the new features in Access 2007 was the possibility to implement multivalued fields, but that's not always going to help out for various reasons.
So handling data from lists where multiple selections are accepted remains a interesting challenge.

Here are some links I found helpful in implementing a solution for this:

(0) Comments ·
20 January 2011 - 05:42

Office Manuals for Download

The Mouse Training group has released their Office Manuals 2007 (2003 & 2000) are still available as well.

They form a good basis for those who are in to self training and are thus worth a download: http://www.mousetraining.co.uk/ms-office-training-manuals.html
http://www.microsofttraining.net/microsoft-training-manuals.php

(6) Comments ·
09 January 2011 - 14:26

Conditional Formatting for Excel charts

If you'd like to obtain some conditional formatting in your Excel charts, you'll have to as roll up the sleeves.
As there's no immediately feature for that - unlike of course the (greatly enhanced since Excel 2007) conditional formatting possibilities for cells- you'll have to proceed through VBA.

conditional formatted chart

Here are some links and samples to get you going:

If you really and rather go for something without VBA, then Andy Pope has some interesting scenarios for some specific situations:

(1) Comments ·
03 May 2010 - 15:12

personal.xlsb in Excel 2007 not loading automatically anymore

This happened to me and I was really pondering what was going on.
But I remembered that when Excel has a crash, the next time it loads it might prompt to de-activate certain add-ins. And indeed that did the trick.

personal.xlsb is gone !!!

So this might help out if you encounter the same issue:

  • Click on the Office button and go to the Excel options
  • Choose Add-ins
  • Go to the combo box at the bottom entitled 'Manage'
  • See whether you find personal.xlsb add-in and enable it again.
(0) Comments ·
28 April 2010 - 15:23

Error handling and debugging in VBA

If you're after some good explanation and highlights in terms of error handling and debugging with VBA, I would suggest these:

(0) Comments ·
13 April 2010 - 10:21

Excel Pivot Tables unplugged: hide items in a filter combo

Excel pivot tables are just a fantastic tool. And you can even boost it in various ways.
Just some creative ideas I encountered while working on a problem with hiding items in a filter.

Sometimes you just need to think out of the box to get where you want to get. And that may mean create an extra column with new data, you can then integrate in your pivot table analysis.
As as sample of this approach: Filter an Excel Pivot table for upcoming dates.

And did you know there are some specific shortcuts and manipulations to sort and hide information in pivottables? You'll find a good article on this at following blog topic.

What I hadn't realised yet is that in pre-2007 versions, there was an option in the 'page field zone' to hide certain items from appearing in the combo box, as you can see in this article on pivot-table.com .
Excel 2007 uses a new concept under the name of 'report filter' and as the 'multi-selection' feature has been added, this possibility to remove items from appearing in the combo has disappeared.
So if certain data shouldn't appear, you might have to go back to the source data and hide them there and thus use a 'out of the box' solution.

(0) Comments ·
30 March 2010 - 13:42

How to find (the first) capital(s) in an Excel cell?

Another interesting topic coming up through one of my clients.

I've found an enlightened discussion at one of these great Excel resource sites: http://www.dailydoseofexcel.com.
Especially the breakdown of the described first function and the elaboration on arrays is worth reading.

These are the two takes I've been going for:

{=MIN(FIND(CHAR(ROW(INDIRECT("65:90")));A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))}

The trick consists in finding the ASCII value of the first capital of a text-string composed out of the cell containing the evaluated text value combined with the 26 capitals of the Alphabet.

And then also a user defined function in VBA:

Function FirstCap(Cell As Range)
   For FirstCap = 1 To Len(Cell.Value)
      If Mid(Cell.Value, FirstCap, 1) Like "[A-Z]" Then Exit For
   Next FirstCap
End Function
' And =IF(firstcap(A1)>LEN(A1),NA(),firstcap(A1))
(1) Comments ·
24 March 2010 - 17:25

Find the cell with the lowest value in an Excel sheet

The beauty of Excel is that there are many ways to achieve the same thing.
Take this topic e.g. : how to find the cell with the lowest value in an Excel sheet?

If you're after the exact location of that cell, and you can limit your search to a specific column or row with results, you could use a combination of the ADDRESS() and the MATCH() function which would give you something like:

=ADDRESS(MATCH(MIN(A:A);A:A;0);1;4;1)
=ADDRESS(2;MATCH(MIN(A2:Z2);A2:Z2;0);4)

Or you might be more picky as to which type of information you'd like to withdraw from that cell containing the minimum value -
it could be the range address, but also it's content, or it's column position, it's formatting or it's locked status. That's something you can do with the CELL() function -
it's first argument determines the type of info you'll get as a result. With our two examples:

to obtain the cell addresses::
=CELL("address";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("address";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

to obtain the cell values::
=CELL("contents";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("address";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

to obtain the cell lock status::
=CELL("protect";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("protect";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

If you need this approach regularly, you could also take the VBA road and insert a module with your own user defined function.
Like the one here found at http://www.excelforum.com :

Function MinCellRef(dsRange As Range)
Dim c As Range
Dim MinCell As Single

MinCell = Application.WorksheetFunction.Min(dsRange)

For Each c In dsRange
If c.Value = MinCell Then
MinCellRef = c.Address
Exit Function
End If
Next c
End Function

And if you want some VBA solution which highlights and formats the cell containing the minimum value, that's possible too : http://excelvbamacro.com

(0) Comments ·
15 March 2010 - 17:10

Autosave in Excel 2007

For those who are searching the specific AutoSave feature in Excel: search no longer - it is really gone.
Well yes it was replaced by the AutoRecover feature, but it's behavior is sligthly different.

You'll need to use an add-in to get exactly what you had before, i.e. possiblity to autosave your Excel file at a specific interval on a specific place (under a specific name ?).
I've found one here: http://www.jkp-ads.com/download.asp

(0) Comments ·
10 March 2010 - 17:31

Templates location configuration in office 2007

Template location properties

If you're using a lot of templates in Office, it's a good idea to organize things a bit. This means changing the default template location and putting your templates in subfolders.
Because if you don't specify a specific location for your templates yourself, Office saves them in a very hard to tackle place in the profile folder of the user.
And if you have created sub-folders in the main template folder, Office is then going to show or represent these as separate tabs in the template dialog window.

In Office 2007 this still happens as in the older versions of the suite: rather mysteriously that is !
Because you have to go into the Word options to find the locations button - and only there ! - as it's unavailable in Excel or Powerpoint ...
So just go to the Advanced Options in the Word options dialog window, at the very bottom, you'll find the button File Locations giving you access to the default folders for the User Templates, as well as the Work group templates. Just choose the parent folder for you templates and presto.

Template location dialog box

All you have to do next is saving your Excel, Powerpoint or Word templates in this folder, or in sub-folders of this folder, and they will then automatically appear in the Template window.

(0) Comments ·
03 March 2010 - 14:57

Mailmerge with groups

Ever wondered or needed to do a mailmerge in MS Word with multiple group levels?
It is possible indeed; it involves customizing the merge fields a bit, but if that's what you need, you'll find the whole procedure through following links:

(0) Comments ·
10 February 2010 - 05:23

How to prevent editing when grouping sheets in Excel ?

This question came up during one of my Excel courses - clearly something to be done with VBA, so I googled it and found following code at http://www.pcreview.co.uk/forums/thread-3768672.php:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     If ActiveWindow.SelectedSheets.Count > 1 Then
     With Application

          .EnableEvents = False
          .Undo
          MsgBox "Oh, please don't group the sheets..."
          ActiveWindow.SelectedSheets(1).Select 'Ungroups
          .EnableEvents = True
          End With
     End If
End Sub

ps: for some interesting elaboration on Events go here: http://www.cpearson.com/excel/Events.aspx and here : http://www.cpearson.com/excel/AppEvent.aspx

(1) Comments ·
03 January 2010 - 13:59

Merge many word documents into one single document

This sounds like something fundamental, but for some reason I never really needed something to merge many word documents into one new document.
Till recently that is.

You can of course go the direction of the Master/Child documents approach in Outline view.
For more information on this you could try these links:

But if you really envisage one flat document, apparently you need a macro to do this properly - you'll need to finetune it with the specific path containing your Word documents as well as pay attention to the specific extensions the documents have :

Sub MergeDocs()
    Dim rng As Range
    Dim MainDoc As Document
    Dim strFile As String
    Const strFolder = "C:\Courses\Word\" 'change asper your need
    Set MainDoc = Documents.Add
    strFile = Dir$(strFolder & "*.doc") ' can change to *.docx or *.rtf
    Do Until strFile = ""
        Set rng = MainDoc.Range
        rng.Collapse wdCollapseEnd
        rng.InsertFile strFolder & strFile
        strFile = Dir$()
    Loop
End Sub
(0) Comments ·
30 December 2009 - 13:26

Adding a public folder to Outlook favorites

Add to Favorites

If you right click a public folder in Outlook, and choose the option to 'Add to favorites', you'll add this favorite to the Public folder favorites only.
But if you thought or wanted it to appear in the main mailbox favorites, you'll notice or have noticed already that nothing was actually happened.

No need to feel defeated any more, as the solution is really simple, although unfortunately not 'obvious' at all:
simply right click the Public folder favorites folder shortcut that you created earlier, and then select 'Add to favorites' again and hey presto, the favorite is now going to show up in your main mailbox favorites area.

(0) Comments ·
28 December 2009 - 07:14

Reversed Engineering on a pivot table in Excel

When you've created a pivot table in Excel, you can drill down on the results by double clicking on them - Excel then creates a new worksheet with the individual lines behind the result.
If you do so with the grand totals, all the lines, i.e. the full source sheet will be inserted into the new worksheet.

results in a pivot table like format without being a pivot table

So far so good.
But what if you have to do it the other way? I.e. if you have the results in a pivot table like format, e.g. provided by your database administrator in a simple Excel sheet - you can't do the drill down as you don't really have a pivot table. And you need to recompose the individual line behind the construction?
A simple reconstruction with some images of the starting and end situation will probably make the point clearer.

end result of the operation

To get to the desired result you'll need to do some 'reversed engineering' which is possible via the Pivot Table and Chart Wizard (which is not directly available any-more from the ribbons in Excel 2007, so if you use this version, you need to add this feature through the Customized Option for the Quick Access Toolbar). You'll find more information on this feature from the Microsoft Office site.

The wizard provides in an option to create a Pivot Table from a 'Multiple Consolidated range' of cells - even if you select just one range of data (with row and column labels), Excel is going to treat the data very much like in the Data/Consolidate feature. Each column become a data series, which is then used to compute a new pivot table. And even it's very near or exactly the same as the original data list, you know have a pivot table with access to the grand totals which you can double click to.... obtain the envisage individual results.

The Spreadsheet Page describes the procedure with screenshots for older Excel versions, but also provides in a macro automatising the whole procedure, at the following link.

Sub ReversePivotTable()
'Before running this, make sure you have a summary table with column headers.
'The output table will have three columns.
Dim SummaryTable As Range, OutputRange As Range
Dim OutRow As Long
Dim r As Long, c As Long

On Error Resume Next
Set SummaryTable = ActiveCell.CurrentRegion
If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
	MsgBox "Select a cell within the summary table.", vbCritical
	Exit Sub
End If
SummaryTable.Select
Set OutputRange = Application.InputBox(prompt:="Select a cell for the output", Type:=8)
'Convert the range
OutRow = 2
Application.ScreenUpdating = False
OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
For r = 2 To SummaryTable.Rows.Count
  For c = 2 To SummaryTable.Columns.Count
   OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
   OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
   OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
   OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
   OutRow = OutRow + 1
  Next c
Next r
End Sub
(0) Comments ·
23 December 2009 - 03:38

How to extract images from mail messages and / or PowerPoint slideshows?

This is actually very simple, although perhaps not that obvious at first sight (or trial).
The answer is : Save As Html.

save as html

And that's valid for any Office document basically.
Have a mail message with some nice images you'd like to use? Just go to the File menu or Office button and choose to 'Save as' - then change the option 'Save as type' to Web page - html. The system is going to produce an Html file in the folder you selected as well as a subfolder with all the 'dependant' files for the html page to render properly, like CSS files and graphic files. And that's it !

Same procedure for other programs like e.g. PowerPoint slideshows: just save it to you harddisk, open it in PowerPoint, then save as Html and presto.

(0) Comments ·
15 December 2009 - 04:16

Import WordPerfect 4 Files into Microsoft Word

In the prolongation of a previously published entree on text conversion problems with Word, I received a mail from someone who was searching for the old Wordperfect 4.2 conversion file, not available anymore in the last MS Office releases (WPFT432.CNV).

The place to be is an article called 'How to Import WordPerfect Files into Microsoft Word', published on http://www.columbia.edu/~em36/wpdos.
Somewhere within that page, you can download A replacement set of conversion filters for use by Microsoft Word.
There are the filters for the WP 5.X version, but also for WP 4.0 - the procedure described for WP 4.2 is the same, so you only need to copy the files to the described location (in a typical install under Win XP: C:\Program Files\Common Files\Microsoft Shared\TextConv).

However to get the converter presented in Word along with the other converters, you need to tweak the registry. To ease the process, you could safe following lines to a text file, save it as wp42update.reg or something; just double click the file will insert these lines in the appropriate places in the registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Text Converters\Import\WrdPrfct4Dos]
"Path"="C:\\Program Files\\Common Files\\Microsoft Shared\\Textconv\\wpft432.cnv"
"Name"="WordPerfect 4.x"
"Extensions"="doc"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Text Converters\Import\WrdPrfct4Dos\Options]
"DisplayWPGDialog"="No"
"Name"="WordPerfect 4.x"
"FavourSequenceFields"="Yes"
"FavourAppearance"="Yes"

Enjoy the nostalgia, as yes indeed where are the amazing times of the blue screened function keys driven interface of WP 4.2 and WP 5 :-)

(5) Comments ·
14 December 2009 - 16:04

Powerpoint Handouts

Handouts in PowerPoint can be printed through the print dialog box and settings - you have a certain control over their formatting through the handouts master.

If you're looking out for more flexibility to print out handouts, your best first step probably is creating handouts in Word.
To do so all you need is the SendTo option in the File Menu for pre-Powerpoint 2007 users or the Publish option from Powerpoint 2007 onwards. The wizard actually offers a series of options as how to layout your pages too.
You'll find a series of screenshots on these options at about.com.

And If you want even more control then have a look at the add-on available from skp.mvps.org.
The site offers a handout wizard allowing you to create your own customized layouts and more.

But coming back on the PowerPoint - Word wizard, it actually produces rather small thumbnails of the slide in the final Word document.
I've found a macro on answers.google.com to resize the graphics and tweaked it a bit as to make it work on the whole document for any number of slides.
Just copy the code into a module behind the document or into the 'Normal' project or template of your current software version.

custom handouts [Read more on Powerpoint Handouts...]
09 December 2009 - 16:12

Propagation of Custom Animation in Powerpoint

Once you have created a custom animation in PowerPoint, you might want to apply the same animation on the other slides. But... there is actually no way to propagate a custom animation in PowerPoint. No despair however, you can avoid having to redo the animation on each of your slides by applying your animation to a placeholder on the master.

Here's the procedure in a nutshell:

  • Open the Master View and select the body text placeholder (or placeholder of your choice depending on the Powerpoint version)
  • Apply the Custom Animation your choice, e.g. making text fly in and ensuring each of the bulleted topics flies in 'after previous' to then 'dim out' when going to the next one.
  • Close the Master View and save.
  • Remove all Custom Animations applied on individual slides now having become redundant.
  • Now play the slide show and check if all slides behave as wanted - if not try to 're-apply' the chosen layout to the slide or choose the correct layout.
propagate animations in PWP

Ps1 : You cannot delete animations applied by the Master slide through the custom animation features on a slide.
If you have PowerPoint 2002 or later you can take benefit of the multiple master feature to scope the animations throughout the slides and work with different masters to apply different or no custom animation asper the need.
And from Powerpoint 2007 onwards, there are much more placeholders available on the masters, so that gives margin for even more flexible solutions by choosing animation for specific placeholders.

Ps 2 For more information on this topic : http://www.jegsworks.com/Lessons/presentations/advanced/step-animatedata.htm

(0) Comments ·
06 December 2009 - 15:12

On windows explorer tooltips and thumbnails

It's amazing how many different settings can be encountered on computers ... and how confusing or complex it can all turn out for the 'normal' end-user.

Like recently during a PowerPoint course in a course room with identical computers. When browsing images to insert into the slides, some participants see the thumbnails of the images, as well as the pixel dimensions, others see thumbnails without the pixel dimensions, and still others don't seem to be able to activate the thumbnails altogether.

To start with the last one: if you don't see the thumbnails for images when you should, it's most likely linked to a setting in the folder options Windows Explorer - in the View Options, you have to make sure the option 'Always show icons, never thumbnails' is un-checked.
Remark that the word 'Thumbnails is not used anymore in Vista, but Large and Small Miniatures should then just do what they say.
If ever this is not the cause of not seeing thumbnails in your folders, you could try-out the procedure as described by the Microsoft Knowledgebase, as indeed there are issues with thumbnails: http://support.microsoft.com/kb/928572

The specific information (size, pixel dimensions, file type, etc) appearing in the tooltips are actually dependant on some registry settings; it seems a bit of a mystery how some of the settings sometimes get updated and sometimes not, probably to installation of certain software, but if you want to master it yourself you'll find the keys in following articles: http://www.ghacks.net/2008/02/10/customize-windows-explorer-tooltips/.

Happy tooltipping !

(0) Comments ·
02 December 2009 - 09:04

Problems with printing backgrounds in Powerpoint

printing in PWP

When printing slides in PowerPoint, you might have encountered problems with the backgrounds - mostly being too dark or so and making text unreadable.
You might have realised also there's not such an option (anymore ?) in PowerPoint as 'omitting the background graphics' from printing.

The first way out in this situation is always to try print in 'grey tones' (this should inverse difficult to read text also) - the option is available from the print dialog box, in the 'print what section'.
If still in problem try it again with the 'pure black and white' option - available at the same place in the print dialog box.

You could of course also create a special master for printing purposes only, where you omit the background and/or choose a different colour scheme. You would then have to save the presentation twice, once for the screen (eg presentation.pptx) and for printing (eg presentation_prn.pptx) or if you use PWP 2003 +, create custom shows within your presentation, one for printing and one for doing the presentation as such. As from PWP 2003 onwards, there's the possibility to use multiple masters in the same presentation.

(1) Comments ·
01 December 2009 - 05:25

Copy an Outlook folder structure

To copy an Outlook folder structure, e.g. for back-up and filing purposes, do the following:

  1. Export the folder with subfolders and content to an pst file (file > import/export to pst file)
  2. Load the file (File open/ outlook data file)
  3. Archive the folder (right click - Auto archive) by setting the condition to 'items older than today, or one day' and by ticking the remove permanently box
  4. compact the pst file to reduce it's size to normal proportions again

For more details see: http://office.microsoft.com/en-us/outlook/HA011166031033.aspx.

(0) Comments ·
02 November 2009 - 06:45

Mail merge with Word and Access 2007 issue

Recently I bumped into weird problem with Office 2007, whereby Word didn't recognize data from an Access database during a mailmerge.
Fairly basic but... no way.

What would we do without the exchange on the internet ?
Peter Jamieson from http://tips.pjmsn.me.uk published the answer on eggheadcafe.com, a free online training platform I consult regularly:

There are several types of query that Word 2007 cannot "see", or does not execute as you might expect, when you try to use them as a Mail Merge data source using the default connection method for Access (which is OLE DB), including:

  • a. parameter queries
  • b. queries that invoke user-defined Access VBA functions
  • c. queries that invoke some of the financial series functions and one or two other "non-user defined" VBA functions such as replace()
  • d. queries that use LIKE and wildcards

In most cases, the quickest way to get around these problems is to change the connection method of the connection to DDE - in Word 2007 - check Office button->Word options->Advanced->Confirm file format conversion on open (near the bottom), go through the connection process again, and you should see an additional Confirm Data Source dialog box. If you see an entry like "MS Access databases via DDE", select it. If you only see "OLE DB database files" (typically if you are using a .accdb rather than a .mdb source), check Show all, then locate and select that DDE option, and continue.

(3) Comments ·
10 October 2009 - 15:35

Customizing the Ribbon in Access 2007 and Office 2007

If you wondered how to customize the ribbons in either Office 2007 or especially also Access 2007, here are some links which could be clarifying.
Ribbons are based on XML now, and so that makes the process more technical then e.g. customizing a toolbar before.

Access

Resources

(0) Comments ·
09 September 2009 - 04:39

Default database & templates in Access 2007

When you'd like to create a default database in Access 2007, containing your preferences, settings, tables, forms, modules and so on of your choice, you can do so more or less as before in previous versions of the program.
Basically you'll have to create a new database, save it in the system's or your profiles' Templates folder for Access and just make sure you've made all changes in preferences and imported all objects you want by default.

You'll find the procedure nicely described on the Office Online Section for Access or also at Allen Brown's pages: http://allenbrowne.com/ser-43.html#dbtemplate.

templates folder and database in Access

But new in Access and a really cool feature is the possibility to use Database Templates (accdt files). Those Templates files are available from the Access Getting Started Page and are presented in different Template categories in the left navigation area.
To create such Database Templates files you'll need the Access Developer Extension tools (ADE), which is not included in the default install files. You can download this package at the Microsoft Download center.

Once installed you'll find a link on the Office Button at the bottom: Developers / Save As Template - once you've saved your template in one of the dedicated Templates folders (see previous section), your templates become automatically available from the Getting Started window.

(1) Comments ·
07 September 2009 - 04:03

Deploy Trusted Locations in Office 2007 system

If ever you need to deploy Trusted Locations in one of the Office 2007 programs and / or want to understand a bit more about the 'Group Policy Object Editor' here's the official Technet link: http://technet.microsoft.com/en-us/library/cc178948.aspx.

And following discussion might throw some light as well: http://www.access-programmers.co.uk/forums/archive/index.php/t-136411.html
(0) Comments ·
03 August 2009 - 03:13

playing with 22, 7, 142857 and precision

In a reflection and research on Excels precision handling, I came across this magic number 142857. From the Wikipedia definition (http://en.wikipedia.org/wiki/142857_(number)):

142857 is the six repeating digits of 1/7, 0.142857, and is the best-known cyclic number in base 10.
If you multiply the number by 2, 3, 4, 5, or 6, the answer will be a cyclic permutation of itself, and equivalent to 2/7, 3/7, 4/7 ,5/7 , or 6/7, respectively.

So in other words try this:
142857 * 2
142857 * 3
142857 * 4
142857 * 5
142857 * 6

But also this:
1/7
2/7
3/7
4/7
5/7
6/7

I hadn't realized that pi, or the famous 22/7 has this exact cyclic number too as decimals: 3,1428571428571428571428571428571...

22 and 7 being numbers which for all kind of reasons have always been predominant to me.
Makes you wonder ...
about the matrix of life e.g.

Oh, I almost forgot the point where it all started, the 'floating point', the maximum precision Excel can handle.

ps you'll find a 142857 calculator at Douglas Twitchell blog.

(0) Comments ·
31 July 2009 - 16:30

Delving in the treasure house of Pivot tables: calculated fields with if and more of those jewels

Delving after a solution to use functions, more precisely an IF() function, in calculated fields within Excels' pivot table tool, brought me about a couple of interesting insights and resources.

if() function in calculated fields

Firstly for the use of an if() function in calculated fields: that's a tricky one; I couldn't find a lot of useful links - I did find some descriptions of people struggling with the same issue: http://www.mrexcel.com/archive/Pivot/4588.html is one example of this. The conclusion of my research here was summarized by one of the comments:

PivotTables always apply the summary function to any field used in the calculated item. So effectively, your calculated field is evaluating =IF('Cost per Order'<35,SUM(Cost),0).
Since the Grand Total of Cost of not less than 35 it will return 0. Grand Total always operate on the data list contents not what's displayed in the PivotTable.

Or in other words: avoid the if function in calculated fields, implement it in the data table itself as a separate column, and use those values as column headers in your Pivot table - you can always hide columns which you don't want.

An example please !

Pivottable with prepared if()

To get the illustrated Pivot table, I first added a column to the data table with following code for a simple evaluation whether the line result goes over 1000 or not.

=IF(F2>=1000;"ok";"nok")

I've then used the ok/nok field (column) as a column label/field and filtered on 'ok' values only to get what I wanted - remark that I've added another field, total turnover, and kept the subtotals. By simply hiding the non wanted columns I can then have the number of 'oks' along with the Total Turnover (for all oks and no-oks).

[Read more on Delving in the treasure house of Pivot tables: calculated fields with if and more of those jewels...]
(0) Comments ·
09 July 2009 - 16:21

How To Extract Flash or SWF from Office files

If ever you're after a nice procedure, here it I've found one: http://www.walkernews.net/2008/03/22/how-to-extract-swf-flash-from-excel-or-word/#VBASC

And the other way round: to embed SWF in Microsoft Office documents: Excel - Word

(0) Comments ·
28 June 2009 - 07:00

Using dates in stead of date-references in Excel formulas

If you're calculating with dates in Excel and you want to refer to a hard-coded date in stead of using references to your dates, you'll be in for a surprise.
In Access you can refer to a literal date using the hashes, e.g. #30/06/2009#, but Excel does not understand that.

So following formulas aren't going to do what you intend it for or will throw out errors:

=IF(A2<=31/3/2009;"Trim1";IF(A2<=30/6/2009;"Trim2";IF(A2<=30/9/2009;"Trim3";"Trim4")))
=IF(A2<="31/3/2009";"Trim1";IF(A2<="30/6/2009";"Trim2";IF(A2<="30/9/2009";"Trim3";"Trim4")))
=IF(A2<=#31/3/2009#;"Trim1";IF(A2<=#30/6/2009#;"Trim2";IF(A2<=#30/9/2009#;"Trim3";"Trim4")))
You need to use the Datevalue () function which transforms a date-string into a real date Excel can understand and handle. So don't forget to put the hard-coded dates between quotes " " :-)
=IF(A2<=DATEVALUE("31/3/2009");"Trim1";IF(A2<=DATEVALUE("30/6/2009");"Trim2";IF(A2<=DATEVALUE("30/9/2009");"Trim3";"Trim4")))
(0) Comments ·
21 June 2009 - 01:58

Automising updates for items in Outlook

attribute categories quickly

Here are two pathways to increase productivity in Outlook.

Suppose you wanted to apply the same category to multiple Outlook items eg, or you wanted to set a whole range of tasks as completed. Or change the due date for a whole bunch of tasks.
U see the point?

One interesting approach is that in Outlook you can always drag and drop many items onto one grouped header.
Make sure you have one item attributed to the category you envisage, use the group by view, then simply select all the items you wanted to categorize and drag and drop them on top of the category header.
Or in the tasks folder, group by Due Date. Then open and change the Due date of 1 Task so that you get a destination group. Then drag the tasks for which you want to change the Due Date on top of the group header.

You can of course also go the VBA path.
[Read more on Automising updates for items in Outlook...]

(0) Comments ·
17 June 2009 - 11:21

Change the default protection message in an Excel sheet

Is there a way to change the message that is displayed when the user tries to modify a locked cell on a protected sheet?
That's exactly the question I was asked during a course.

The answer apparently is : not really - following the discussion on this thread: http://www.xtremevbtalk.com/showthread.php?t=224139, the conclusion probably is: prevent the use of being able to go into the cell at the first place.
Just make sure the option 'select locked cells' is DE-activated when you activate the protection on the worksheet

Then also you could add this VBA code to your sheet tab

Private Sub Worksheet_Activate()
If ActiveSheet.ProtectContents Then
  MsgBox "Watch out, you're going to have some funny messages" _
  & "if you try to change some protected cells in this sheet"
Else
  MsgBox "Sheet is not protected"
End If

End Sub
(0) Comments ·
27 February 2009 - 16:26

Excel 2007: the xlsb format? And formats basically…

Have you heard already from the xlsb format?

In case I made you wonder: the 'b' stands for 'binary' and here are two links explaining a bit more about this new file format in Excel 2007:

And here is a very nice scheme and article explaining how to 'decode' the excel file formats:
http://blogs.msdn.com/excel/archive/2006/07/20/671995.aspx

file formats in Excel
(0) Comments ·
20 February 2009 - 12:48

Multiple Substitute in Excel

During my excel courses I generally explain about the substitute() function.
But what if you wanted to change many characters, like e.g. many accented characters to their non-accented equivalents?

You could of course nest many substitute() functions and end up with something like this.

But here you'd probably best implement the use of a socalled user defined function, a 'udf'. Not sure where I found this one ...

Function BigSubstitute(CellToChange As Range, _
NameNumber As String) As String

  Dim X As Long
  Dim Data() As String
  Data = Split(NameNumber, ",")

  If (UBound(Data) + 1) Mod 2 Then
      BigSubstitute = "#MISMATCH!"
      Exit Function
  Else
      BigSubstitute = CellToChange.Value
      For X = 0 To UBound(Data) Step 2
      BigSubstitute = Replace(BigSubstitute, Data(X), _
      Data(X + 1), , , vbTextCompare)
      Next
   End If
End Function

So you'll end up with something like this:

=BigSubstitute(C3;"é,e,à,a,ç,c,è,e")

P.S. The mismatch in the function is used to make sure you always have couples of data, in this case an accented and non-accented character

(2) Comments ·
30 January 2009 - 15:50

Refreshing an Excel worksheet when cell content changes

Recently I sat with a chess player who had found his way through table and pivot table functionalities in Excel in order to sort, filter and display chess results for his chess association.

His next step actually consisted in automating some of his sheets presenting the chess results. One thing he wanted to achieve is an automatic sorting of the table with the main results along with the automatic update of the pivot table based on those results. So far the operation consisted out of manual resorting of the data based on 3 or 4 fields, along as clicking the refresh button in the pivot table options.

The solution here consisted out of 3 steps.

Firstly, of course a simple macro could be made to trigger of the sorting instantly. However as the results change all the time during a chess happening, the macro ideally would have to be triggered off by each entering or updating of the results in the sheet. Actually this second step is possible by using some embedded Excel VBA worksheet events. Worksheet events, just as workbook events, or a kind of prefixed or pre-named procedures which you call in the module sheet behind the worksheet object in the VBA editor:

worksheet events

Amidst the possibilities are following triggers:

Private Sub Worksheet_Activate() - when the worksheet is activated
Private Sub Worksheet_Calculate() - when the worksheet is recalculated
Private Sub Worksheet_SelectionChange(ByVal Target As Range) - when the selection is changed
Private Sub Worksheet_Change(ByVal Target As Range) - when a change is made to a cell in the worksheet

It's the last one which offers the possibility to solve the riddle here; following code triggers off the macro doing the sorting to the table whenever a cell value is updated or entered:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     mkSortCK 'macro which is being called
End Sub

Finally a simular worksheet event construction offers the possibility to refresh the pivottable automatically, when you activate the sheet it's located in:

Private Sub Worksheet_Activate()
     ActiveSheet.PivotTables("Results").PivotCache.Refresh
End Sub
(0) Comments ·
30 December 2008 - 06:50

Excel: Data validation, combo lists and autocompletion to search values quickly

Is it possible to type in the first letter in an Excel validation list to quickly go to some values within the list?
Another of those questions falling in between the lines of an Excel course.
The answer is : no. But of course even if the answer is right, it isn't satisfying really... or is it?

data validation [Read more on Excel: Data validation, combo lists and autocompletion to search values quickly...]
(2) Comments ·
29 December 2008 - 12:21

Accumulated subtotals in a filtered list or table in Excel

How to obtain cumulated subtotals in filtered data in Excel (be it in a list or table asper Excel 2007)?

cumulated subtotals in filtered data

It's not very difficult actually, but it seems to have a tweak ! This is how it works.

Create your list or table as usual first. Make sure you have a blank column to the right of your table to add your main formula. As you might already know we'll have to use the subtotal() formula here, as this is the only one considering filters and giving you correct results with filtered data. (Excel 2007 uses this by default in the creation of Tables by the way if you add the totals from the Table Data sub-ribbon).
If you want cumulative subtotals, just fix the first reference (make it absolute) and leave the second half relative as follows:

=SUBTOTAL(9;$F$3:F3)

Copy this through and you're basically done.

If you don't leave a blank column and/or don't make sure Excel does not include your subtotals column, there seems to be a problem in the results you'll get when filtering. You get one or more extra lines which do not match the filters. This might be linked to the fact that we're referring here to the cells in a rather unusual way...
Only disadvantage of this tweak is that if you use alternated row colouring (a new feature in tables from Excel 2007 onwards), you won't be able to get this formatting in the subtotals column. You could transform your subtotals column also in a table and format it in the same way, but this doesn't seem to be a solid solution either.

Don't hesitate to drop a line with your experiences on the matter or if you've got a better solution !

(0) Comments ·
28 December 2008 - 15:25

Double Clicking format painter in Office rocks … not always

The format painter is one of the most use features in Office programs and many people also use it by double clicking on it once the source has been set to enable multiple pasting of the formatting.

Recently a course participant expressed his frustration as this feature didn't work on his PC - and he seemed to have a friend wrestling with the same issue.
Not much more is needed to invite me on the investigation path.... and indeed not much to be found by googling it and in Microsoft knowledge base only some rare confirmations of a couple of people having had or having the same issue.

format painter problem

Looking a bit more careful to the cursor behavior, brought me to the inspirational thought: "and if it would be linked to the double speed of the mouse button ?"
And indeed, soon we found out that double click speed was set to maximum and that by reducing speed a bit, the format painter rocked again...

Well the wonders of refined technology - 'wah' and 'oh', but all so often 'grrrr' before the 'aha' brings back the 'omm' .... :-)
(1) Comments ·
26 December 2008 - 08:43

Text Conversion problem with Word 2007

Hadn't noticed this before with Word 2003, but when trying to open very old text documents (made with wordperfect 4.2 and 5) I got a funny "the selected text converter is an old version" error message.
I reinstalled the text converter... in vain. Downloaded Microsoft's text converter from their site... in vain. Searched the MS knowledgebase... you got it yes... in vain.

But what a wonderful place the internet is, as you always find one or another thread leading to a breakthrough.

This time my lucky one was this one.

The problem seems indeed that the older text converters don't seem to be updated when installing Word 2007 (or even 2003 as mentioned in the post).
The solution for me was:

  • Rename the current folder with older converter files at C:\Program Files\Common Files\Microsoft Shared\TextConv
  • Reinstall the text converters - this provided the updated dll and so indeed solved the issue
18 December 2008 - 12:23

Excel Shortcut Keys

excel shortcuts

A question I regularly get asked during Excel courses is: where do I find some good Excel shortcut keys?
Well, on the net of course :-)

Just two links to get you going:

And if you're after a downloadable document with shortcuts:
http://officeblogs.net/excel/Charlie_Keyboard_Shortcut_Book.xls.

(2) Comments ·
11 December 2008 - 09:57

Save embedded images from an Outlook 2007 message

"Why have it simple, if you can complicate it" - one often wonders...

If you've ever wondered why you just can't copy/paste embedded images from Outlook 2007 messages... well you're definitely not the only one.
I don't really know the exact reason - I sense the Word rendering engine now used by default might behind it, but I do found the solution to the problem here.

It works through a VB macro, which you need to embed into a module and trigger off by a button. Very like much a procedure to save all attachments at once, but this one saves the embedded images as well.
Watch out, this macro needs you to activate a special reference through the VB editor (Microsoft Shell controls and automation) and saves all attachments and embedded images at once in a directory you can choose.

If you want to use something more flexible you could try the Outlook plugin from EZ Detach

.
(2) Comments ·
22 November 2008 - 12:49

Excel 2007 - Ribbon survey

office ribbon survey

This is the first of it's kind I've come across: an interesting survey examining users' experiences with the new ribbon GUI in Office 2007.
No comment :-)

ps. but a must read for all those doing Training Courses

(0) Comments ·
21 November 2008 - 10:24

How to find the previous or last filled cell in a column in an Excel spreadsheet?

During an Excel course yesterday, someone wondered whether it was possible to fill out a column with ascending numbers, whilst omitting the blank lines (or lines where the triggering value in a column are blank). I'm including an image here to kind of clarify the nature of the question.

filling in Excel

My initial wanderings brought me to some interesting approaches based on the use of following formula based on what I found at this site:

=MAX(IF(LEN(A1:A100)>0;ROW(A1:A100);0)) - to find the row number of the last filled cell in a column

=INDEX(A1:A100;MAX(IF(LEN(A1:A100)>0;ROW(A1:A100);0));1) - to get the content of last filled cell in a column

These are array formulas of course, to be entered with Ctrl-Shft + Enter; also bear in mind you that you need to use the commas or semicolons to separate the arguments depending on the regional settings you use.

Finding the content of the last filled cell in a column, could also be done with something like this, using the ADDRESS and INDIRECT functions:

=INDIRECT(ADDRESS(MAX((ROW(1:50)* (C1:C50<>"")));COLUMN(C:C)))

These formulas only consider the last cell of the column however, and to really solve the uttered puzzle we need to obtain the last cell of each previous block of cells, in order to be able to continue and increment the counting. So this is what I came up with :

=IF(F3<>"";IF(A2="";ROW()-COUNTBLANK(OFFSET($A$2;0;0;ROW();1))-1;A2+1);"")

If there's nothing in the triggering cell, then of course there's no numbering. Then if there's something in the previous cell, the numbering can just go on (+1). However when the cell is empty, in stead of looking for the last cell of the previous block, I finally decided to just go and get the number of blank cells and reduce it from the row number. That does the trick.

But yes if you have another approach or know a way to get the last cell of the previous block of cells to increment it, I'd be delighted to know !

(1) Comments ·
17 November 2008 - 16:56

Managing external links in Office 2007

If you're used to use the Paste Link functionality between PowerPoint and Excel, or similar functionality to obtain links between your different Office applications in Office 2007, then at one point you'll be wondering where the management tool went...
well you know: the dialog box which in older versions was available from Edit | Links...

Well very simple : it now sits under the Office button | Prepare | Edit links to files

Gosh... what a location I hear you thinking - yes I did so too :-)

ps : If you haven't done so, it may be a good idea to download the Reference Guides to find locations of 2003 commands in 2007.
(0) Comments ·
16 November 2008 - 16:20

Transferring the Quick Access Toolbar settings in Office 2007

The easiest way to transfer the Quick Access Toolbar from one computer to another is simply copying the *.qat files in your Application Data folder.
You'll find these here:

  • XP: C:\Documents and Settings\'username'\Local Settings\Application Data\Microsoft\Office\*.qat
  • Vista: C:\Users\'username'\AppData\Local\Microsoft\Office\*.qat

Simply copy the *.qat files for the customized quick access toolbar you're after from one computer to another.

Ps : found this one here:http://www.avianwaves.com/Blog/default.aspx?id=74

[Read more on Transferring the Quick Access Toolbar settings in Office 2007...]
(0) Comments ·
14 November 2008 - 15:15

Calculate variable cumulative rates and commissions in Excel

Doing courses has the interesting side-effect that people come with all kind of questions challenging you to go beyond what you know and are able to do all the time.

So next situation was proposed by one of my participants, and although I had some initial pathways to solve the riddle, I really started wondering about better and other logics and ways to approach the question.

Suppose you have a salesperson having sold 45000 € - a cumulative bonus needs to be calculated based on a table, considering the proportional bonus for each scaling :

0 0,00%
20000 2,50%
30000 5,00%
40000 7,50%
50000 10,00%
60000 12,50%
70000 15,00%
80000 17,50%

So in other words: (2,5% * 10000) + (5% * 10000) + (7,5% * 5000) i.e.
upto 20000 there's no bonus, below 30000, there's 2,5%, below 40000 there's 5% etc. (the vlookup logic)

So I published this question on an outstanding Excel discussion forum and obtained plenty of inspirations through it.

One of the links pointed to a very broadening approach for me: http://www.mcgimpsey.com/excel/variablerate.html.

The most simple solutions to me finally seemed these:

variable cumulative rates and commissions
In the range A1:A8 enter the numbers 
0,20000,30000,40000,50000,60000,70000,80000

B1: 0
B2: =(A2-A1)*C1+B1
Copy from B2 to B8
C1:C8 - enter the percentages 0%, 2,5%...17,5%
E1: the amount being examined: 45.000

And for the formulas as such

=VLOOKUP(E1;A1:B8;2;TRUE)+(E1-VLOOKUP(E1;A1:A8;1;TRUE))*VLOOKUP(E1;A1:C8;3;TRUE)
=SUMPRODUCT(--(B11>$A$1:$A$8); (B11-$A$1:$A$8); $D$1:$D$8)
(0) Comments ·
12 November 2008 - 16:04

Criteria ranges with functions in Excel

Yes you can use functions in Excel's criteria range when using the advanced filter.
Here's a sample.

functions and criteria ranges

Remark that in this scenario, you don't need to have something written in the criteria range's label cel; but you do need to select.
Another thing is that you need to refer to the first cell with content (below the label) to refer to a column in your expression.
Finally you need to think carefully about absolute and relative references - as always actually :-) ; the result of the function may give a 'FALSE' or a 'TRUE', even an error code, but the filtering will work.

(0) Comments ·
05 November 2008 - 15:09

Excel Resources and Links

Some interesting Excel links :

Specifically linked to VBA :

For those users who feel they need to practice more:

Some hints towards books

In french:

li/lihttp://www.excel-exercise.com/
(1) Comments ·
04 November 2008 - 14:10

Blocked Outlook attachments

To solve problems with Outlook 2007 attachments which are blocked:

  • 1. Make sure Outlook is closed.
  • 2. Open your registry editor by opening the Run command and type regedit
  • 3. Locate the following key
    Outlook 2000 [HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Outlook\Security]
    Outlook 2002 [HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Outlook\Security]
    Outlook 2003 [HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Security]
    Outlook 2007 [HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Security]
  • 4. Go to Edit-> New-> String Value and name the value Level1Remove (case sensitive!)
  • 5. Double-click on the newly created value and enter the extension including the “dot” that you want to open in Outlook. For instance .exe
    If you need to enter more than one extension you’ll have to type separate them by a semicolon like this; .exe;.bat;.url
  • 6. Press OK on the input box and close the registry editor
  • 7. When you open Outlook the attachments which hold those extensions aren’t blocked by Outlook anymore.
(0) Comments ·
18 October 2008 - 14:08

Median in MS Excel Pivot tables

Adding to a previous post on this blog, Excel pivottables and subtotals in % relative to row-values, I keep on receiving all kind of simular questions of things which are hard or not possible with Excel’s Pivot Table functionality .  One of these is e.g. calculating ‘median’ values or using ‘median’ functionality.

If you really need to tackle huge pivot tables and analyze lots of data, then consider using the digidb’s special add-on. It offers all kind of added value which can be use to fill the gaps in the otherwise really wonderful Pivot Table feature. And yes: one of these features consist in the possibility of calculating Medians.

(0) Comments ·
17 October 2008 - 14:56

How to fill out blank cells in Pivot Table rows in Excel?

If you want to further analyse your Pivot Table generated data, then you might have come across this question I get generally asked by course participants:
How to fill down the blank cells in Pivot Table rows, as by default only the grouped field label is shown?

It’s a three step process basically: Selecting all non blank cells, typing a simple reference formula and propagating it in all non blank cells.
filling blank cells in pivot table rows

  1. So copy the pivot table to a different location first. Then select all cells you want to fill out, including the non blank cells, but starting with the first row label cell.
    The first trick is to get all non blank cells: Press F5 (goto) or ctrl + G. This open the Go to dialog, which has a special feature hidden under the ‘special’ button to select all the ‘blanks’.  That’s where to go first.
    Press OK to close the dialogs. The blank cells are now selected.
  2. Next step is entering the formula.  So double check that the first ‘active’ cell is the one just below your first row label (highlighted in white). Type “=” and press the ‘up’ arrow once.
    You now have a formula, which basically is just a reference, for this first cell.
  3. Last step is to hold down Ctrl and to hit enter.  This is a trick to copy the formula throughout all blank cells.

And that’s it.  From here you can do whatever you like.

(5) Comments ·
11 October 2008 - 09:24

Print Outlook mail messages without header

Most articles on printing Outlook mail items would generally deal with message headers not appearing, but since some time I was wondering how to actually remove the header when I really want to print a mail message.
I found this article on the office site: Hide or remove e-mail message headers. It explains it all !

message in developer view

Basically, you have to

  1. activate the developer ribbon
  2. edit the mail form (design form)
  3. go to the field's properties you want to de-activate or not see when you print (right click)
  4. on the validation tab, you can de-select the item when printing

This basically only works for the message only, so it's a hard way i'd say. If you want it to be permanent you'd need to find out where to re-publish the form, and I'm not sure you can overwrite the embedded forms used by the program. Before this was much more clear as in the latest versions apparently...
so anyone with a more robust solution ?

(1) Comments ·
01 October 2008 - 10:11

programming resources for Access

General

Code

Specifics

SQL Server

Some Books

(2) Comments ·
25 September 2008 - 02:34

How to remove multiple hyperlinks in Excel?

A question from a course participant: how to remove multiple hyperlinks in Excel ?

The best solution is a macro, which has to be made in visual basic code - it's really not very difficult, but if that scares off, you can follow the second or third 'semi-direct' methods...

  1. http://www.techonthenet.com/excel/macros/delete_hl.php
  2. Removing_Hyperlinks_without_a_Macro
  3. Removing_Hyperlinks_without_a_Macro_Take_Two

For the VBA code:

Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete
End Sub

(0) Comments ·
15 May 2008 - 12:01

View mails by conversation topic in Outlook

How to order or organize mails by conversation topic ? - a question today during an Outlook course....

Outlook Group By Conversation

The answer basically is : by grouping the view you're working in by 'Conversation' - a special field made available by Outlook; just go to the menu View | Arrange by | Conversation. (this is for Outlook 2003, but the main menu system is actually still available in Outlook 2007 too...)
You'll get a screen as the one shown above, allowing you to drill down through the topics by clicking on the small arrow in front of the topic.

A more detailed description is available on the Office web site.

(0) Comments ·
07 March 2008 - 13:00

Count Excel cells which DO NOT contain something

Every searched for a way in Excel to count the number of cells which DO NOT contain some text or some error message?
It’s quite easy really - suppose you had to count the number of downloads contained in cells, leaving aside all those cells which contain either some specific text or some error message:

=COUNTIF(downloads;"<>#N/A")
or =COUNTIF(downloads;"<>*something*")

or this would do as well:

=COUNTA(downloads)-COUNTIF(downloads;#N/A)
or =COUNTA(downloads)-COUNTIF(downloads;"*somthing*")

(0) Comments ·
29 February 2008 - 12:02

Access goodies

It has been an in depth Access training time this week.  So all kinds of goodies came up of course.

Data validation for variable alphanumeric string

One participant had a alphanumeric field and wanted to ensure that data entry was between 10 and 18 characters - and as of course text characters could be added, some simple number validation wouldn’t work.
I haven’t actually come to terms with input masks, e.g. if it would be possible to determine a range of minimum and maximum characters with it, I’d suggest to solve this puzzle by using a Data Validation Rule in the fields’ properties and adding this:

Len([FieldName])<=10 And Len([FieldName])<=18

You can actually use functions against the same field you’re entering data in - it doesn’t work always, but this one does.  And of course as always, for data entry you’re better off with a form to start with anyway.

[Read more on Access goodies...]
(0) Comments ·
22 February 2008 - 09:31

Working with Access in a multi user environment

Here are some links which I’ve found useful to better understand and implement the process of setting up an Access database in a multi-user environment:

(0) Comments ·
20 February 2008 - 10:07

Office 2007

Yes it’s a kind of earth quake in terms of user interface....
I’m referring here to Microsoft’s Office 2007 release and I see both experienced and basic users struggling with it - a bit proportionally I suppose with the number of years they’ve been using the previous releases.

I’ve recently found these helpful links to the Microsoft Office website, offering either a flash online and interactive presentation or some excel ribbon mapping workbooks -
a true help in making the bridge between the office 2003 and office 2007 commands !

(0) Comments ·
03 February 2008 - 13:46

Available formatting and available styles in Word

word available formatting
Have you ever wondered what ‘Available Formatting’ was in Word, where it went if it wasn’t there when you had seen it already, how it’s actually working or why this was added to MS Word 2003 ‘Style Taskpane’ options?
Well if you’ll switch to the latest version of Word 2007, just forget about it I’d say, as the whole Office 2007 interface changed anyway, but otherwise, here’s some possible help.

word available formatting
Firstly if you don’t have it, make sure you have ‘Format tracking’ on in Tools/Options/Edit - as you can see on the sample screenshot provided in this post.  You’ll then have an entry on the Style Taskpane allowing you to see all ‘Available formatting’.
word available formatting
‘Available Formatting’ will not only show you the styles, but also all the samples of additional makeup you’ve added to your existing styles.

You might wonder here : what is that???
Well the normal procedure is that you create a style and then apply that style to a paragraph or a character set.
However many people are not aware that if you change some formatting on text which already has been formatted, that Words’ Style Taskpane Panel tracks those entries, and actually adds them as a kind of ‘secondary’ style formatting.... You won’t see that, unless you have the “Available Formatting” activated.  In this way, you can actually also re-use those recorded formatting entries from the Style Task Pane… even if they’re not really styles !

So indeed this acts a bit against the fundamental logic of using Styles, where you’re only applying ‘pure’ styles and not the ‘hybrid’ kind of styles, as this messes up things considerably.  Very much as styles used on the web with CSS, Cascading Style Sheets…
amazing to see how this works very so similarly…

If ever you’re looking for more in depth explanations on this, Shauna Kelly wrote a nice article on this. I recommend it !

(0) Comments ·
27 January 2008 - 17:02

MS Project Quick Guide

MS Project Quick Guide - download +/- 1,5MB

This is a guide I once wrote for some training course - it has some good tips and quick overviews on:

  1. Setup for your project
  2. Creation of your project plan
  3. Tracking your schedule
  4. Communication
  5. 10 basic insights to better master MS Project

The screenshot are from MS Project 2007, but the guide works for any version of this software basically.
In my experience the basic insights are really helpful in getting started with this project management tool.

Enjoy !

(0) Comments ·
29 December 2007 - 13:30

Excel pivottables and subtotals in % relative to row-values

Something I had noticed already before and was presented in a recent course: how to get pivot tables in Excel to show subtotals for subitems in %, relative to values of the parent group, also ordered in rows?

Because the point here is the following: you can organise two grouped values, lets say 'manufacturers' and 'productfamilies', easily in rows and columns, and then for the calculated data (eg total number of products) request Excel to plot data in percentages (%) relative to row totals.... In case you were not aware: just look for 'field settings' and choose 'advanced options' to explore and discover this beauty.

However most users - and that includes myself - find working in rows 'only' much more handier. This gives you a great and very readible overview of your 'manufacturers' and 'productfamilies'. Now if you'd like to see the results in percentages, ... well that doesn't seem to be possible. Plotting against 'total of rows' gives you 100% for each row ; plotting against 'grand total' gives you the total of each individual line against the 'grand total' and not against the 'subtotal' (see rectangle in red).

pivot table with subtotals in rows and in percentages [Read more on Excel pivottables and subtotals in % relative to row-values...]
(0) Comments ·
27 December 2007 - 06:12

Installation of Excel add-ins

Funny how everything passes in ‘seasons’ - even software training !
The first part of 2007 was really coloured by Project Management training; over the last month Excel really was a champion. All kind of questions kept on coming in ...
So as a nice ‘add-on’ to a recent previous post (on excel charts), a question on Add-ins installation.
If it can be done automatically?

Well manually for sure via Tools / Add-ins / Add in Excel 2003 and before; from Excel 2007 onwards of course everything changed: press the Office Button (alt-F), go to Excel Options at the bottom, then click Add-Ins, go to the ‘Manage’ combo at the bottom, choose Excel Add-ins and you finally get what before took 2 clicks ... (now 5 or 6...ahhh I’m getting side-tracked...)

Automatically?
Well I’ve found this link to a freeware tool which you deploy during installation and updates the registry.
And a VBA road allowing you to automize with following code taken from a VBA blog

Sub Add_an_Addin()

Dim oAddin As AddIn
Dim oTempBk As Workbook

Set oTempBk = Workbooks.Add

Set oAddin = oXL.AddIns.Add("E:\yourAddInn.xla", True)
oAddin.Installed = True

oTempBk.Close

End Sub

The temporary workbook is added because at least one workbook needs to be open, before the Add-In install section is accessible.

24 December 2007 - 10:05

Dynamic Data Ranges in Excel

This is a question I get asked regularly when giving Excel courses: how to make a range of cells dynamic, so that when data change the range gets updated automatically?
Very useful indeed in pivot tables, vlookup scenarios, charts etc.
The answer lies in the combined use of a name for the range and the offset() formula, which creates a new range, but which you can feed with the count() or counta() formula.
First create your range name, then insert following formula:

=OFFSET(Prices!$A$1;0;0;COUNTA(Prices!$A:$A);COUNTA(Prices!$2:$2))

The first argument is the set off point, second and third arguments stand for the number of rows or columns the set off point should be calculated - in the example the cell A1, as no relocation takes place.
The fourth and fifth argument do the magic: use a simple count(), for numbers, or counta() for anything else, to dynamically calculate the number of rows and/or columns should be taken on board of your named range. You can take a whole column or row on board, as in the example, or just define a range big enough to contain all possible future values.
And that’s it basically…

PS Use commas or semicolons for the separation of the arguments asper your settings !

(0) Comments ·
11 December 2007 - 12:20

Excel charts issues, like the XYZ scattering and Cie

The beauty of teaching is actually that you learn yourself all the time. Each new course always has the potential of new discoveries, sometimes very obvious ones, sometimes at levels you’d never imagine beforehand.
I’ll leave the less obvious for another time now, but on the obvious side it’s quite simple: people ask questions ! (and I tend to provoke them a lot as I’m convinced is the best tool you have when teaching ).  Of course you don’t alway have the answer immediately and so of you go into some research…

The other day; one of my participants in an Excel course wondered about the possibility to have a third dimension in a XY scatter chart.... Oeps not immediately clear that was: how to create a XYZ scattered one?
So off I went onto the net, which brought me to a very interesting set of chart samples from someone called Andy Pope, a MVP - a most valuable professional indeed: http://andypope.info/charts.htm. He has an alternative for the XYZ scatter, as apparently Excel has some 3D charting deficiencies - and the XYZ scatter seems to be one of them. Learned this here actually, from the hand of another MVP, Jon Peltier, who ‘en passant’ also offers a world of valuable stuff on Microsoft Excel Charting FAQs .

And as one never comes along: another questioned concerned an issue with better management of labels on charts - suppose you just want to determine your own logic on labels.... well on the internet you’re never alone, wondering - and chances are real someone even has a solution for it: http://appspro.com/Utilities/ChartLabeler.htm (there are several actually, but just like this one and also works in Excel 2007).

(3) Comments ·
Page 1 of 1 pages