Thursday, August 31, 2017

Tips and Formulae

Functions and Macros



I'm always looking for Excel sites. A fresh perspective can make the view more clear.
While he does approach from a Mac angle, the Excel world welcomes those of all persuasions.

J.E. McGimpsey's XL Pages

Here are some of the tips:

  • Remove internal worksheet/workbook protection
  • Why your sum is a penny off...
  • Highlight row without losing color formatting
  • Why use -- in SUMPRODUCT formulae
  • Using SUMPRODUCT() to calculate variable rates and commissions
  • Three-dimensional SUMIF()s
  • Bitwise Logical Functions
  • Sampling from a range
  • Summing every Nth column or row
  • Worksheet and Workbook names using the CELL() function




See all Topics

Wednesday, August 30, 2017

Color Code Appointments

Red letter day



Outlook 2002+ allows you to assign colors to appointments and meetings.
To format a single appointment:
  1. Simply right click on the event
  2. Select Label
  3. From here you can select from a variety of pre-selected colors
Appointments can also be Automatically formatted.
  1. Right-click a meeting entry and choose Automatic Formatting...
  2. Click on Add and name the Rule.
  3. Next, click Condition and set up a Filter such as having the word "Dr" in the subject or note field.
      In the future, whenever you set up an appointment that has "Dr" in the text, the entry will be color-coded.

      New Folders

       Also see:

    1. Slipstick.com To add color coding to Calendar items
"… if you have ten or fewer main categories that you use on a regular basis, you might want your label names to be the same as your categories. To change the default label names, go to the Edit menu from the Calendar view and click on the Labels option."
University of Wisconsin-Eau Claire: Color Coding Appointments - 2007 



See all Topics

Tuesday, August 29, 2017

Folder Tree

DOS is back


"To find out how many folders there are on your hard disk, you can open a Command Prompt and use the Tree command. You'll get a very nice looking graphical tree structure showing all the folders on your hard disk.

The only problem is that the display will scroll by your screen so fast and exceed the buffer size, so you'll never be able to see it."


Import Tree command into Word

(Open Command Prompt as Administrator. Rather than "Insert>File", open the file with Word and choose the MS-DOS format))



Tree command



See all Topics

Monday, August 28, 2017

Loop the Beginning

Then start the show


It can be effective to have an opening segment run before the actual presentation begins.

We all know how to set up a show that will run in kiosk mode until you hit escape.

Here are instructions about how to set up the loop so that you can seamlessly start the show without an interruption.

Creating & Running an Opening Loop



See all Topics

Sunday, August 27, 2017

World War I

Color pictures


"Louis Lumière had already invented instant photographic plates and the Cinematographe when, in late 1903, he and his brother Auguste patented a new process for producing colour photographs : the Autochrome.

Before the invention of the Autochrome, colours were separated using a complex three-colour process whereby three successive exposures had to be taken and then superimposed onto each other.

Louis Lumière, however, devised a method of filtering light by using a single three-colour screen made up of millions of grains of potato starch dyed in three different colours.

This mixture was then laid out on a varnished glass plate, which would be ready for use once it was coated in a black and white emulsion.

Developing the plate entailed applying the same process as was used for black and white photographs at the time, with the impression being processed to reversal.


Institut-Lumiere.org
Here are some examples:

"It looks like a painting by impressionist Edouard Manet, but it is a real color picture, made in 1914, by Jean-Baptiste Tournassoud, Commander of the Photography and Cinematography Section of the French Army.

When the Great War broke out, in 1914, French poilu's (common soldiers) still wore their Napoleontic uniforms with red trousers. They made perfect targets.

Here are some more:
World War I Photos

The Great War



See all Topics

Saturday, August 26, 2017

Access Field Highlighting

More code


This technique can also be applied to controls like option groups.

Instead of using OnGotFocus and OnLostFocus events you must use the OnEnter and OnExit events.

In addition, the control group's BackStyle property must be set to Normal to take advantage of the Windows color scheme:

Function Highlight(Stat As String) As Integer
Dim ctrl As Control
On Error Resume Next
Set ctrl = Screen.ActiveControl
If Stat = "GotFocus" Then
ctrl.BackColor = vbHighlight
ctrl.ForeColor = vbHighlightText
ElseIf Stat = "LostFocus" Then
ctrl.BackColor = vbWindowBackground
ctrl.ForeColor = vbWindowText
End If
End Function

