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, October 31, 2017 – Permalink –

Calculate Running Total

Using the OFFSET function


Adding up a running balance can be frustrating when new data is added or old transactions are removed.
"How to create a data list to manage transactions, add and delete rows from the list, and accurately calculate a running balance using the OFFSET function."
Cash flow using OFFSET.PDF

Office.Microsoft.com:
Calculate a running total


See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:13 AM

Comments: Post a Comment


  Thursday, October 26, 2017 – Permalink –

Insert Line Breaks With Code

Label Captions


If you've ever needed to insert line breaks in a message box prompt, you most likely built a string that incorporated a line feed or carriage return character. Unfortunately, label objects aren't as forgiving when it comes to using these characters.

If you're setting a label's Caption property with code, you'll find that the special control characters are interpreted as squares, since they're otherwise un-displayable.
To successfully insert a line break in a label caption, you need to include both a line feed character and a carriage return character, entered consecutively.

To do so, you can use the Chr() function, such as:

Me.Label1.Caption = "Line 1" & _
Chr(13) & Chr(10) & "Line 2"

However, you can also simplify your code using an built-in constant:
Me.Label1.Caption = "Line 1" & vbCrLf & "Line 2"



See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:08 AM

Comments: Post a Comment


  Monday, October 23, 2017 – Permalink –

Office Art

2007+ choices


Office 2007+ uses OfficeArt to format text boxes, graphics and pictures.

It's available in Word, Excel , and PowerPoint, but it is most active in PowerPoint and Excel.

Here's a description:

Office PPT Art



See all Topics

Labels: , , ,


<Doug Klippert@ 3:49 AM

Comments: Post a Comment


  Thursday, October 12, 2017 – Permalink –

Specifications for Excel 2017-2016

More flexibility


Lots of limits have been changed with 2007. The size of a worksheet is now 1,048,576 rows by 16,384 columns. The number of undo levels has gone up to 100.

Here are some more changes:

  • Worksheet and workbook specifications and limits

  • Calculation specifications and limits

  • Charting specifications and limits

  • PivotTable and PivotChart report specifications and limits

  • Shared workbook specifications and limits

Excel 2007-2013 Specifications


See all Topics

Labels: ,


<Doug Klippert@ 3:26 AM

Comments: Post a Comment


  Sunday, October 08, 2017 – Permalink –

Undo Excel

Level talk


In Excel 2007+. the number of levels of the "undo stack" was increased from 16 levels to 100.

Setting AutoFilters, showing/hiding detail in PivotTables, and grouping/ungrouping in PivotTables are now reversible.

And the undo stack is not cleared when Excel saves, be it an AutoSave or a Save by the user.

If you think the number of undos should be changed, here's how:


  1. Close any programs that are running.

  2. Click Start, click Run, type regedit in the Open box, and then click OK.

  3. In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:

    Microsoft Office Excel 2010
    HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

    Microsoft Office Excel 2007 uses Office\12.0\
    Microsoft Office Excel 2003 uses Office\11.0\
    Microsoft Excel 2002 uses Office\10.0\
    Microsoft Excel 2000 uses Office\9.0\


  4. On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.

  5. On the Edit menu, click Modify.

  6. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.

  7. Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.


Modify the number of undo levels
 

If you want to clear the undo stack, just run a macro such as:


Sub ClearUndo()
Range("A1").Copy Range("A1")
End Sub

Allen Wyatt:
Clearing the Undo stack


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:14 AM

Comments: Post a Comment


  Tuesday, September 12, 2017 – Permalink –

Data Validation

Control input



Mark Rowlinson provides a discussion by Kid Van Ouytsel that does an excellent job explaining data validation. He has also constructs a sample workbook that you can download and play with.

Data Validation
"Data validation is a tool that can help you control the input/changes someone can make in a spreadsheet. It can help you or your users to make choices, guide them to make relevant input/changes, or restrict input to a specific type of data or structure. It can help you or your users to save time and to keep formulae working properly."

Also:

Contextures:
Excel -- Data Validation

Ozgrid:
Data Validation and Conditional Formatting

