Copying Formulas

Posted by Edward Yeldham on January 23, 2002 8:47 AM

I have created a worksheet with a variety of formulas, one being a vlookup. The output if which is #NA, this is correct as the value is not yet contained in the source data.

I copied the whole worksheet (including this formula) and initially left it referencing the same source data. The output of the same formula with the same input parameter is now #value.

I have checked the formats (they appear the same) and recopied the formula from one sheet to another. Still the same answer.

I have then entered my input parameter which is "55431", on the sheet that works this automatically justifies right - suggesting it is a number.

On the sheet that doesn't work it also justifies left, entering an apostrophie e.g '55431 in the cell makes the formula work - but nothing suggests in Excel that the cells are different.

Help??? It is really bugging me!!

Posted by Joe Was on January 23, 2002 9:08 AM

It is hard to say why this happens on a copy, format is a good start, but I would just qualify the formula through an IF test and move on.

Try:

=IF(address of data="","n/a",your lookup formula)

This way your formula works both ways, also try testing with some data, does the #VALUE error go away?

If it does then code the IF test, if not compare the formats of the cells in both versions?

I find that many times the problem is in cell formatting. JSW