Take advantage of global constants. Just add the following two statements to a module:

Global Const Orange = 39423
Global Const LightBlue = 16776960

Then, set the OnGotFocus and OnLostFocus events for the controls in the following format:
Private Sub controlName_GotFocus()
controlname.BackColor = Orange
End Sub
Private Sub controlName_LostFocus()
controlname.BackColor = LightBlue
End Sub

Highlight data on forms by using conditional formatting



See all Topics

Friday, August 25, 2017

Spies in the Mailbox

Who'd read my mail?



"Espionage and email: as the Pentagon discovered, the two often go hand in hand. Following the arrests of two U.S. servicemen suspected of spying, the Pentagon is broadening its investigation of possible espionage activities at Guantanamo Bay, Cuba. According to CNN, at least one of the security cases has involved suspicious emails that were discovered as part of a routine monitoring process."


OutlookPower magazine has an article on the problem:

Espionage and email: tips for managing your own email archives
By Ann James



See all Topics

Thursday, August 24, 2017

Tuva or Bust

Richard Feynman's Last Journey


By Ralph Leighton
ISBN 0-393-32069-3
W.W.Norton & Company, Inc. 2000, 1991


Tuva or Bust

There has been a lot made of the PowerPoint contribution to the failure of the Challenger shuttle (see Edward Tufte.)

Before that was the Columbia disaster. Richard Feynman found the problem with the "O" rings, He too complained about PowerPoint like presentations:
"Then we learned about bullets — little black circles in front of phrases that were supposed to summarize things. There was one after another of these little goddamn bullets in our briefing books and on the slides."

This book however is about something altogether different.
As a stamp-collecting boy always fascinated by remote places, Nobel Prize-winning physicist Richard Feynman was particularly taken by the diamond-shaped stamps from a place called Tannu Tuva. He hoped, someday, to travel there. In 1977, Feynman and his sidekick — fellow drummer and geography enthusiast Ralph Leighton — set out to make arrangements to visit Tuva, doing noble and hilarious battle with Soviet red tape, befriending quite a few Tuvans, and discovering the wonders of Tuvan throat-singing. Their Byzantine attempts to reach Tannu Tuva would span a decade, interrupted by Feynman's appointment to the committee investigating the Challenger disaster, and his tragic struggle with the cancer that finally killed him. Tuva or Bust! chronicles the deepening friendship of two zany, brilliant strategists whose love of the absurd will delight and instruct. It is Richard Feynman's last, best adventure.



Quote
"Sure enough, occupying a notch northwest of Mongolia was a territory that could well once have had the name Tannu Tuva.
"Look at this," remarked Richard, "The capital is spelled K-Y-Z-Y-L."
"That's crazy," I said. "There's not a legitimate vowel anywhere!"
"We must go there," said Gweneth.
"Yeah!" exclaimed Richard. "A place that's spelled K-Y-Z-Y-L has got to be interesting."



More Tuva:
Tuva Movies and Sounds
Friends of Tuva

Also:
Listen to the music of Tuva on this CD. Willie Nelson is on one track, but it does demonstrate two toned throat singing:

Tuva Throat Singing

Here's another great Tuva story:

Genghis Blues

" Paul Pena is a blind San Francisco blues singer who has played with the likes of John Lee Hooker and Jerry Garcia (he also penned "Jet Airliner," which Steve Miller covered). One night while listening to his shortwave radio, he picked up a Radio Moscow broadcast and heard the mesmerizing, gutteral sound of throat singing, which is peculiar to Tuva's region of upper Mongolian. Enthralled, he became a master of this obscure art form. Enter Friends of Tuva, a curious group that included Nobel Prize-winning physicist Richard Feynman, who likewise had become fascinated with Tuva. In 1993 they sponsored a San Francisco appearance by Tuvan singers. Pena was in the audience and met with the singers afterward. Pena so impressed the Tuvans that he was encouraged to come to Tuva and participate in its annual festival competition. Genghis Blues chronicles this incredible journey."




See all Topics

Wednesday, August 23, 2017

Location Indicator