Microsoft Office Online:
Overview and samples of data validation


See all Topics

Labels: , , ,


<Doug Klippert@ 3:41 AM

Comments: Post a Comment


  Wednesday, September 06, 2017 – Permalink –

Move a Column

No Cut, No Paste


With a little practice you can quickly move columns or rows.

  1. Select the entire column or row you want to move
    The move will effect whatever is selected; cells, or columns, or rows

  2. Move your mouse pointer to the edge of the selection until it changes from a normal pointer cross to an arrow (four headed arrow in 2007+).
    (Put the pointer on the selected cells, not the Row or Column indicators.)

  3. Hold down the shift key and click the left mouse button and drag the column/row to the new position.

  4. Release the mouse button before releasing the shift key



See all Topics

Labels: , , , ,


<Doug Klippert@ 3:21 AM

Comments: Post a Comment


  Saturday, September 02, 2017 – Permalink –

Indent Code

Realign a bunch


Indenting blocks of VBA code, such as statements within loops or If...Then statements, makes reading a procedure much easier.

You probably indent a code statement using the [Tab] key, and outdent by using [Shift][Tab].

However, you may not be aware that the [Tab] and [Shift][Tab] techniques also work when multiple code lines are selected.

The Visual Basic Editor also provides Indent and Outdent buttons on the Edit toolbar that allow you to easily reposition blocks of code.


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:35 AM

Comments: Post a Comment


  Thursday, August 31, 2017 – Permalink –

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

Labels: , , , , , ,


<Doug Klippert@ 3:15 AM

Comments: Post a Comment


  Wednesday, August 23, 2017 – Permalink –

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

Labels: , , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Friday, August 18, 2017 – Permalink –

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

Labels: , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Wednesday, August 16, 2017 – Permalink –

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

Labels: , , , ,


<Doug Klippert@ 3:48 AM

Comments: Post a Comment


  Sunday, August 13, 2017 – Permalink –

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

Labels: , , ,


<Doug Klippert@ 3:24 AM

Comments: Post a Comment


  Thursday, August 10, 2017 – Permalink –

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

Labels: , , , ,


<Doug Klippert@ 3:57 AM

Comments: Post a Comment


  Friday, August 04, 2017 – Permalink –

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

Labels: , , , ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Monday, July 31, 2017 – Permalink –

Drop Down Menu

Validation



You can create a dropdown list that gets its choices from entries located in another part of the spreadsheet.

  1. Type the entries in one column or row.

    You can name the list by selecting the entries and then clicking in the Name box on the Formula bar and entering a name, and then press Enter.

  2. Next, select the cell where you want the dropdown list.

  3. On the Menu bar, go to the Data menu, click Validation, and then click the Settings tab.
    In the Allow box, click List.

  4. Enter the name of the list or its location.

    Make sure the reference or name is preceded with an equal sign (=).

    Make sure the In-cell dropdown check box is selected.

    If your list is short, you can type the entries directly in the Source box, separated by commas.
Debra Dalgleish, Excel MVP has a complete discussion on her Contextures.com site. She covers resizing the dropdown box. The site also includes other information about data validation:
Excel — Data Validation —Tips  


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:25 AM

Comments: Post a Comment


  Wednesday, July 26, 2017 – Permalink –

Hide Dups

Format don't show



Duplicate entries can be formatted to "disappear", but still be available for computation.
  1. Select the range

  2. Goto Format>Conditional Formatting

  3. Select Formula Is

  4. Enter
    =A2=A1

  5. Click the Format button.

  6. Select a font color to match the cell background color.

  7. Click OK and OK
Dups can also stand out:
  1. Select the range

  2. Go to Format>Conditional Formatting

  3. Choose Formula Is

  4. Enter
    =COUNTIF($A$2:$A$100,A2)>1

  5. Click the Format button.

  6. Select a font or background color for highlighting.

  7. Click OK and OK
Hide Duplicate Values

Also:
Hide Records with Duplicate Cell Entries


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Wednesday, July 12, 2017 – Permalink –

Numbers to Words

Cardinal numbers



From the Microsoft Knowledgebase collection:

