Book

Suggestions

Delivered by FeedBurner

Bloglines

1906
2006

OfficeZealot

Scobleizer

TechRepublic

SpyJournal

 Web http://www.klippert.com

Tuesday, December 22, 2009 – Permalink –

### Tie them together

Excel is a flatfile database, but you can do some Access kinds of relationships.

"A link is a formula that gets data from a cell in another workbook. When you open a workbook that contains links (a linking workbook), Microsoft Excel reads in the latest data from the source workbook or workbooks (updates the links).

Use links when you want to maintain information in a single workbook, but also use the information in other workbooks. For example, if your product prices change frequently, you might keep a master price list.
Other workbooks that use the price data in calculations, such as purchase orders, inventory valuations, or sales estimates, can create links to the price list workbook so calculations always use current prices. When prices change, you only have to enter the new prices in one place."

1. Open both workbooks.

2. In the source workbook, select the cells you want to link to and click the Copy button.

3. Switch to the destination workbook and click the upper left cell of the range where you want the links.

4. On the Edit menu, click Paste Special, and then click Paste Link.

See all Topics

Labels: ,

<Doug Klippert@ 3:10 AM

Sunday, December 06, 2009 – Permalink –

## Curves

### and More

Famous curves from Cal State at LA. All your favorites from Astroid to Witch of Agnesi.

Here is a collection of Functions relating to astronomy from Stargazing.net.

Can't tell who might be interested in the obliquity of the equator given date in days after J2000.0. See: Astro VBA

Other Curve stuff:

DelphiForFun.org: converting polar coordinates to Cartesian coordinates.

"Students of analytic geometry, (the kind that combines algebra and geometry), often work in one of two coordinate systems: Cartesian or Polar - and frequently must convert from one to the other.

The Cartesian system locates points on a plane by measuring the horizontal and vertical distances from an arbitrary origin to a point. These are usually denoted as a pair of values (X, Y).

The Polar system locates the point by measuring the straight line distance, usually denoted by R, from the origin to the point and the angle of an imaginary line from the origin to the point, θ, (Greek letter Theta), measured counterclockwise from the positive X axis."

See all Topics

Labels: , ,

<Doug Klippert@ 3:02 AM

Sunday, June 14, 2009 – Permalink –

## Julian Dates

### Gregorian to/from Julian

Julian dates refer to the number of days from the first of the year and the number of days until the end of the year.

The year -45 has been called the "year of confusion," because in that year Julius Caesar inserted 90 days to bring the months of the Roman calendar back to their traditional place with respect to the seasons. This was Caesar's first step in replacing a calendar that had gone badly awry. Caesar created a solar calendar with twelve months of fixed lengths and a provision for an intercalary day to be added every fourth year. As a result, the average length of the Julian calendar year was 365.25 days.

The Gregorian (Pope Gregory XIII) calendar is based on a cycle of 400 years, which comprises 146,097 days. Since 146,097 is evenly divisible by 7. Dividing 146,097 by 400 yields an average length of 365.2425 days per calendar year, which is a close approximation to the length of the tropical year. The Gregorian calendar accumulates an error of one day in about 2500 years.

Calendars by L. E. Doggett

From Chip Pearson's site CPearson.com:

"Many applications (especially mainframe systems) store dates in the Julian format, which is a 5-digit number, consisting of a 2-digit year and a 3-digit day-of-year number. For example, 24-August-1999 is stored as 99236, since 24-August is the 236th day of the year. Excel does not support Julian dates directly, but you can use them with only a few fairly simple formulas.

### Converting A Standard Date To A Julian Date

The formula below will convert a standard Excel date in A1 to a Julian Date.

`=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")`

This formula takes the 2 right-most characters of the YEAR of the date in A1, and then appends the number of days between the date in A1 and the 0th day of that year. The TEXT function formats the day-of-year number as three digits, with leading zeros if necessary.

### Converting A Julian Date To A Standard Date

The formula below will convert a Julian date to a standard Excel date.

`=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))`

If the year digits of the Julian date are less than 30 (i.e., 00 to 29), the date is assumed to be a 2000 century year. If the year digits of the Julian date are greater than or equal to 30 (i.e., 30 to 99), the date is assumed to be a 1900 century year. This formula works by taking advantage of the fact that the DATE function can handle days beyond the "normal" days in a month. For example, DATE correctly computes 100-Jan-1999 to be 10-April-1999.

These Julian dates must have the leading zero or zeros for years between 2000 and 2009. For example the 123rd day of 2000 must be entered as 00123. Format the cell as TEXT before entering the data, or enter an apostrophe before the Julian date -- e.g., '00123. This will prevent Excel from treating the Julian date as a number and suppressing the leading zeros."

US Naval Observatory has this definition (and a calculator):

Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost 2.5 million days have transpired since this date.

April 29, 2004 at 6:00 AM would be 2453854.75

See all Topics

Labels: ,

<Doug Klippert@ 3:43 AM

Tuesday, February 10, 2009 – Permalink –

## Show the star

You can tie the result of a cell to an AutoShape.

This displays the value in a more dramatic manner.
1. Create an AutoShape on the Worksheet
2. With the shape selected, type an equal sign in the formula bar.
3. Enter the address of the linking cell (or click the cell)
4. Hit Enter
Thanks to AutomateExcel.com for the reminder.

See all Topics

Labels: , ,

<Doug Klippert@ 3:21 AM

Tuesday, May 13, 2008 – Permalink –

## Stick it in Word

You might like to distribute a short PowerPoint slide show, and include some extra material.

Open Word and PowerPoint.

Arrange the windows so that both applications can be seen.
(Right-click an empty area of the Task bar and choose "Tile Windows Vertically."

Type your introductory text in the Word document.

Switch to PowerPoint and open the PowerPoint file.

In Slide Sorter View, hold down the Ctrl key and select the slides you want to include.

Drag the selected group of slides onto the Word document.

You will only see the first slide in the document, but if you double-click on the image, the PowerPoint show will run.

It will also work in Excel.

(This, of course assumes that the target machine has PowerPoint or PowerPoint Viewer installed)

See all Topics

Labels: , ,

<Doug Klippert@ 6:23 AM

Sunday, March 09, 2008 – Permalink –

## Smile!

(This is the per-2007 routine. For 2007 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. 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@ 8:15 AM

Thursday, October 18, 2007 – Permalink –

## Spokes of the web

There's a lot of information out there. The problem is how to find it.
Here is a site that contains links to Excel information arranged in topics:

• Excel Help

• Excel Templates

• Excel Tips & Tricks

• Excel Tutorials

• Excel VBA

See all Topics

Labels: , ,

<Doug Klippert@ 8:03 AM

Thursday, May 31, 2007 – Permalink –

## 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`