#VALUE! results from XL BUG?

**synergy46** · 01-23-2010, 05:14 PM

I have been wrestling with this for over 2 weeks.

I have a spreadsheet with a simple LIST.

In the last 12 columns are months... Jan, Feb etc... the same formula auto calculates through out that range.

Even if I change the formula to something simple like =$D4.... when I save, close, and then reopen the workbook and then add a NEW ROW ... I get #VALUE! throughout the range.

I have destroyed the list and recreated it... no help.
I have added the simplest of formulas.... no help.
I have deleted the two PivotTables... no help.
I have deleted the existing graph that was based on a PT... no help.

I have run out of ideas.... What causes this weirdness?

**contaminated** · 01-23-2010, 05:34 PM

Just double click to the cell with formula and copy down then accross. I got then working

**romperstomper** · 01-25-2010, 08:09 AM

Is this happening in Excel 2008 for the Mac? (Your profile mentions 2003 rather than 2008, so just checking)

**romperstomper** · 01-25-2010, 07:10 PM

Did you create this workbook in 2008? I can replicate the problem in your workbook, but the lists work fine for me in a new workbook.
Also, is your copy of Office 2008 fully patched up to date?

**synergy46** · 01-27-2010, 03:46 AM

Originally Posted by romperstomper

Did you create this workbook in 2008? I can replicate the problem in your workbook, but the lists work fine for me in a new workbook.
Also, is your copy of Office 2008 fully patched up to date?

Yes, I am working the cursed XL 2008. (I will have to fix my profile).

I finally got around the 'bug' by turning 'off' ListMaster on my list. Why did that screw it up? I am clueless but would like to know.

**synergy46** · 01-27-2010, 03:49 AM

Originally Posted by romperstomper

Is this happening in Excel 2008 for the Mac? (Your profile mentions 2003 rather than 2008, so just checking)

In working with the situation, I ran across this consistency.

I can 'edit' any of the calculated columns (ie, put my cursor in the #VALUE cell and press enter. This corrects the VALUE and makes the formula work as expected. I can then copy that formula across all the columns and when they get recalculated all looks good.

BUT, SAVE AND REOPEN THE WORKSHEET. Then just add a new 'account' (row) and watch everything go back to #VALUE...???????

I 'fixed' it by turning off LIstMaster.... But, why does it do this?

**romperstomper** · 01-28-2010, 11:45 AM

I don't know - as I said, I can reproduce it in your workbook, but not in a new workbook. The biggest problems I have had with Lists in Excel were when workbooks were used in both Windows and Mac versions.

**synergy46** · 02-03-2010, 04:49 AM

Originally Posted by romperstomper

I don't know - as I said, I can reproduce it in your workbook, but not in a new workbook. The biggest problems I have had with Lists in Excel were when workbooks were used in both Windows and Mac versions.

I created this workbook in XL 2008 so I don't know what would cause it??

Thanks for your ideas.

**romperstomper** · 02-03-2010, 04:59 AM

Have you used it on Windows machines as well?

**synergy46** · 02-03-2010, 06:24 AM

Originally Posted by romperstomper

Have you used it on Windows machines as well?

No I haven't tried it on a Windows box. But, your experience of putting the problem cells in a new workbook and having that work got me thinking.

So, I created a new workbook and copied all the sheets into it. After recreating the named dynamic ranges... ba boom. Problem solved.

Thank you for the trouble and thoughts you shared on this weird problem. It looks like I had a corrupt workbook. Your experience and observations got me pointed to finding that.