Hi everyone

I have the formula
<=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDIRECT(A3&"Column"),2))> in
a cell and it takes the contents of A3 (a country) to interrogate a list and
return the appropriate value. It works fine, unless the country name
contains a space (such as Czech Republic). I tried to change the formula to
have the final <A3> replaced with <(SUBSTITUTE(A3," ",""))> but all that
does is actually display the formula in the cell as well as in the formula
bar. I haven't let an apostrophe sneak in there, nor have I pressed <ALT+¬>
to display the formulae. It is only the formula that I've changed which is
displayed in this way. There are several other formulae in the sheet which
aren't displayed in this way and they all work fine.

I thought of getting around it by having a hidden helper column (column C:C
which has the formula <=SUBSTITUTE(A3," ","")> then have the formula which
is causing the problems refer to a cell in this column (i.e.
......INDIRECT(C3&"Column"),2......) but this also simply displays the actual
formula in the cell. It doesn't give any error message such as #REF! or
#N/A etc. It seems that the <SUBSTITUTE> is somehow messing things up for
me. I have two questions, firstly how can I allow a country name with a
space to be used and secondly, why is the formula being displayed in the
cell, rather than a value or error message?

Thanks for your time