#num! Error as worksheet is not recalcaculating the cell

[Roncondor]

I am using Excel 2007 / Windows 7 Home Premium / PC not a MAC

I have a tab where I use a pull down to select from a list (there are 80 of them) and based on the resulting position in the database, it pulls data from various locations (using index) to display the info.

It works for ALL of the properties except for one. Some of the data calculations show up as #num! and these calculations are very simple
Cell C20 =EDATE(i_initqtrdate,(c_three*(O6-c_one))) using named ranges (which are constants NOT variables), and O6=8 (and is an integer since I rounded), i_initqtrdate is a valid date format, c_three = 3, c_one = 1
Cell D20 =EDATE(C21,3)-1
Cell E20 =IF(D20<i_asofdate,"Actual","projected") I_asofdate is an input in appropriate Date format and is 04/01/13

The worksheet is set to automatic calculation and there are no circ references.

If I edit the cell, the #num! goes away and the correct value appears so I know my formula is correct. if I hit F9 or run a macro to recalc nothing changes. First correct answer wins a few very cool multitool!

 

[jasmith4]

Can you be more clear about the 80 lists and the database? It sounds like somewhere on that list you have text that looks like numbers but really isn't, e.g., a space before or after a sequence of digits.

If it's an Excel list, go to the source range, and in an empty cell next to the first data cell in the list enter the formula:

=UPPER(CELL("type",click-the-data-cell))

Then hit Enter, copy that formula down next to the test of the data cells, and you should see nothing but V's for value. If you see L's, those are labels, and if you see B's, those are blanks.

If the values are in a database, you need to check the data type of the field that has the values -- it's probably not numeric but text.

Either way, that's why you can edit it and it works: when you type nothing but digits, Excel understands that it's a number, but before you do, something is telling Excel that it's not a number.