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, September 27, 2011 – Permalink –

Camera Tool

Smile!


(This is the per-2007 routine. For 2007-10 see the bottom of this tip. )

To create a linked picture of part of a spreadsheet for use elsewhere:
  1. Edit>Copy the Cell or Range
  2. Choose the target cell
  3. Hold down the Shift key and choose Edit>Paste Picture Link
If you're going to do this frequently, go to Tools>Customize. Choose the Commands tab and locate Camera in the Tools category. Drag to place the Camera button on your toolbar.

If the Camera tool is on your toolbar:
  1. Select the Cell/Range
  2. Click the Camera tool
  3. Move the cursor to the target position and click to insert the linked picture
To create a static picture from cells, or a chart to use in another part of your workbook, or another program:
  1. Clear cell gridlines if you do not want them displayed in your picture.
    (Click Options on the Tools menu, click the View tab, and then clear the Gridlines check box")

  2. On the worksheet or chart sheet, select the cells or click the chart you want to copy as a picture
  3. Hold down SHIFT and click Copy Picture on the Edit menu
    (For the best picture quality, make sure As shown on screen and Picture are selected)
  4. Click OK
  5. Select the worksheet or other document where you want to paste the picture
  6. Edit>Paste.
You can use the Picture toolbar to change the image. (to display the toolbar, right-click any toolbar and choose Picture) To paste information you've copied from another program as a picture in Microsoft Excel, hold down SHIFT and click Paste Picture or Paste Picture Link on the Edit menu. You can, also, create a dynamic linked text box by using the tool on the Drawing toolbar to place the object on the worksheet.
Now click to the Formula box. Type = and the click on the cell you wish to link.

Also see: Copy - Paste Methodology by Jon Peltier
 
The process is a little different in 2007-10. Either add the Camera tool to the Quick access toolbar, or just select the range and drop down the Paste options. Choose copy as Picture. Next pick a location and  choose Paste as picture.







See all Topics

Labels: , , ,


<Doug Klippert@ 3:23 AM

Comments: Post a Comment

  Tuesday, September 20, 2011 – Permalink –

OLAP Cubes

More dimensions than Star trek


When a company accumulates a great deal of information, it becomes un-wieldy to work with just basic Excel or Access databases.

There is a database concept called on OLAP cube (On-Line Analytical Processing).

This multidimensional collection of data can be thought of as a 3-D pivot table viewed from flat land.

MSDN:
Just What Are Cubes Anyway?
(A Painless Introduction to OLAP Technology)

OLAPReort.com:
What is OLAP


Wikipedia:
OLAP

See all Topics

Labels: , , ,


<Doug Klippert@ 3:41 AM

Comments: Post a Comment

  Friday, September 16, 2011 – Permalink –

Legacy Files from 2007

Go back


Read this article closely. If you work in a situation where you need to work with legacy (pre-2007) files, it may be handy.

If you do most of your work in 2007, I wouldn't bother.


"When you use Windows Explorer or the desktop to create a new 2007 Microsoft Office file, a new Office file is created in an XML file format (.dox or .xlsx). For example, this behavior occurs when you right-click the desktop, you point to New, and then you click Microsoft Office Word Document. By default, files that you create in the 2007 Office system are in XML file formats.

This article is about how to create legacy Office files, such as .doc files, .xls files, .ppt files, or .mdb files in the 2007 Office system. You can create legacy Office files without opening any Office applications. To do this, you must modify some settings. The modified settings will apply to all the users who log on to the computer."


See all Topics

Labels:


<Doug Klippert@ 3:47 AM

Comments: Post a Comment

  Tuesday, September 13, 2011 – Permalink –

VBA, Named Arguments

An easier read


Use named arguments for cleaner VBA code.


Most likely, you use positional arguments when working with VBA functions. For instance, to create a message box, you probably use a statement that adheres to the following syntax:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])


When you work the MsgBox function this way, the order of the arguments can't be changed.

Therefore, if you want to skip an optional argument that's between two arguments you're defining, you need to include a blank argument, such as:
MsgBox "Hello World!", , "My Message Box"


Named arguments allow you to create more descriptive code and define arguments in any order you wish. To use named arguments, simply type the argument name, followed by :=, and then the argument value.

For instance, the previous statement can be rewritten as:

MsgBox Title:="My Message Box", _
Prompt:="Hello World!"


(To find out a function's named arguments, select the function in your code and press [F1].)



See all Topics

Labels:


<Doug Klippert@ 3:56 AM

Comments:
You can also right click the function name and choose "Quick Info".
# posted by Anonymous JP : 6:24 AM
 
Post a Comment

  Sunday, September 11, 2011 – Permalink –

Zero 0

Zero is nothing


If a zero isn't worth anything, why show it?

Here is a Microsoft tutorial about how to deal with zilch:


  • Display or hide all zero values on a worksheet

  • Use a number format to hide zero values in selected cells

  • Use a conditional format to hide zero values returned by a formula

  • Use a formula to display zeros as a blanks or dashes

  • Hide zero values in a PivotTable report
Hide Zeros

See all Topics

Labels: , , ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment

  Tuesday, September 06, 2011 – Permalink –

What's new in Excel 2010

Different can be good


Microsoft has a Quick reference card that explains the highlights of the new features of Excel:

  • Improved ribbon

  • Access workbooks in new ways

  • Make fast, effective comparisons from lists of data

  • Sparklines

  • Create workbooks with more visual impact

  • Collaborate on workbooks in new ways
There are also other links.

What's New in Excal 2010

See all Topics

Labels: ,


<Doug Klippert@ 3:09 AM

Comments: Post a Comment