Vlookup problem

[dk]

we are getting a wording of (#REF!) insteadd of getting the description of
the feild even though there a text data in the refrenced field
ex:=VLOOKUP(B10,'C:\Documents and Settings\Administrator\My
\[calling_.XLS]Customers'!$A$1:$D$58,5,FALSE)

Also we have 2 times the same name in column a ex row a1; has data "Joe" row
a21; has data "Joe" from were will all data filled if were going to enter in
cell b10 =CONCATENATE([calling_.XLS]Customers!$A$21)

 

[Barb Reinhardt]

I'm guessing you have something in your vlookup that includes some
concatenation. Can you show the expression? That might help. Also, is
calling_.XLS open when you do this? It needs to be. Otherwise, you'll
need to use the INDIRECT.EXT within MOREFUNC here

http://xcell05.free.fr/english/index.html#Morefunc_Functions

 

[Peo Sjoblom]

Vlookup works fine with closed workbooks so does concatenate

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

I'm guessing you have something in your vlookup that includes some
concatenation. Can you show the expression? That might help. Also, is
calling_.XLS open when you do this? It needs to be. Otherwise, you'll
need to use the INDIRECT.EXT within MOREFUNC here

http://xcell05.free.fr/english/index.html#Morefunc_Functions

we are getting a wording of (#REF!) insteadd of getting the description
of
the feild even though there a text data in the refrenced field
ex:=VLOOKUP(B10,'C:\Documents and Settings\Administrator\My
\[calling_.XLS]Customers'!$A$1:$D$58,5,FALSE)

Also we have 2 times the same name in column a ex row a1; has data "Joe"
row
a21; has data "Joe" from were will all data filled if were going to enter
in
cell b10 =CONCATENATE([calling_.XLS]Customers!$A$21)

 

[SiC]

$A$1:$D$58,5
That's the part with the problem. The formula is looking in columns from A
to D (4 columns), but it's asking to return a value in column 5. So either
extend your lookup range to more columns, or lower your return column number
value. As for the concatenate question, I don't quite understand it. It
looks like you're trying to concatnate only 1 cell?

-Simon

 

[dk]

OK it seems working , but we realized that sometimes when you update and
change info. in the main database & Save it it will not change the info. in
the other workbook what is the clue?

$A$1:$D$58,5
That's the part with the problem. The formula is looking in columns from A
to D (4 columns), but it's asking to return a value in column 5. So either
extend your lookup range to more columns, or lower your return column number
value. As for the concatenate question, I don't quite understand it. It
looks like you're trying to concatnate only 1 cell?

-Simon

we are getting a wording of (#REF!) insteadd of getting the description of
the feild even though there a text data in the refrenced field
ex:=VLOOKUP(B10,'C:\Documents and Settings\Administrator\My
\[calling_.XLS]Customers'!$A$1:$D$58,5,FALSE)

Also we have 2 times the same name in column a ex row a1; has data "Joe" row
a21; has data "Joe" from were will all data filled if were going to enter in
cell b10 =CONCATENATE([calling_.XLS]Customers!$A$21)

 

[RJ]

Make sure your workbook calculations are set to automatic. Go to Tools -->
Options --> Calculation Tab and set the calculation to automatic. You can
also hit F9 to update the worksheet and calculate if you prefer working in a
manual calculation mode.

OK it seems working , but we realized that sometimes when you update and
change info. in the main database & Save it it will not change the info. in
the other workbook what is the clue?

$A$1:$D$58,5
That's the part with the problem. The formula is looking in columns from A
to D (4 columns), but it's asking to return a value in column 5. So either
extend your lookup range to more columns, or lower your return column number
value. As for the concatenate question, I don't quite understand it. It
looks like you're trying to concatnate only 1 cell?

-Simon

we are getting a wording of (#REF!) insteadd of getting the description of
the feild even though there a text data in the refrenced field
ex:=VLOOKUP(B10,'C:\Documents and Settings\Administrator\My
\[calling_.XLS]Customers'!$A$1:$D$58,5,FALSE)

Also we have 2 times the same name in column a ex row a1; has data "Joe" row
a21; has data "Joe" from were will all data filled if were going to enter in
cell b10 =CONCATENATE([calling_.XLS]Customers!$A$21)