I have one small section on a larger Totals spreadsheet which happens to be
picking up names and hours from external workbooks. This year's Totals
spreadsheet is identical to last year's, but when I put in test data in the
first external workbook for this year, which appears on the first row of this
little grid on the Totals spreadsheet, I noticed there were no totals on the
bottom. The formula is a simple SUM(D173:D185). After making sure that the
first row was included in the formula, I highlighted the entire column and
hit AutoSum, which should have created the same formula, but it didn't.
Instead I noticed that when I clicked on Autosum the first row, the only one
with data, un-highlighted, and the SUM expression at the bottomof the column
started at the second row, In other words, AutSum would not include the row
with data.
I tested this again by adding similar data to a second workbook, which
appears on the second row of this grid, and found the same result. No totals
at the bottom, and when I used AutoSum to re-create the SUM expression,
highlighted the column again and hit AutoSum, the first and second row
unhighlighted, and the AutoSum created SUM expression started at the third
row.
I checked the type, which was General for the grid and Number for the totals
line, so I could control the number of decimals in the total. This is how
last year's spreadsheet is set up, and there is no problem. However,
changing the type for the whole grid to General makes no difference, using
only whole numbers makes no difference.
I also cleared the contents of this section and re-entered the INDIRECT
formula in all the cells to make sure there was no contamination somewhere.
That had no effect on the problem.
I re-booted, and opened the spreadsheet again, that had no effect.
This whole spreadsheet consists of columns of numbers derived from INDIRECT
formulas which get data from other workbooks. I have never had a problem
totaling those columns, and even on the section in question, while there are
three Hours columns, all of which have the same problem, the Total column on
the right, which Totals the totals using the same SUM expression at the
bottom does work.
Finally, I changed the SUM expression so that instead of using the ":" I
listed each cell in the column individually separated by plus signs. That
worked.
So what would cause Sum (:) to produce no result, or the AutoSum to reject
rows from the column highlighted?
Thanks,
Eric