Excel - Using Referential Cells

Type in values on one worksheet and use them on another worksheet without retyping them.  This is what referential cells do for you in Microsoft Excel.  It's almost like having a database within a spreadsheet.



This tutorial uses a fictional silent auction as the basis for the spreadsheet.

Start by setting up a "Master" worksheet where you'll do data entry.  Columns should be: item #, item description, value, donated by, cost, winning bid amount, winner name.

Enter data in a few rows.

Now create a new worksheet called "Results" with these columns: item #, item description, bid amount, winner name.

In the first row of data, click on column A cell (A2) and hit the = (equal sign) key.  Then click on the "Master" worksheet and select the A2 cell on it.  This will now link the two cells, so any value typed into cell A2 on the "Master" worksheet will also display on the "Results" worksheet.  You can now highlight A2 and do a copy paste across the rest of the cells in the row (ctrl+c = copy, ctrl+v = paste).  Excel will automatically increment the cell letter/number to match the appropriate cells for the rest of the row.

Can you now see the data from row 2 on your "Master" worksheet displayed on row 2 of your "Results" worksheet?  If not, view the contents of each cell and fix if needed.  For example, cell A2 should have a value that looks like:
='Master'!A2

You can also use this feature to create a bidding sheet for each item which can be printed and placed by the item.  Follow the same process - set up your desired field labels, then click in each cell in the first row of data, type the = key and then select the cell to reference on the Master worksheet.  The easiest way to create multiple bid sheets is to create one with all of the formatting and fields set up first, then link to the cells on the Master, then copy the worksheet.  You will have to go to each worksheet to update the referential cells to the appropriate row of data (1st sheet is row 2, 2nd sheet is row 3, etc.) in the Master.

If you'd like to see an example in excel, please see this link:
http://www.tepatosystems.com/Articles/SAMPLE_silent_auction_items.xls

Payments Processing for Low Volume - PayPal Buttons

If your small business or non-profit group wants to be able to sell products/services online, but you'll have a very low volume, a full blown e-commerce solution may be too much.  A full e-commerce site can be complex, expensive to maintain, and requires special skills of your webmaster or service provider.

Tepato has several non-profit clients who sell event tickets to one or two events a year on their websites.  The total ticket sales for each event are a few hundred, with about 10 - 25% of the ticket sales being done online.  Since they have such a low volume (both in sales and in frequency), they use PayPal buttons to satisfy their needs.

Most people who shop online are familiar with PayPal and its usefulness in paying for your online purchases.  It also has a merchant function which allows you to create buttons for use on a website.  The buttons can be for a variety of functions, including "donate", "buy it now" or "add to cart" functionality, and can be customized with your specific item names and pricing.  After creating the buttons, your webmaster can copy/paste the code needed to place the buttons right into your website.

PayPal will provide all of the security, data collection and processing on their website, so your organization's website has very minimal impact.  You don't have to pay for security certificates or complicated sales processing pages to be created.  PayPal will process all payments for a fee, similar to any credit card processor.  You can withdraw funds from your account to another bank account electronically at any time.

PayPal allows buyers to submit an instant payment using a bank account or credit cards, whichever they prefer.  Your buyers don't even have to create a PayPal account if they don't wish to do so.

Two small conditions apply when using PayPal:

1. If you are a non-profit group, you may need to provide proof to PayPal (such as a copy of your 501c3 form) to use some of its functions.
2. If your company or non-profit sells anything gun-related, PayPal will not allow it.  You can sell any other items that are not guns or gun-related but they have a restriction on weapons (including raffles which have guns as prizes).

If your company or non-profit organization has a need to do payment processing on a small scale, I recommend PayPal buttons for their ease of use and competitive processing rates.

DropBox - Free File Sharing

Recently one of my clients suggested that we begin using DropBox when he needed to send me large files for his website.  I checked it out and it is a very nice free service.

DropBox starts out new users with a minimum amount of space (2 GB).  If you do certain things, such as set up shared folders or inviting other users to join, you will be given additional free space.  You can earn up to 16 GB of additional space this way.  You can also upgrade to a paid subscription at a monthly cost.

It is very easy to set up and use, and works similarly to Windows Explorer.  In fact, once you have DropBox installed on your PC, you can see your DropBox folder in Windows Explorer just like all of your other hard drives or other storage locations.  It also will give you an icon on your Quick Launch bar to either open your drop box folder or go to their website.

Other useful features:

