Trying to deal with #Num! on linked spreadsheet (1 Viewer)

I'm trying to build what is supposed to be a very simple invoicing app, and running into nothing but problem after problem after problem.

In a nutshell, accounting exports invoicing data into a spreadsheet, and I'm to import this spreadsheet into the invoicing tool and generate the invoice.

Sounds easy enough, but there's a hitch: due to inconsistant entry, for a number of records, numeric fields have been left blank. Most of them are null entries, which is fine, I have a function that converts those to $0 values for billing purposes. What's killing me is that SOME of the fields are linking as #Num! rather than a value or a null, and I can't seem to do anything with those. :banghead:

I've tried converting any value to 0 that returns FALSE value from IsNumeric; I've even tried formulas that return a 0 if the looked up value is an error, but that bloody error propagates through everything I've attempted.

So, does anyone have any suggestions? After this, I get to move on to trying to limiting record adding to unique rows when the 'unique identifier' is 11 fields long because of management requirements.

Hi,
-Ensure you have no circular references. This is indicated in to bottom left of the Excel window as CIRC or circular reference directing you to the offending cell.

-Try making your numbers smaller. Do any of the values referenced contain E+ which indicates an exponential value which is too large for excel to handle, so will return #Num.

-Format referenced cells to number and lose any currency symbols. Look out for apostrophe '. This can cause real formatting problems as it formats the number in the cell as text. This can be incredibly difficult to get rid of as it is not always even visible.

-If using iterative functions, you may need to change the number of times Excel calculates the worksheet. You don't give your version of Excel but in 2010, its Home tab, Options (just above Exit), Formulas. On other versions it may be tools, options and a calculation tab. Here you can just try Unchecking or checking the enable interation calculation box. If this doesn't work familiarise yourself with iterations to work out wether you want the box checked or not and the Min and Max number of iterations. The smaller the Max number, the more accurate the result and (I believe) will therefore take longer to calcualte the worksheet.

Thanks. Unfortunately, I gave up and just switched to importing into an internal (temporary) table.

There were no references at all in the spreadsheet - it was a data export from another prorgram, and is thus pure data.

The main problem is that out of the 1200 lines of data, it's being entered by 600 people, and the issue was definitely text strings being entered in date and number strings. Because of that, the #Num! propagated through everything I tried.

By importing rather than linking, I just let Access reject any text being imported into numeric fields. As I do a cleanup step after that (the whole process has to be automated), it's easy enough to convert any null values in number fields to zeroes.

Thanks a ton for the suggestions, though - if it HAD been functions, I'd have never thought to look for iterative functions as the culprits.