Point to the spot


Here's a link to the code that produces conditional formatting on the fly to the cells in the current row and column.



Color banding location



See all Topics

Tuesday, August 22, 2017

BIOS Bias

A bootstrap script


Way down deep in the innards of a PC there lives a tiny control freak named the Basic Input/Output System.

And that's just the beginning.

"The BIOS software has a number of different roles, but its most important role is to load the operating system. When you turn on your computer and the microprocessor tries to execute its first instruction, it has to get that instruction from somewhere.
It cannot get it from the operating system because the operating system is located on a hard disk, and the microprocessor cannot get to it without some instructions that tell it how."

Here are a couple of links for more information:

How BIOS Works

Wikipedia-BIOS

What is BIOS



See all Topics

Monday, August 21, 2017

No Black Slide

End the show


After creating a presentation, save it as a PowerPoint show. This allows you to run the show and not be faced with all the paraphernalia that was used to create it.

One negative is that at the end of the show a black slide is displayed.

To eliminate this last distraction, so that the show will run and then just return to the desktop, Go to PowerPoint Options (Tools>Options or Logo PowerPoint options in 2007+).

Remove the check from "End with black slide". Resave and carry on.



This will hold for every show until the setting is changed. It is not saved with the file.


"To force the presentation to end without the black screen on every computer, add an action button or autoshape on your last slide within the presentation.

You can now set the . . .action setting to "end show". This will force your presentation to end without displaying the final black screen and end of slide show message.

123PPT.com



See all Topics

Sunday, August 20, 2017

Learn to Draw

No match cover required


Do you want to learn how to draw? Now you can online! Learn how to draw like an artist, from a professional artist. Begin by learning the fundamentals of drawing with easy to follow interactive instruction.

Learn how to draw a person and make it actually look like the person! Take drawing people further and learn how to draw a caricature of a person!

  • DRAWING BASICS
    You don't know how to draw? Begin learning how to draw.

  • DRAWING PEOPLE
    You know how to draw, but you can't draw people.

  • DRAW CARICATURES
    You want to draw funny people, but you don't know how?

LearntoDraw.com



See all Topics

Saturday, August 19, 2017

Slashed Zero

Oh!

ø

There is a discussion of the slashed zero at:
How to Insert a Slashed Zero (0 Overlaid with a /)

You can also download the Monaco font that has a slashed ø
(Monaco is an embeddable font)

Andale.ttf (Mono) has a dotted 0

Seagullscientific.com has a font called Crystal

Windows has a free font editor. Type eudcedit on the Start>Run line.
Vic Laurie has a description of the Private Character Editor- Eudcedit

You could also use the EQ field to create a strike through and assign it to an AutoCorrect entry.

{EQ \o (0,/)}

The easiest is, probably Alt+0216 or Alt+0248 It's a Latin "oh" with stroke, but it looks close.

The HTML character code is ø ø



See all Topics

Friday, August 18, 2017

Zeros - Before and After

Nothing's a problem



"When you import data into Microsoft Access, trailing zeros may be lost. This will happen when you import data that is formatted to show these zeros, but where the zeros are not actually part of the data.
For example, in a Microsoft Excel workbook, you can format the number 1234 so that it will be displayed as 1234.000. When you import this workbook into a Microsoft Access table, the number will be displayed as 1234.
This article shows you how to preserve trailing zeros when you import data into Microsoft Access."


How to Preserve Trailing Zeros When Importing Data
Also:

Word — Decimal Point or Trailing Zeros Missing When You Merge Microsoft Access Database

Excel — Using a Custom Number Format to Display Leading Zeros



See all Topics

Thursday, August 17, 2017

Document Panes

Divided Doc


To divide the view of a document into panes, use the tiny divider mark in the upper-right corner of the document window.

It's above the up arrow at the top of the vertical scroll bar at the right side of the window.

Click on the marker and drag it to where you want the document window divided.

The size of the panes can be adjusted by dragging the divider up or down.

You can look at the top and bottom at the same time, or have one pane in Page View and the other in Outline View

Double click the marker to return to the full view.



See all Topics

Wednesday, August 16, 2017

Link to Word

Excel to Word connection


The Insert Hyperlink dialog will allow you to link to any file.

