Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal









Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com


  Tuesday, August 25, 2009 – Permalink –

Charting Tools

Sample graphs


Ed Ferrero has a number of sample Excel charts and tools.

One he calls a Wind Rose:


How to build a wind rose. Converts compass directions to degrees and radians, and plots a nice-looking chart.



Ferrero Consulting Pty Ltd:
EdFerrero.com/Charting




See all Topics

Labels: ,


<Doug Klippert@ 3:05 AM

Comments: Post a Comment

  Saturday, August 22, 2009 – Permalink –

Self Help

Get started in the right direction


The Office of Technology Services of Towson University, located in Towson, Md., provides Self-Help Training Documents for many applications.

They are available for many levels of knowledge. They’re clean, clear, and concise.
  • Access

  • Adobe Acrobat

  • Dreamweaver

  • Excel

  • FrontPage

  • Microsoft Office Tools

  • Outlook

  • Outlook Web Access

  • PowerPoint

  • Publisher

  • Visio

  • Windows

  • Word Art

  • Word
Tech Docs




See all Topics

Labels:


<Doug Klippert@ 3:06 AM

Comments: Post a Comment

  Thursday, August 20, 2009 – Permalink –

Still More Functions

Never Enough


Laurent Longre Has put together an Excel add-in with 65 more functions.

Here are a few you might find useful:


CHBASE


converts a value from a base into another base.

EASTERDATE


date of Easter Sunday for a given year

FORMULATEXT


returns the formula of a cell

MMAX


returns the N highest numbers of a range or an array

NBTEXT


converts a positive number into spelled-out text (supports 13 languages)

UNIQUEVALUES


returns the unique items of a range or an array

MOREFUNC.XLL, 65 add-in worksheet functions




See all Topics

Labels: ,


<Doug Klippert@ 3:14 AM

Comments: Post a Comment

  Friday, August 14, 2009 – Permalink –

Digital Signatures

How do I know it's real?


If you find a need to provide some sort of certification that your document has not been tampered with and is the rel thing, you might consider a digital signature.

This Microsoft Support article discusses the process.
What is a digital certificate?

What is a digital signature?

What occurs when I use a digital signature?

What Word files can I sign?

How can I obtain a digital signature?
  • Method 1: Obtain a digital certificate from a certification authority
  • Method 2: Create your own digital certificate
    Description of digital signatures and code

    Here's information for Excel.

    Digital Signatures for Excel




    See all Topics

    Labels: , ,


    <Doug Klippert@ 3:58 AM

    Comments: Post a Comment

      Thursday, August 13, 2009 – Permalink –

    Scroll Restrictions

    Without Protection


    You can protect a spreadsheet so that data is shielded from inadvertent entries.
    There is another way to set up a scroll area that does not involve protection.
    Open the Control Toolbox (right-click any existing tool bar) and click on the Properties icon.

    In the Scroll Area text box, type the scroll area range, or type the defined Name for the range.
    To cancel the Scroll Area restricted range, clear the Scroll Area text box.



    If you are using Office 2007, the Property icon is on the Developers tab.



    Multiple areas can be selected using Protection, but only one area is allowed using the Scroll Property.





    See all Topics

    Labels: , ,


    <Doug Klippert@ 3:29 AM

    Comments: Post a Comment

      Monday, August 10, 2009 – Permalink –

    Military Clipart

    Thousands of items


    If you find the need for Armed Forces photos and art, here is the place to look.
    Regardless of your opinion about their present mission, the military does present a spectacular visage.



    "06/17/06 - An F/A-18E Super Hornet aircraft sits at the ready as storm clouds pass overhead aboard the Nimitz-class aircraft carrier USS Ronald Reagan (CVN 76) in the Philippine Sea June 17, 2006.
    (U.S. Navy photo by Photographer's Mate 2nd Class Aaron Burden)

    All of these files are in the public domain unless otherwise indicated. However, we request you credit the photographer/videographer as indicated or simply "Department of Defense."


    HqDA.Army.Mil - Clipart



    See all Topics

    Labels:


    <Doug Klippert@ 3:02 AM

    Comments: Post a Comment

      Wednesday, August 05, 2009 – Permalink –

    Large Text Files

    Split between worksheets


    While this problem is alleviated in Excel 2007 with its 1,048,576 rows by 16,348 columns, The old XL versions are still here.

    Text files with a large number of records are better handled in a program like Access. Having said that, there can be times that these lists must be imported into Excel. If the file has over 65,536 records, the data will not fit on a single worksheet.

    Here's a Microsoft Knowledge Base article with the macro code needed to bring oversized text data into Excel and split it into multiple worksheets:

    Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False

End Sub


    Importing Text Files Larger Than 16,384/65,536 Rows

    Notice the code about 17 lines from the bottom of the macro.

    'For xl97 and later change 16384 to 65536.

    Also, after import, the data must be parsed. Use Data>Text to columns.




    If you have not worked with macros before, Dave McRitchie has a tutorial:

    Getting Started with Macros and User Defined Functions




    See all Topics

    Labels: , ,


    <Doug Klippert@ 3:13 AM

    Comments: Post a Comment