Hi everyone

I have the formula
<=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,IND IRECT(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

Using the formula with Substitute in the Indirect function should work.

=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",
VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3," ","")&"Column"),2))

Is the Czech Republic range named CzechRepublicColumn ?
Perhaps the formula has a space before the equal sign.

Tosca wrote:
Hi everyone

I have the formula
<=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,IND IRECT(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








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra

Yes, the range is CezchRepublicColumn and no, there is no leading space!

As a matter of interest, I started a new workbook and set up a dummy
CzechRepublicColumn range and entered the formula into C3. It still
demonstrated the formula in the cell as well as the formula bar, despite no
apostrophe, leading space etc!!!

It's almost as if my version of Excel (2003) doesn't like the combination of
INDIRECT and VLOOKUP.

BTW, I am using the linked lists process that you have explained on your
website so I am pleased that you "jumped in" to help!
..

"Debra Dalgleish" wrote in message
...
Using the formula with Substitute in the Indirect function should work.

=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDI RECT(SUBSTITUTE(A3,"
","")&"Column"),2))

Is the Czech Republic range named CzechRepublicColumn ?
Perhaps the formula has a space before the equal sign.

Hi again

In further desperation, I saved the change to the formula (which still
generated odd behaviour), then rebooted. Everything works fine now!!! The
laptop is only 6 months old and had been turned on for about 6 hours. I
don't have any virus or malware (or didn't 3 days ago when I last scanned) -
but I'll check again.

Thanks for your help - it was reassuring to know that what I thought should
work, has done, and it seems to have resolved itself.

"Tosca" wrote in message
...
Hi Debra

Yes, the range is CezchRepublicColumn and no, there is no leading space!

As a matter of interest, I started a new workbook and set up a dummy
CzechRepublicColumn range and entered the formula into C3. It still
demonstrated the formula in the cell as well as the formula bar, despite
no apostrophe, leading space etc!!!

It's almost as if my version of Excel (2003) doesn't like the combination
of INDIRECT and VLOOKUP.

BTW, I am using the linked lists process that you have explained on your
website so I am pleased that you "jumped in" to help!
.

"Debra Dalgleish" wrote in message
...
Using the formula with Substitute in the Indirect function should work.

=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDI RECT(SUBSTITUTE(A3,"
","")&"Column"),2))

Is the Czech Republic range named CzechRepublicColumn ?
Perhaps the formula has a space before the equal sign.

You're welcome, and thanks for describing how you solved the problem
(very mysterious behaviour!). I'm glad you've got it working now.

Tosca wrote:
Hi again

In further desperation, I saved the change to the formula (which still
generated odd behaviour), then rebooted. Everything works fine now!!! The
laptop is only 6 months old and had been turned on for about 6 hours. I
don't have any virus or malware (or didn't 3 days ago when I last scanned) -
but I'll check again.

Thanks for your help - it was reassuring to know that what I thought should
work, has done, and it seems to have resolved itself.

"Tosca" wrote in message
...

Hi Debra

Yes, the range is CezchRepublicColumn and no, there is no leading space!

As a matter of interest, I started a new workbook and set up a dummy
CzechRepublicColumn range and entered the formula into C3. It still
demonstrated the formula in the cell as well as the formula bar, despite
no apostrophe, leading space etc!!!

It's almost as if my version of Excel (2003) doesn't like the combination
of INDIRECT and VLOOKUP.

BTW, I am using the linked lists process that you have explained on your
website so I am pleased that you "jumped in" to help!
.

"Debra Dalgleish" wrote in message
...

Using the formula with Substitute in the Indirect function should work.

=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDI RECT(SUBSTITUTE(A3,"
","")&"Column"),2))

Is the Czech Republic range named CzechRepublicColumn ?
Perhaps the formula has a space before the equal sign.







--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

"Debra Dalgleish" wrote...
....
=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",
VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3," ","")&"Column"),2))
....

Quibble: could replace the OR call with COUNTA(A3,B3)<2.

Thank you Harlan - I'm glad to receive such comments as it makes the formula
less complicated!

"Harlan Grove" wrote in message
...
"Debra Dalgleish" wrote...
...
=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",
VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3," ","")&"Column"),2))
...

Quibble: could replace the OR call with COUNTA(A3,B3)<2.