Vlookup problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


Barb Reinhardt said:
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



dk said:
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)
Click to expand...
Click to expand...
 
$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
 
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?

SiC said:
$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 said:
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)
Click to expand...
Click to expand...
 
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.

dk said:
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?

SiC said:
$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 said:
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)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top