How to Convert a Numeric Value into English Words

This support article contains the VBA code needed to create a User Defined Function, You can, then, change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering something like the following formula into a cell:

=SpellNumber(32.50)


Also:

For Access see:
Access- Numbers to Words


To create cardinal numbers in Word, see:
Word - Numbers to Words


See all Topics

Labels: , , ,


<Doug Klippert@ 3:25 AM

Comments: Post a Comment


  Tuesday, July 04, 2017 – Permalink –

Select by Code

Programmatically pick cells


Microsoft has provided 22 ways to select cells/ranges by using Visual Basic procedures in Excel.

Here are a few of the subjects covered:

  • How to Select a Cell on the Active Worksheet

  • How to Select a Cell on Another Worksheet in the Same Workbook

  • How to Select a Range of Cells on the Active Worksheet
  • How to Select a Named Range on a Worksheet in a Different Workbook

  • How to Select a Cell Relative to the Active Cell

  • How to Select the Union of Two or More Specified Ranges

  • How to Select the Intersection of Two or More Specified Ranges

  • How to Select the Last Cell of a Column of Contiguous Data

  • How to Select the Blank Cell at Bottom of a Column of Contiguous Data

How to select cells/ranges by using Visual Basic procedures

Dick Kusleika has some comments on the coding:
Spreadsheets are the Devil


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Tuesday, June 27, 2017 – Permalink –

Getting 2010-13 Reference Guides

Where'd they hide that thing?


Wondering where your favorite Word 2003 commands are located in the new Word 2010-13 interface? Or just want to explore the rich, new design with a little guidance?

... rest the mouse pointer over a Word 2003 menu or button to learn its new location in Word 2010-13. To see an animation of the location of the command or button in Word 2010-13, just click it.


Command reference guides for:

Office 2010

Office 2013




See all Topics

Labels: ,


<Doug Klippert@ 3:53 AM

Comments: Post a Comment


  Sunday, June 25, 2017 – Permalink –

Comment Code

Edit toolbar



You'll many times want to change blocks of code to comments in VBA modules; temporarily convert a block of VBA code to comments so that it's ignored during a trial run. Inserting an apostrophe before each line of code is a bother. Office 2000+ simplifies this task by letting you convert a block of code to comments with a click of a button.

Open any module in the Visual Basic Editor (VBE), and then choose View>Toolbars and choose Edit from the menu bar to display the Edit toolbar.

