************************************************************
SPREADSHEET TIPS FROM AN EXCEL ADDICT
Helping Average Spreadsheet Users
Become Local Spreadsheet Experts
Thu Aug 14, 2003
A Free Weekly Publication of TheExcelAddict.com
http://www.theexceladdict.com
************************************************************
I Respect Your Privacy And Pledge Not To Abuse This Privilege.
This newsletter is sent ONLY to those who have Subscribed, is
completely 100% opt-in and NEVER uses unethical methods to gain
new subscribers. If you no longer wish to receive this
newsletter, you'll find a simple way to unsubscribe at the
bottom of every newsletter.
This newsletter was sent to:
Francis @
************************************************************
CONTENTS
************************************************************
1) Editor Notes
2) Excel "Quick Tips"
3) T-Shirt Contest
4) "How To Excel" Mini-Tutorials:
5) Reader Suggestions
6) Spreading The Word
7) "Non-Excel" Tip
8) Subscription Management
************************************************************
EDITOR NOTES
************************************************************
Hi Francis,
I hope you are enjoying the first week of summer. Here in
Newfoundland, on the east coast of Canada, we are finally
getting some nice hot weather.
Congratulations to Lisa Hobbs of the USA. She is the very first
winner in my monthly T-Shirt Contest (see below).
Francis, please drop me a line and let me know what you
think about my newsletter. There IS a real human being on the
other side of cyber-space writing this newsletter. I'm writing
it for you and would like to give you what you want. You can
email me at francis@TheExcelAddict.com. I'd love to hear from
you.
To Your Success!
Francis Hayes (The Excel Addict)
************************************************************
QUICK TIPS
************************************************************
------------------------------------------------
Show The Full Path And Filename in Your Workbook
------------------------------------------------
Here's a little known tip that allows you to put the full path
and filename of the current workbook into a cell.
Select the cell where you want the path and filename to appear
and enter the formula =CELL("filename"). NOTE: This will not
work with an new/unsaved workbook.
Besides "filename" there are various other types of useful
information you can return using the CELL worksheet function. Go
to Excel's help and search for "CELL worksheet function" for
more info.
------------------------------
Find/Replace Across All Sheets
------------------------------
Right click on a sheet tab and choose Select All Sheets. Select
Edit | Find (or press CTRL+F). Enter what you want to search for
and click the Find Next button.
Note that in Excel 97 and earlier you can only search a single
sheet.
************************************************************
T-SHIRT CONTEST
************************************************************
Every month I am giving away a really cool t-shirt from
TheExcelAddict.com. You can sneak a peak at my website
http://www.TheExcelAddict.com
If you subscribed to this newsletter prior to this current
month, you can still get your name in for the draw each month by
helping me share this newsletter.
All you have to do each month is refer your friends and
associates to my website and newsletter through the Refer A
Friend link on my website. For each person that you refer, your
name will go into the draw. The more friends you refer, the more
chances you have of winning a t-shirt.
************************************************************
"HOW TO EXCEL" MINI-TUTORIALS
************************************************************
------------------------------------------------
Preventing Duplicate Entries In A Range Of Cells
------------------------------------------------
If you want to prevent duplicate entries in a range of cells,
you can use Excel's Data Validation feature. Let's assume you
want to prevent duplicate entries in cells B5:B50.
1) Select cells B5:B50
2) Choose Data | Validation
3) Click the Settings tab
4) Click the dropdown arrow in the Allow: box and select Custom.
A new Formula box appears
5) Click in the Formula box and enter the formula
=COUNTIF($B$5:$B$50,B5)=1.
6) Click on the Error Alert tab and type Duplicate Entry in the
Title box.
7) In the Error message box type You cannot enter duplicate
values in this list!
8) Click OK.
-------------------------------------------
Quickly Convert Positive Values To Negative
-------------------------------------------
If you have a range of cells where you need to convert each
entry to a negative value, there is an easier and faster way
than changing them individually. Excel's Paste Special option
allows you to accomplish this task relatively easily.
1) Type -1 in any empty cell.
2) Click Edit | Copy
3) Select the cells containing the values you want to convert.
4) Choose Edit | Paste Special.
5) In the Paste section, select Values and in the Operation
section, select Multiply.
6) Click OK.
All the numbers have been changes from positive to negative, or
vice versa. This is a great trick that can also be used to
reduce numbers by a factor of 10, 100, etc. Remember that this
operation changes the underlying values of these cells not just
the displayed value.
************************************************************
READER SUGGESTIONS
************************************************************
TIPS: Do you have an Excel tip you'd like to share with
everyone, please send it to tips@TheExcelAddict.com
TESTIMONIALS: Please drop me a line at kudos@TheExcelAddict.com
and let me know how my Tips, Tricks, Shortcuts, and Techniques
have helped you.
COOL STUFF: Do YOU know of some "out-of-the-ordinary" really
cool thing that YOU can do in Excel? Share it with me and I may
publish it in a new section coming to my website called "Cool
Stuff You Can Do With Excel". Send your suggestions to
coolstuff@TheExcelAddict.com
OTHER COMMENTS: If you have any other comments or suggestions,
please don't hesitate to let me know at
francis@theexceladdict.com
***********************************************************
SPREADING THE WORD
************************************************************
Currently this newsletter is reaching Excel Addicts in: Canada,
USA, Germany, Norway, Brazil, Saudi Arabia, China, Australia,
South Africa, Jordan, Singapore, the Philippines and South
Korea.
There are tens of millions of other Microsoft Excel users all
over the world. The vast majority of them have a very limited
knowledge of Excel's capabilities. My goal is to share this
information with everyone I possibly can and help them to
benefit from Excel's lesser know capabilities.
Francis, please help me spread the word about my
newsletter. If you know other Excel users, please tell them
about "Spreadsheet Tips From An Excel Addict". You can forward
this email to them or, if you'd like to qualify for my T-Shirt
Contest, go to my website http://www.theexceladdict.com and use
the Refer A Friend link to let them know. I'm sure they'll
appreciate it.
Please refer only people you know personally and who use
Microsoft Excel and could benefit from this newsletter.
PLEASE NOTE: Before you forward this newsletter to anyone else,
first delete the Unsubscribe Link at the bottom of this
newsletter. You wouldn't want someone to 'accidentally' remove
your name from my mailing list, would you?
************************************************************
BONUS "NON-EXCEL" TIP
************************************************************
-------------------------------------
Ask Before Emailing Large Attachments
-------------------------------------
Has this happened to you too? I'm using an email service that
gives me a limit of 3MB of email that I can store on their
server. One day I realize that I haven't received any email from
that service for a couple of days. I'm thinking, "That's
strange, I cleaned up my inbox only a couple of days ago." Then
I find the problem, a huge 2MB file that someone has sent me.
All my emails for almost two days were bounced back to the
senders.
This could be a serious problem to some people and you don't
want to be the cause. This is why it's a good idea to ask the
recipient if it is OK before sending large email attachments.
************************************************************
SUBSCRIPTION MANAGEMENT
************************************************************
If this newsletter was forwarded to you from a friend and you
would like to get your own copy, just send a blank email to
TheExcelAddictNewsletter@infogeneratorpro.com
------------------------------------------------------------
"Spreadsheets Tips From an Excel Addict" is a weekly publication
of TheExcelAddict.com. Copyright © 2003, FJH Marketing. All
Rights Reserved.
------------------------------------------------------------ |