1. Why does a formula SOMETIMES act as a formula and in other work
book/sheet act as text? ie will produce "Joe Bloggs" if A3="Joe" and
A2="Bloggs" but in another worksheet will remain "=CONCATENATE(A3," ",A2)"
2. How do I lookup a column of text which are a result of formula? eg.
=VLOOKUP(A2,NAMES,2) where A2="Joe Bloggs" and NAMES=Array of results of
formula "=CONCATENATE(A3," ",A2)". Currently produces "N/A" even though I
KNOW that Names contains "Joe Bloggs".
Any help with this would be greatly appreciated as I only have one or two
handfulls of hair left!!
--
Bob JONES

Give me ambiguity or give me something else

There are a couple of reasons why your formula might be displayed as a text
string.

Do you have "Formula View" toggled ON?
<Tools <Options <View tab,
And make sure that "Formulas"
Is *UNCHECKED*.
Keyboard toggle is
<Ctrl < ` (open single quote - above Tab key - under Esc key, on my
keyboard)

OR

The formula was entered into a cell that was pre-formatted to Text.
Try this:
Select one of these cells, then
<Ctrl <Shift < ~
Then
<F2
Then
<Enter

What you did here, was use the keyboard shortcut to format the cell to
"General",
Then entered the "Edit" mode,
Then re-registered the formula *after* the format change.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Bob Jones" wrote in message
...
1. Why does a formula SOMETIMES act as a formula and in other work
book/sheet act as text? ie will produce "Joe Bloggs" if A3="Joe" and
A2="Bloggs" but in another worksheet will remain "=CONCATENATE(A3," ",A2)"
2. How do I lookup a column of text which are a result of formula? eg.
=VLOOKUP(A2,NAMES,2) where A2="Joe Bloggs" and NAMES=Array of results of
formula "=CONCATENATE(A3," ",A2)". Currently produces "N/A" even though I
KNOW that Names contains "Joe Bloggs".
Any help with this would be greatly appreciated as I only have one or two
handfulls of hair left!!
--
Bob JONES

Give me ambiguity or give me something else

The formula was entered into a cell that was pre-formatted to Text.
Try this:
Select one of these cells, then
<Ctrl <Shift < ~
Then
<F2
Then
<Enter
This did it! I was formatting the cells as Text when importing the .TXT
file. Keeping the cells as General made the difference.
Much thanks!
--
Bob JONES

Give me ambiguity or give me something else

1. Check ToolsOptionsView and make sure that Formulas is not checked.

2. Try =VLOOKUP(A2,NAMES,2,False) in case they are not ordered.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Jones" wrote in message
...
1. Why does a formula SOMETIMES act as a formula and in other work
book/sheet act as text? ie will produce "Joe Bloggs" if A3="Joe" and
A2="Bloggs" but in another worksheet will remain "=CONCATENATE(A3," ",A2)"
2. How do I lookup a column of text which are a result of formula? eg.
=VLOOKUP(A2,NAMES,2) where A2="Joe Bloggs" and NAMES=Array of results of
formula "=CONCATENATE(A3," ",A2)". Currently produces "N/A" even though I
KNOW that Names contains "Joe Bloggs".
Any help with this would be greatly appreciated as I only have one or two
handfulls of hair left!!
--
Bob JONES

Give me ambiguity or give me something else

"Bob Phillips" wrote in message
...
1. Check ToolsOptionsView and make sure that Formulas is not checked.

Formulas not checked. Problem was cell formatted as Text when data imported
from .txt file. Kept format as General and it worked
2. Try =VLOOKUP(A2,NAMES,2,False) in case they are not ordered.

Have tried both ways. Neither worked. Suspect VLOOKUP looks at actual
content of cell rather than result of formula, hence "Joe Bloggs" not
contained in "=CONCATENATE(A3," ",A2)
--
Bob JONES

Give me ambiguity or give me something else

Vlookup, as all other functions, will calculate on the *results* of any
formula contained in the referenced cell.

Your issue here is, I'll bet, that your data just *doesn't* match!

Re-type the name
Joe Bloggs
into A2.

Now, go to your "Names" range.
You can shorten your formula to,
=A3&" "&A2
Re-enter Joe in A3
Re-enter Bloggs in A2
Make sure you do this exactly the same way as you did in the lookup cell A2.
Make sure you don't inadvertently add any spaces!

It should work now.

If it does, that means that you might have invisible characters in either of
the concatenated cells or the lookup cells.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Bob Jones" wrote in message
...
"Bob Phillips" wrote in message
...
1. Check ToolsOptionsView and make sure that Formulas is not checked.

Formulas not checked. Problem was cell formatted as Text when data
imported
from .txt file. Kept format as General and it worked
2. Try =VLOOKUP(A2,NAMES,2,False) in case they are not ordered.

Have tried both ways. Neither worked. Suspect VLOOKUP looks at actual
content of cell rather than result of formula, hence "Joe Bloggs" not
contained in "=CONCATENATE(A3," ",A2)
--
Bob JONES

Give me ambiguity or give me something else