Select the lines of code that you want to turn into comments. Then, click the Comment Block button on the Edit toolbar (it's the sixth button in from the RIGHT end of the toolbar).
Each line of the selected code is now preceded with an apostrophe.



To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
This, of course, works in any application that uses the VBE.

It's been suggested that two or three apostrophes (sometimes called inverted commas) be placed around existing comments. When the Comment Block is used, the original comments will not be removed.


See all Topics

Labels: , , ,


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Saturday, June 24, 2017 – Permalink –

Data Security Charts

Ways to look closer



Professional statisticians typically have powerful software at their disposal to perform advanced analyses and create slick graphs. But many professionals in the quality field don't enjoy that luxury.

Faced with a limited budget, they must be resourceful with the software they already have. Besides, not everyone needs the capability to perform nonlinear regression with custom loss functions for maximum likelihood!

Fortunately, many occasional data analysts already own a versatile software capable of providing most basic quality analyses -- Microsoft Excel.

  • Shewhart control charts

  • Pareto charts

  • Simple box-and-whisker plots


Excel for Data Analysis


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:28 AM

Comments: Post a Comment


  Tuesday, June 20, 2017 – Permalink –

Use Access or Excel

When to use one or the other


Use Access when you:
  • Require a relational database (multiple tables) to store your data.

  • Might need to add more tables, in the future, to an originally flat or nonrelational data set.

  • Keep a very large amount of data (thousands of entries).

  • Keep data that is mostly text.

  • Rely on multiple external databases to derive and analyze the data that you need.

  • Need to maintain constant connectivity to a large external database, such as one built by using Microsoft SQL Server.

  • Want to run complex queries.

  • Need many people working in the database and you want robust options that expose that data for updating.
Use Excel when you:
  • Require a flat or nonrelational view of your data (that is, you do not need a relational database with multiple tables).

  • This is especially true if that data is mostly numeric - for example, if you want to maintain a financial budget for a given year.

  • Want to run primarily calculations and statistical comparisons on your data - for example, if you want to show a cost/benefit analysis in your company's budget.

Use Access or Excel to manage your data


See all Topics

Labels: , , ,


<Doug Klippert@ 3:25 AM

Comments: Post a Comment


  Monday, May 22, 2017 – Permalink –

Progress Brs

Don't go away



If your macro is going to take a bit of time to complete, it is good manners to inform your users what is going on. If screen updating is turned off, they may think their machine has frozen.

Andy Pope has some great charting examples and also demonstrates a number of
Progress meters

Here's a static example:

Progress display

John Walkenbach, also, has tips about how to create a Progress indicator

also see:
Chip Pearson


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Friday, May 19, 2017 – Permalink –

Open 2007-2010 in 200-2003

Not everyone is going to jump at once


How to open and to save Word 2007, Excel 2007, and PowerPoint 2007 files in earlier versions of Office
Microsoft Office Word, Microsoft Office Excel, and Microsoft Office PowerPoint versions 2000 through 2003 cannot natively open documents that are stored in the Office Open XML Formats in 2007 Microsoft Office programs.

You can install the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats to open or to save 2007 Office files.

Microsoft Office XP and 2003
Word, Excel, and PowerPoint

After you install the Compatibility Pack, you can use your existing version of Word, Excel, and PowerPoint to open, edit, and save the file formats that are new to Word 2007, Excel 2007, or PowerPoint 2007. For example:
  • You can open Word, Excel, or PowerPoint 2007 files by double-clicking them exactly as you do with your existing Word, Excel, and PowerPoint presentation(s).
  • You can save Word, Excel, or PowerPoint 2007 files by clicking the Save button in your version of Word, Excel, or PowerPoint.
Microsoft Office 2000
Word, Excel, and PowerPoint
Word 2000
  • After you install the Compatibility Pack, you can open, edit, and save the document file formats that are new to Word 2007 within Word 2000.
  • You can open files in the formats that are new to Word 2007 by double-clicking the files.
  • You can save files in the formats that are new to Word 2007 by clicking Save in Word 2000.
Excel 2000 and PowerPoint 2000
  • After you install the Compatibility Pack, you can open and save the file formats that are new to Excel 2007 and to PowerPoint 2007 from the Microsoft Windows operating system.
  • You can open files in the formats that are new to Excel 2007 and to PowerPoint 2007 by double-clicking the file on the desktop, in the My Documents folder, or in Microsoft Windows Explorer.
  • You can save files in the formats that are new to Excel 2007 and to PowerPoint 2007 by right-clicking an Excel 2000 file or a PowerPoint 2000 file and then clicking Save As.
Compatibility Pack Functions

Compatibility Pack Download



See all Topics

Labels: , , ,


<Doug Klippert@ 3:02 AM

Comments: Post a Comment


  Sunday, May 14, 2017 – Permalink –

1040 on a Spreadsheet

Free tax forms


All right, we spend our waking hours SUMming with Excel and then when one of the more stressful times of the year shows up, we turn it all over to TurboTax.

Here is a collection of IRS forms Glenn Reeves has done as interlocking spreadsheets.

The formulas are already set up; ready for data entry.
"No PASSWORD is needed!

If a cell is protected, it is because it contains a calculated value.

Many times, a manual override cell is provided if you need to override the calculated value.

After a while, you will figure out that the spreadsheet is completely functional without needing to unprotect it."



(Included on the site are forms back to 1996.)

http://www.excel1040.com


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:06 AM

Comments: Post a Comment


  Saturday, May 13, 2017 – Permalink –

Update the FUI Ribbon

Let the add-ins begin


It is said that the Office 2007 Graphical User Interface Ribbon cannot be as easily changed or modified like it has been in previous versions.

This may be partially true, but not all is lost.

Here is some information from the equine's mouth:


Learn how to customize the Ribbon user interface (UI) in the 2007+ Microsoft Office release. Also learn how new features in Microsoft Visual Studio 2005 Tools for the 2007+ Microsoft Office System support RAD development of Ribbon customization. (40 printed pages)

Customizing the Office Ribbon


Monsieurs MS also have a downloadable spreadsheets with the Control IDs. There are files for 2003 as well.

Lists of Control IDs


2007 Office System Add-In: Icons Gallery

2013 Icons Gallery



See all Topics

Labels: , , , ,


<Doug Klippert@ 3:19 AM

Comments: Post a Comment


  Monday, May 08, 2017 – Permalink –

Statistical Functions

Definitions and list


Excel contains a slew of functions relating to statistical analysis.

That's a slew not a skew.

SKEW(number1,number2,...)

Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean.

Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values.

Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.


Statistical Functions

Training - Statistical


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:51 AM

Comments: Post a Comment


  Wednesday, May 03, 2017 – Permalink –

Font Properties Plus

Everything you need to know


To embed a font in a document or slide show so it can be displayed on any other machine, the font must support that action. The standard Windows properties statement does not show all the needed information.

The bottom of this illustration shows the standard information shown when you right-click a font file, and choose properties. The two views at the top are what appear when the Microsoft Font properties extension is installed.


Font Properties

If you right click on a font file in Windows its basic properties are displayed. The Font properties extension adds several new property tabs to this properties dialog box. These include information relating to font origination and copyright, the type sizes to which hinting and smoothing are applied, and the code pages supported by extended character sets.

It also will tell you if the font can be embedded and/or edited in a document.



Protected
The font may not be embedded, copied, or modified. If you use a protected font in a document and if the document is opened on a computer that does not have the font installed on it, a font substitution occurs. Word substitutes the closest font available on the computer for the missing protected font.
Print/Preview
The font is embedded and temporarily loaded on the target computer. Documents that contain print/preview fonts must be opened read-only, and no edits are stored in the document. Embedding a font of this nature has the least impact on file size increase.
Editable
The font behaves just like the print/preview fonts, except that you may also apply the font to other text in the same document.
Installable
The font is installed on the target computer permanently when you open the document. This allows you to use the new fonts as if you installed the fonts directly into Windows yourself. This type of embedded font has the greatest impact on file size because the entire font or fonts are included with the document.


Versionand Features tabs
The Version tab includes version and date information. The Features tab describes the font in terms of number of glyphs, number of kerning pairs, the possible existence of a euro symbol and the presence of embedded bitmaps within the font.

Linkstab
If a font doesn't include a Web site URL, but does include a 'vendor ID code' a link will be provided to Microsoft's font vendor database.

Font properties extension, version 2.3
(32-bit only)


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:52 AM

Comments: Post a Comment


  Tuesday, May 02, 2017 – Permalink –

Result is a picture

If 4, show kumquat


Allen Wyatt has a cool procedure that will let you show a picture of an object on your spreadsheet depending on a value.

Maybe a snow suit when it's 29 or, say, a pair of bloomers when the computed temperature is 70.

The procedure does not use any VBA, just equations and bright thinking.

ExcelRibbon.Tips.net

Display Images based on a Result


See all Topics

Labels: , , , , , , ,


<Doug Klippert@ 3:55 AM

Comments: Post a Comment


  Sunday, April 23, 2017 – Permalink –

Worksheet Name

Formula construction


There may come a time when you need to display the name of a worksheet.

This formula will do the job:
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)

=CELL("filename",$A$1) 
returns the path, the Workbook name and the Worksheet name. (C:\Documents\[April.xls]\Costs)
=MID(text,start_num,num_chars) 
selects the text that starts at a certain point and goes on for a certain number of characters.

The formula, as written, looks at the full path and selects the first time a closing bracket (]) is found.

It then moves 1 character to the right and displays the results up to 31 characters.
(A worksheet name cannot be more that 31 characters long.)

You could include a reference to that cell on other worksheets.


See all Topics

Labels: , , , ,


<Doug Klippert@ 3:56 AM

Comments: Post a Comment