Linked spreadsheet with formulas

[Torgeir]

Hi;
I have a DB where I want to link excel-files that are used
by KBI-reporters in my company. These excel-files are
user-restricted so that users can't rename titles etc.
The tab I want to link is containing formulas, like

=+IF('Inputfile!$A$1'="";"";'Inputfile!$A$1')

The formula-result quite often is text.

Now, when I link this excel-tab, I get a numeric field
producing a #Num! where my excel-formula returns text.
But if I link tabs where the same text is written directly
in the cell, I don't experience this...

Any suggestions?
....or do I have to make a VBA-code to import rather than
link the tables...

 

[John Nurick]

Hi Torgeir,

Linking to Excel is often problematic, especially when there is a
mixture of text and numeric values in a column. Complex formulas seem to
be a source of trouble too.

Try using the TEXT() function in your formulas to force the numeric
values to text, e.g.

=IF('Inputfile!$A$1'="";"";TEXT('Inputfile!$A$1',"0.0000")