Sum Named Cells

[fishinglittlepine]

I have a workbook with multiple worksheets. I have gathered totals on those worksheets and made the Totals cell a "Named Cell" which is brought back to a Summary worksheet. I have about 77 lines on the Summary worksheet that I want to total the "Named Cells" that are brought back into one column. These cells are Conditionally Formatted, green cell fill for negative result, red cell fill for positive result.

At the bottom of that column I have "=sum(E3:E77)", this sums all the "Named Cells" in this column. But it doesn't give me any result, it remains $-. If I highlight the column of "Named Cells" excel shows me the correct result at the bottom of the worksheet where it gives you the Average, Count and Sum. If I copy and paste the column of "Named Cells" and paste values it gives me the result. I want to be able make changes to the worksheets and have the named cells repopulate the Summary sheet and update the Total at the bottom.

I have tried changing the format of the column from Number to Accountancy to Currency, to General, no result?

Any help would be appreciated.

Vince

 

[konew1]

Three questions.
Are all the sheets in the same workbook and the summary?
Has calculation been turned from automatic to manual?
Are the values being reported numeric, and not text? Modify the formula on a couple of them by adding +0 to see if anything happens