formula OR text

[Bob Jones]

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!!

 

[RagDyeR]

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!
=====================================================

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 Phillips]

1. Check Tools>Options>View 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]

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]

1. Check Tools>Options>View 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)

 

[RagDyer]

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!
==============================================