The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,952 other followers

Archive for the ‘Office 2003’ Category

Learned that figuring out Excel formula dependencies has been there since at least Excel 2000 (:

Posted by jpluimers on 2022/03/01

Sometimes, you figure out Excel functionality you have never needed before, but has been there for decades.

A while ago, I had a very complex with formulas referencing full columns back and forth when suddenly I got into something strange: when reloading the spreadsheet, values would not appear because of recursion errors. Before saving this was fine, so it was hard to track back where I want wrong.

So I was happy to find out that Excel has two cool features for this:

  • Trace Precedents
  • Trace Dependents

Heck, looking at the icons I had a feel these features had been there for a long time. Boy, was I surprised to find them in [Wayback] Excel 2000 – Student Edition – Complete (a great book by the way), as you can see in this picture:

Excel 2000 - Student Edition - Complete - Trace precedents, dependents, error

Excel 2000 – Student Edition – Complete – Trace precedents, dependents, error

As others can explain this feature so much better than I can, here are some links:

–jeroen

 

Posted in Excel, Office, Office 2000, Office 2003, Office 2007, Office 2010, Office 2011 for Mac, Office 2013, Office 2016, Power User | Leave a Comment »

Random, Quick Brown Fox and Lorem Ipsum – How to Add Filler Text in Microsoft Word Documents

Posted by jpluimers on 2021/06/07

Choices:

  • =lorem(...): Ramdom Lorem Ipsum text paragraphs.
    • The sentences are roughly between 0.25 and 1.0 document widths in length.
  • =rand(...): Random English help text like paragraphs.
    • The sentences are roughly between 0.5 and 1.5 document width in length.
  • =rand.old(...): “The quick brown fox jumps over the lazy dog.” paragraphs (up until Word 2003, this was what rand(...) returned).
    • The sentences are always the same length.

(...) parameters:

  • (x,y): x paragraphs of y sentences of text
  • (y): y paragraphs of 3 sentences of text (lorem more towards 1, rand more towards 3)
  • (): depends on the function:
    • =lorem(): 5 paragraphs of 3 sentences of text
    • =rand(): 5 paragraphs of 3 sentences of text
    • =rand.old(): 3 paragraphs of 3 sentences of text

Note:

  • There is no way to specify x paragraphs of random number lines of text.
  • They need to have “Replace text as you type” enabled (see menu option “File” -> “Options” -> “Proofing” -> “Autocorrect Options”)
  • Word 2003 and lower only have =rand(...)

Based on:

Related: [WayBack] 10 awesome Lorem Ipsum alternatives – Justinmind via [WayBack] rand () in word – Microsoft Tech Community – 325554

For a random document demo, I usually do this:

  1. =lorem(100,1) to get 100 paragraphs of 1 line of text
  2. About every 10 paragraphs, I mark a paragraph with a relevant Heading style
  3. Inside the remaining text, I combine some paragraphs to get longer ones

–jeroen

Posted in Office, Office 2003, Office 2007, Office 2010, Office 2013, Office 2016, Power User, Word | Leave a Comment »

Displaying Full Message Headers and Source in Microsoft Outlook (Windows) – via: Yale ITS

Posted by jpluimers on 2015/12/07

Showing message headers and source used to be easy in early Outlook versions.

But as of Outlook 2007 they hid the internet message headers even further away than in Outlook 2003.

Steps for Outlook 2007+:

  1. Start Outlook.
  2. Double-click the message for which you want to view full internet headers.
  3. Click Options (2007) or Tags (2010/2013).
  4. The Message Options dialog box is displayed. You are after the Internet headers field at the bottom of the dialog box.

Same for the message source:

  1. Start Outlook
  2. Double-click the message for which you want to view full internet headers.
  3. In the Move section of the Ribbon, click on Actions
  4. Click Other Actions
  5. Click View Source
  6. Notepad (or the program associated with html source files) opens with a file email.txt containing the message source.

–jeroen

via:

Posted in Office, Office 2003, Office 2007, Office 2010, Office 2013, Outlook, Power User | Leave a Comment »

How to aggregate (count/sum/average) cells and ignore the #div/0! ‘s – via: list of functions by Excel version

Posted by jpluimers on 2015/11/19

I bumped into a #DIV/0! result for average functions when processing large sets of data.

It is actually very easy to spot the error in small results, sets, but hard in big ones, as you cannot see the #DIV/0!

So there are average functions that can ignore certain outcomes. COUNT already does that (there is no COUNTIF), the others have a *IF equivalent, but not in all Excel versions:

Note there is a small SUMIF/SUMIFS/AVERAGEIF/AVERAGEIFS in Excel 2010 (not in 2007, and maybe not in 2013) glitch when the criteria are in a different sheet.

The seemingly easy workaround of summing columns A and B, then doing the division fails: it returns different results as it forgets to ignore faulty rows:

SUM/AVERAGE versus SUMIF/AVERAGEIF (click to enlarge)

SUM/AVERAGE versus SUMIF/AVERAGEIF (click to enlarge)

Leermomentje (techable moment comes close)…

–jeroen

via:

Posted in Development, Excel, Office, Office 2003, Office 2007, Office 2010, Office 2013, Power User, Software Development | 2 Comments »

Changing a TextBox annotation in an MDI file

Posted by jpluimers on 2015/08/28

Even though MDI files have been abandoned for a while (you can only install it for Office XP, 2003 and 2007), sometimes I need to do some editing of TextBox annotations.

This is not intuitive: you have to right-click the textbox in order to edit it.

Printing in Office 2003 also was non-intuitive: Ctrl-P would crash the Microsoft Office Document Imaging MDI editor with a Microsoft Crash Report. But mouse-clicking the print-icon works. I remember having sending dozens of these reports to Microsoft around 10 years ago, but it never got fixed.

–jeroen

Posted in Microsoft Document Imaging, Office, Office 2000, Office 2003, Office 2007, Power User | Leave a Comment »

 
%d bloggers like this: