I cannot work out the coding to over-write a cell containing a formula which has produced a ‘#N/A’ result following a Vlookup, but wanting to retain the formula in the cell. Placing a string in the cell seems to blank the formula.
to over-write a cell containing a formula … but wanting to retain the formula
Either you overwrite a cell or you retain the formula. You cannot have both. A cells’ content is either a formula or data (a formula never replaces itself, but shows something to the user, but the content of the cell is, regardless of what you see, the formula). And therefore you get a blank cell (or the string) if you overwrite the cell with a string - the formula is no longer there. If you don’t want to to see #N/A use another formula, taking into account that the original formula could yield #N/A (e.g. =IFNA(VLOOKUP(A2;B1:B100;1;0);"No result"))
Thanks, opaque, understood. However, I get error 504 if I adapt the formula even if the result is not #N/A. The look up expression is, I have to say, long and involves another sheet! Is this the problem?
I have found this old OO link [Solved] How to Remove #N/A from IF VLOOKUP result (View topic) • Apache OpenOffice Community Forum Is this the way to go?
You ask about an error code without presenting the formula producing that error - I don’t feel that I could provide an answer or comment on which way to go. 504 in general means that number of required parameters is wrong and this is very often in conjunction with using a , (comma) as argument separator, while your language settings (locale) requires ; (semicolon). My advice: Always use ; as argument separator while inserting functions (and it will be automatically converted to ,, if your settings require. It does not work the other way round (i.e. a , gets not converted to ;, if your locale requires; instead you get a 504 in this case).
Many thanks again, opaque - indeed it is a , and not a ; - I will adjust and see how it goes.
OK - 'tis fixed! opaque gave me the necessary ‘kick up the backside’ to spur me on. Although the answer was NOT ,/; , I found the solution on a random YouTube video (which is linked here for anyone else to see https://www.youtube.com/watch?v=LwVkhuxz8EY)
The solution was to use IF(ERROR( etc etc, similar to FJCC’s OO solution in the previous link.
Although the answer was NOT ,/;, I
The solution was to use IF(ERROR( etc etc, similar
… and who should benefit from such imprecise solution for an undescribed problem (you still did not provide your non-working formula and the solution is vague as well)?
Obviously not you?