• Automatically copy all of the photos off of your mobile devices such as an iPhone 
• Create a link to any file or folder in your DropBox, so that you can share that link with anyone who needs it
• Create a shared folder for use by a specific person or people

See this link for more information:
https://www.dropbox.com/


If you are a Tepato Systems client and would like to begin using DropBox to send us your data files, please contact us at crt@tepatosystems.com.

Outlook Calendar - Conditional Format for Color Categories

Personally, I like to have each type of meeting on my Outlook calendar display as a different color.  I often am handling multiple projects, so I'll assign each project a color and use it to easily see what projects I might be meeting on any given day.

When I converted from 2007 to 2010, I lost my settings for this feature.  Today I finally searched and found instructions to re-do my conditional formatting settings in Outlook Calendar.  This is similar to using color categories on emails, however it is created and maintained using the "conditional format" setting.

See this article for detailed instructions:

http://agsci.psu.edu/it/how-to/display-outlook-calendar-appointments-in-different-colors

Email Marketing

Your customers need to hear from you.  They need to know what you have going on - sales, new products or services, special events, etc. for your business.  They also need to see that you are an expert in your field, so you need to share with them when you learn something new or hear about something that might be of interest to them.

There are a lot of email marketing tools available, but I recommend Constant Contact.  It's priced very reasonably (based on the number of email contacts you want to reach).  It has a very easy to learn user interface.  You can import your list of emails from a spreadsheet file easily.  You can link to articles or content on any website (your own or others).  You can include articles, images, coupons or other tidbits that your customers need or want.

Keeping your business name in front of your customers is smart.  As they say, "out of sight, out of mind".

To find out more about Constant Contact, use this link:
http://www.constantcontact.com/features/signup.jsp?rc=1574614664&sru=1101086242696&fc=f&cc=RAF-REFLINK&pn=ROVING

Microsoft Retiring Hotmail

In August of last year, Microsoft announced that they are retiring support for their Hotmail email service.  They are replacing it with Outlook.com.

I have several friends and colleagues who still use Hotmail, and they've asked me about what they need to do about this.

Here is an article that explains what to do to start using Outlook.com instead of Hotmail.  The good news is that you can continue to use your @hotmail address.

http://www.zdnet.com/using-the-outlook-com-preview-faq-7000001967/

Technology Gifts for Xmas

This year the main gifts in my family were all technology related.  I have a 10 year old son who got a new iPod Touch, a 7 year old son who got a new 3DS XL handheld game system and I got an iPad.

Doing the setup of all of these gadgets can be time consuming and frustrating.  Here are a few things that I learned during the experience.

1. There are some good parental controls on both the iPod Touch and the 3DS.  You can use them to limit the types of things that your kids can download onto their device.  For example, I can limit my 10 year old so that he can't download movies that are rated R or NC-17, and he can't download music or Podcasts with explicit content.
2. Just like gift cards for iTunes and the App Store, there are gift cards available for the 3DS.  You can load the gift card value onto the device so that the device owner can purchase games and other items from the Nintendo eShop.  If you don't live in the United States, the e-store may not be available to you.  I discovered this because my son's 3DS was still set to the default region location, which was the first in the alphabetical list, so it was Anguilla.
3. Since I already had an iPhone, I was happy that I could use the cloud to get a copy of all my apps on both my phone and my iPad.  The bad news is that for the apps I have already loaded on my iPhone, they don't show up automatically on my iPad.  Anything new that I add from now on will show up on both devices.  Fortunately it seems that the apps that are not free will give you a copy on both devices at no additional charge (so far).
4. Setting up a second device on my iTunes on my laptop was actually fairly easy.  My son's iPod Touch uses an email address of mine that I didn't use, so his Apple ID is different than mine.  That prevents him from having my apps, etc. and the reverse.  I can set iTunes to only download the music that he likes from my laptop, not all of my music (much of which he doesn't want).
5. I still need to try importing a digital copy of one of our movies, to see how it works in iTunes.
6. I am enjoying my new iPad, especially since I'm trying to use it as an e-reader.  I own far too many paper copy books.  I'm going to start reading the e-versions so that I don't have to buy any more bookcases.  There are a surprising number of free or cheap (1,2,3 dollars) books and other reading materials available in the iBooks store for my iPad  I need to also check to see if my local library will let me borrow books electronically.

Overall, this experience has not been too painful, which is a relief.  Stay tuned for more...