Wednesday, February 20, 2013

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.