To link to a particular bookmark in a Word document, you can specify the bookmark by adding it yourself, adding a # (pound sign) plus the bookmark name to the path and file name.

C:\My Documents\MyDocument.doc#MyBookmark

To link to a page in a Word document, add a pound sign and the page number after the document path and file name:

C:\My Documents\MyDocument.doc#4

See Jon Peltier's hyperlink collection:
Hyperlinks



See all Topics

Tuesday, August 15, 2017

DNS Cache

Down the drain


Internet connection problems can, sometimes, be traced to a corrupted DNS cache.

Flushing this cache is an easy fix to many of these problems.

Here is how to fix that corrupted DNS cache.


  1. Click the Microsoft Start logo in the bottom left corner of the screen

  2. Click All Programs

  3. Click Accessories

  4. Right-click on Command Prompt

  5. Select Run As Administrator

  6. To view the DNS cache, type ipconfig /displaydns at a command prompt.

  7. In the command window type the following and then hit enter: ipconfig /flushdns

  8. You will see the following confirmation:

    Windows IP Configuration
    Successfully flushed the DNS Resolver Cache
    .




See all Topics

Monday, August 14, 2017

Presentation Review

Suggestions included


... (the) CEO of Whole Foods Market, John Mackey, gave a presentation called "Past, Present, and Future of Food" for an audience of 2000 in Berkeley, California.

... (he) was there to make a presentation and have a conversation that would . . . (show) a skeptical Berkeley audience that his large company still has the credibility to lead the food movement into the future.

. . .(the) 45-minute talk "aided" by 67 text-filled slides followed by an on-stage conversation

. . . Most people felt that the evening generally was successful given Mackey's sincerity, honesty, and general likability, but John Mackey's "multimedia presentation" as it was billed, could have been so much more.
----------------------------------------------------------------------------------------------------------------
. . . (the) presentation in Berkeley is a wonderful example of a presentation by an intelligent, personable, and passionate leader that easily could have been insanely great but was not. "[He] raced through the slides like a Ph.D. student presenting his dissertation," said the UC Berkeley reporter in the audience.

. . . it's a shame the presentation itself was not better planned and delivered given the importance of the topic and the profile of the speaker. Frankly, when you're trying to change the world, there is no excuse for being dull.
  • It's a story. This topic screams "Story" yet there was no story that I could follow.
    There were bits and pieces (some of it interesting) and way too much history and data-without-purpose.

  • Make it shorter. Cut the presentation part of the evening to 20-25 minutes and spend more time discussing on stage with the host, taking questions from the audience, etc.

  • Make it visual. There are no boring topics, but this topic is especially interesting and provocative.
Signal vs. Noise



See all Topics

Sunday, August 13, 2017

Unkept Secrets

From Microsoft

"After supporting Microsoft Excel for years, technical Support Professionals have found that some of the most powerful and useful features and functions in Microsoft Excel remain undiscovered by you, our users.

For example, you may create a new macro to perform a calculation when an existing formula or function can perform the task. Or, you may create a new macro to perform a task when you could use an existing feature that performs the task."

Undiscovered Tips About Microsoft Excel for Windows
Here are just a few:
  • Secret #1: Joining Text Together

  • Secret #3: Excluding Duplicate Items in a List

  • Secret #7: Using a Data Form

  • Secret #11: Linking a Text Box to Data in a Cell

  • Secret #12: Linking a Picture to a Cell Range

  • Secret #17: Using OFFSET to Manipulate Data in Cells that are Inserted

  • Secret #21: Using INDEX and MATCH to Look up Data

  • Secret #25: Returning Every Nth Number

  • Secret #29: Using One Keystroke to Create a new Chart or Worksheet

  • Secret #30: Setting up Multiple Print Areas on the Same Worksheet




See all Topics

Saturday, August 12, 2017

Ripple the Ribbon

Change the look


"Learn how you can create a custom Office Fluent Ribbon for an Access 2007-13 database by using only Office Fluent extensibility markup XML and macros.

Discover how to create a command space without writing any code and also learn about more advanced scenarios that require code."

Customizing the Office Fluent User Interface



Customize the Ribbon



See all Topics

Friday, August 11, 2017

Attachment Default Save

Set the solution



