Family Reunion Account Workbook Proposal

I had to write a proposal for a Family Reunion Account Workbook that would be implemented in Microsoft Excel. Those involved in the project are certainly aware of what all this "Phase 4" business is all about. Enjoy.

I envision the Family Reunion Account Workbook as having two worksheets. The first worksheet is called “Family Summary” and includes a list of each family and the amount they owe. Row 1 is the header row and includes “Family #”, “Last Name”, “First Names”, “Family Cost”, “Amount Paid”, and “Paid in Full?” in the range A1:F1. The first column is just an incrementing number to quickly identify which family we’re referring to. Since many families have the same last name, it’s difficult to identify each family by one characteristic.

The data for the next three columns will come directly from the “Invoice List” worksheet of our original family reunion workbook submitted in Phase 4. It is important to remember when copying and pasting from the Phase 4 workbook to use “Paste Special” and “Values.” A normal paste will include cell formatting and the formulas from the original spreadsheet. I’ll explain the last two columns momentarily.

The next worksheet is called “Register” and is where receipts are entered. The idea here is that some families may not be able to pay in full in one payment. So, we’ll keep a running tabulation of receipts and reflect the families payment status in the “Family Summary” worksheet. The “Register” worksheet should have the following headers in Row 1: Date, Family #, and Amount. Although it would be easier to read this table if we used names instead of numbers, our SUMIF() function won’t work if we need to reference more than one column.

For the Amount Paid column in the Family Summary worksheet, we’ll use the SUMIF() function to add the payments together if the Family # on the Register worksheet matches the Family # on the Family Summary worksheet. The appropriate function for cell E2 is =SUMIF(Register!B:B,'Family Summary'!A2,Register!C:C). This formula says that if any value in the column B of our Register worksheet (Family #) matches cell A2 of the Family Summary worksheet (Family # for the family on row 2 of the worksheet) then add the value from column C in the Register worksheet (Amount). This function will give us an up to date calculation of how much each family has paid.

<p>

The “Paid in Full?” column will verify the “Amount Paid” vs. the “Family Cost” and either display “Yes” if the family is paid, or nothing if they still owe. The formula that should go into cell F2 is

<code>

=IF(E2<d2, "", "yes")< code>. This formula says, “if the amount paid (E2) is less than the amount owed (D2), print nothing, otherwise print Yes.” Some conditional formatting in this column can help highlight those families that have not paid in pull.

</p>

The final piece of work that needs to be done is data validation in the Register worksheet. We’ll use the Data Validation tool on the Data ribbon to ensure the Column A is a date after the date after the invoices were mailed. We’ll also ensure that Column C is a decimal number greater than 0; after all, no payments of \$0 should be allowed! One thing I’m not sure how to do is validate that every Family # in Column B appears in ‘FamilySummary’!A:A. I believe it can be done, though. For all data validation, we’ll uncheck the ‘Ignore blank’ checkbox of the Settings tab and put a check in the ‘Show error alert after invalid data is entered’ checkbox in the Error Alert tab. This will ensure that no bad data gets in accidentally.

I know this workbook seems overly complicated. Once the formulas are entered into the Family Summary worksheet, it really becomes as simple as entering checks into a check register. I’ve uploaded an example workbook along with this document that should hopefully make things a little easier to understand.