Spreadsheet created in Excel 2007, saved as .xls file.
I have a report for a fundraising campaign, which I have updated every week for months in exactly the same format. The database I use to track the data exports into Excel a list of the pledge totals per person with every payment per person. I then consolidate the data into one line per donor, adding up all the payments. To make sure I haven’t made an error in the consolidation, I have used conditional formatting on each total at the top of the spreadsheet so that if the total varies from what it should be, the cell will be filled with red. Each week, I change the formula for the conditional formatting to reflect changes in total pledges (if any – mostly there aren’t changes at this stage in the fundraising campaign), total payments and total balance due. I have a total for pledges, which totals all cells below, a total for payments, which totals all cells below, and a total for balance, which subtracts payment total from pledge total. I have attached a copy of the spreadsheet so you can see what I’ve done. This is not the entire spreadsheet – I’ve removed the top 13 lines, which contained various bits of information about the data, and removed any identifying information. However, even after removing all of that, the conditional formatting still misbehaves as it does in the full spreadsheet.
Starting with last week’s report, the conditional formatting on the Balance (total) cell began to fail. The cell is filled with red regardless of the value in the cell. I have tested a number of things. First, I removed all the lines that make up the heading to see if there was any data which could cause a conflict. Second, I changed the formula in the Balance total cell so that it would add all the lines below rather than subtracting one total from another. Third, I tried applying the conditional formatting to other cells, both cells that contained the same total (which shows up a couple of times in the header) and in blank cells, which referred to the total cell for the conditional formatting. Fourth, I rebuilt the entire spreadsheet, copying only the data, rebuilding all formulas manually. The conditional formatting on the Balance cell only still fails.
I’m just about to tear my hair out over this. Does anybody have any suggestions about what might be causing this problem? Thanks so much.
Lee Morgan