Here's how to set the default location that Outlook will use to save attachments:

  1. Click Start, and then click Run.

  2. In the Open box, type regedit, and then press ENTER.

  3. In Registry Editor, locate the following subkey in the registry:

    HKEY_CURRENT USER\Software\Microsoft\Office\11.0\Outlook\Options 

  4. On the Edit menu, point to New, and then click String Value.
  5. Type DefaultPath, and then press ENTER.

  6. Double-click the DefaultPath value.
    In the Edit String dialog box, in the Value data box, type the path, including the drive letter, to the folder that you want to use for your Outlook saved items, and then click OK.

  7. Exit Registry Editor.
Office.Microsoft.com



See all Topics

Thursday, August 10, 2017

New Excel Web Grabber

Web Toy


"The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel. The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option.

The system extracts data by learning from a user's selection of data they wish to capture into Excel. The more selections, the more the system is trained.
An example scenario: You wish to import and track data from MSN's weather page. Visit the site using the tool, enter Data Capture mode, and select a row or two of data from the table. Then click Select Similar, and the system will find similar data based on your previous selections.

You then can click Import and leverage Excel's rich data-editing capabilities, including the Refresh command, which will revisit the Web page and extract potentially new, updated data."

Web Data Add-In

(Webservices 2013)

From the Excel Blog team



See all Topics

Wednesday, August 09, 2017

Icon Tile Opacity

See through


To make the background of desktop icons transparent, go to:

Control Panel > System > Advanced

Click Settings in the Performance section.



On the Visual Effects tab, check "Use drop shadows for icon labels on the desktop".



See all Topics

Tuesday, August 08, 2017

Secret Slide Numbers

PPT does not forget


"PowerPoint numbers slides in several ways and it pays to know the difference.

When you create a new slide, it gets a unique SlideID, a unique number that's read-only… you can't change it manually or programmatically. Reordering the slides won't cause it to change. Once a slide is created, it keeps the same SlideID forever.

SlideIndex is the ordinal number of the slide in the presentation as it's currently arranged. Move a slide around in slide sorter and its SlideIndex changes to reflect its new position in the show."

Slide number, SlideID, SlideIndex and all that jazz

From Informit.com:
Sample Code to Print Slide Numbers for a Custom Show


"Microsoft PowerPoint has the ability to create custom slide shows, which are subsets of existing slides within your presentation. When you print a custom show, PowerPoint prints the page number defined for that slide. For example, if you print a custom show named My Show that consists of slides 2, 8, and 13 of your presentation, the numbers 2, 8, and 13 appear on the printed output.

This article provides a sample Microsoft Visual Basic for Applications macro that prints out a specified custom show and numbers the pages consecutively, beginning with the number 1."

Also:
Working with Slide Objects
and

Microsoft PowerPoint Objects



See all Topics

Monday, August 07, 2017

Themes and Templates

Need a look?



Themes are a collection of design elements and color schemes that can be applied to your web site to give it a unified appearance.

Templates include themes and layout modules to aid in quickly creating a web site. Templates can be purchased through the web, but here is one of the locations that provide free design help.

FreeLayouts.Livejournal.com
"Want a great new look for your website? Free Layouts.com offers you just that - select from our huge database of complete "look and feel" designs. Professional, sports, technical and others - they are all here. Free Web Templates are available for you to download now.

Look around and download your favorite page layouts - for free. Also, submit templates of your own and increase traffic to your website greatly. Currently over 500 free HTML templates and 50 Flash templates for you to download!"




See all Topics

Sunday, August 06, 2017

Highlight the Current Control

Code vs. property


Many users have trouble knowing which text box on a form they're currently working with. One way to make it clear for users is to highlight the current one, for example, with a yellow background.
Access allows you to do this with conditional formatting, but you can also get a similar result using code.

To do so, create a new module and add the following code:

Function Highlight(Stat As String) As Integer
Dim ctrl As Control
On Error Resume Next
Set ctrl = Screen.ActiveControl
If Stat = "GotFocus" Then
ctrl.BackColor = 65535
ElseIf Stat = "LostFocus" Then
ctrl.BackColor = 16777215
End If
End Function

Save and close the module, then open the form you want to apply the highlighting to in Design view.
Click the Code button and insert

