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

No comments:

Post a Comment