Change Can Be a Good Thing

One of the things I've been working on for quite a while (just over a year, believe it or not) is a new design for the Tips.Net sites. I'm not ready to switch over the ExcelTips sites quite yet, but I've made the plunge with a few of my other sites.

I'd really, really, REALLY appreciate it if you could visit one of these newly redesigned sites and let me know what you think. This site is probably a good approximation of what the ExcelTips site would look like:

https://drive.tips.net

The sites are intended to be a bit simpler, plus they are "responsive," so they look better on tablets and mobile devices.

Anyway, PLEASE let me know what you think. I suspect that all of my sites (including the ExcelTips sites) should be switched over in the next month or so.

—Allen
     

ExcelTips (ribbon) for 14 January 2017

Macros extend Excel
Calculating the Distance between the Top of the Window and Row 1

Normally Excel positions a UserForm in the center of your screen. You may want to position the form elsewhere, more specific to your purpose in using the UserForm. This tip provides some guidance on how you might be able to achieve this.

Read this tip »

(Thanks to Steve Aprahamian, Hans Hallebeek, and Thomas Van Dam for contributing to this tip.)

 
Powerful spreadsheets
Reference Shortcut

Need to modify how a cell reference, in a formula, is constructed? The shortcut described in this tip will help you step through each of the various referencing modes that can be used by a cell.

Read this tip »

 
Free Mac Security Online Course

The Practical Guide to Mac Security is a free course that will enable the typical home and office Mac user to secure their Mac from dangers like malware, online account break-ins, data loss, and online scams. The lessons allow you to secure your own Mac without needing to understand complicated computer techniques. A great everyday guide for everyday Mac users!

 
Conditional formatting
Conditional Format that Checks for Data Type

Conditional formatting can be used to highlight cells that contain the improper type of data for your needs. This tip examines how to check if either text or numbers have been entered into a cell.

Read this tip »

 
Macros extend Excel
Understanding the If ... End If Structure

One of the most basic of programming structures is the conditional structure: If ... End If. This tip explains how this structure works within a macro.

Read this tip »

     

Help Wanted

This section is for those having problems making Excel behave. If Excel is giving you fits, feel free to submit your own Help Wanted question.

If you have a solution for the problems below, click the link after the problem to send us your answer. (All responses become the sole property of Sharon Parq Associates, Inc., and can be used in any way deemed appropriate.) If your response is used in a future issue, you will be credited for your contribution to the answer.

 
Highlighting Pattern Violations

I have a worksheet that contains over ten thousand rows, with each cell in column A containing a file name. These names need to follow two rules, and I need to discover which names violate either of the rules. If a file name contains a dash, it must also have a single space before and after the dash. The second rule is that if the name contains a comma, there must be no space before it but a single space after it. How can I highlight any cells which violate either (or both) of these rules?
—Steve Wyche (provide an answer for this Help Wanted question)

 
ExcelTips is a free service of Sharon Parq Associates. You can find thousands of tips at our website and a whole lot more. ExcelTips is part of the Tips.Net network.
Sharon Parq Associates, Inc.  •  PO Box 1187  •  Mountain View, WY  82939  •  801-607-2035