Highlight("GotFocus")

in each text box control's GotFocus event procedure. Likewise, add

Highlight("LostFocus")

to each text box's LostFocus event procedure.
When you've finished,save the changes, close the VBE, and switch to Form view.

When you tab to a field, it's shaded yellow. When you tab away from the field, its background is restored to white.



See all Topics

Saturday, August 05, 2017

Wage is Too Minimum

Low pay by state


Since 1997, the federal minimum wage has been stuck at $5.15. The new Congress plans to introduce legislation raising the minimum wage to $7.25-an increase that is long overdue.

This minimum wage increase would boost earnings for 13 million American workers-9.8 percent of the United States workforce.

Six million families with children-46 percent of the total low wage-earning families with children-currently receive all of their earnings from minimum wage jobs.

Raising the minimum wage will increase annual earnings to $15,000 from $10,700.

Without this increase, a family of three supported by one minimum wage earner will live roughly $5,400 below the federal poverty line.

At the 350 largest public companies, the average CEO total direct compensation was $11.6 million in 2005. At this rate of compensation, it takes the average CEO only one hour and 55 minutes to earn the annual pay of a minimum wage worker.

Here is an interactive map that will show how your state relates to the others.

Minimum wage map



See all Topics

Friday, August 04, 2017

Calendars - Perpetual

It's that year again


One Month and One Year perpetual calendars are available.

These Excel file calendars do not use macros and can be used in OpenOffice or other Excel compatible software.

  • Perpetual calendars with Week starting on Sunday

  • Perpetual calendars with Week starting on Monday

  • A simple "universal" one month calendar that will update for any month and any year (after 1900) just by changing a date cell.

  • A "universal" one year calendar that will show 12 months starting with a user defined Month and Year.
Start dates can be at the beginning of any year and any month.

DotXLS.com: Perpetual calendars
 
Microsoft: How to make calendars



See all Topics

Thursday, August 03, 2017

Custom Forms

Template collection



"At one time, Microsoft had a pretty nice collection of custom Outlook forms anyone could download. While a number of the forms are no longer available from Microsoft, a sizable collection remains. You can use these as-is or as a basis for your own custom forms."

See Slipstick.com - Sample Forms for the forms that are available.
  • Calendar Corporate Calendar
  • Expense Report
  • Help Desk
  • Holiday Card List
  • Job Candidates
  • Purchase Order Request
  • Real Estate Listings
  • Software Inventory Form
  • Student Attendance
  • Technical Repair Request
  • Time Card
  • Travel Request
  • Vacation Request
  • Web Site Listing
While they were all designed for Outlook in the Outlook 97/98 time frame, every one I've tried works with all versions of Outlook. However, the self-installers may not work due to security settings and you'll need to publish the form yourself. If you need step-by-step help to publish a form, see:
Outlook.net - Publish Form

OutlookCode.com - Send Form  

OutlookCode.com - Microsoft Outlook Custom Forms



See all Topics

Tuesday, August 01, 2017

Equations = Word 2007, 2010, 2013 and 2016

Real text


Remember, back in the old days of pre-2007-10 Word. Equations were a pain. Messy to set up with the Equation Editor and difficult to edit and reformat.


Brace {yourself} there have been changes.



  • Equations are regular Word text (not objects), so they're integrated with regular Word formatting.

  • High-quality display and typography: we're using TeX standards and a brand-new Math Font to produce great-looking equations

  • Two input methods: UI and Linear Format (keyboard syntax)

  • MathML support: now you can write an equation in Word, paste it into a calculating or graphing application, and get results!

Equation Demo
 
Equations in Word 2007, 2010, 2013 and 2016



See all Topics

Classroom Ideas

Ideas and tutorials



If you're looking for student projects and tutorials involving PowerPoint and more, look here:

PowerPoint - Creating Classroom Presentations

More information is located at:
EducationWorld.com

  • a search engine for educational Web sites only, a place where educators can find information without searching the entire Internet;
  • original content, including lesson plans, practical information for educators, information on how to integrate technology in the classroom, and articles written by education experts;
  • site reviews;
  • daily features and columns;
  • teacher and principal profiles;
  • Wire Side Chats with the important names in education;
  • employment listings.




See all Topics