In spreadsheet A I enter a part number. I then have a formula that does a
look up of that part number in spreadsheet B and returns the description,
cost and price of the part number back into spreadsheet A.

This has been working perfectly until today. I found an error with a part
number in spreadsheet B and corrected the error (changed from ":CFF-8" to
"CFF-8"). I have done corrections in the past and added items to spreadsheet
B with no problems.

However, today after correcting the part number in spreadsheet B, all I get
in spreadsheet A for description, cost or price is "#N/A". If I change the
part number back to the old one, then everything works fine. Even if I
insert a new line with the correct part number, it still will not return any
information.

Any ideas what is going on?

Thanks in advance for any help.

Check out the formual array range .. Post sample data and formula which is
erroneous.

If this post helps click Yes
---------------
Jacob Skaria


"dannaz" wrote:

In spreadsheet A I enter a part number. I then have a formula that does a
look up of that part number in spreadsheet B and returns the description,
cost and price of the part number back into spreadsheet A.

This has been working perfectly until today. I found an error with a part
number in spreadsheet B and corrected the error (changed from ":CFF-8" to
"CFF-8"). I have done corrections in the past and added items to spreadsheet
B with no problems.

However, today after correcting the part number in spreadsheet B, all I get
in spreadsheet A for description, cost or price is "#N/A". If I change the
part number back to the old one, then everything works fine. Even if I
insert a new line with the correct part number, it still will not return any
information.

Any ideas what is going on?

Thanks in advance for any help.

Array range is fine.
if I enter ":CFF-8" in cell os spreadsheet A, then I get the proper
description of this part from spreadsheet B. However, if I go into
spreadsheet B and change te part numberfrom ":CFF-8" to "CFF-8), enter "CFF-8
" into cell in spreadsheet A, all I get for a description is #N/A.

Here is the formula that I am using for the lookup:
=IF($A530,VLOOKUP($A53,'C:\Users\Dan\Documents\Ex cel Files\Information for
project costing sheets.xlsm'!Inventory,2,FALSE)," ")

Thanks

Dan

"Jacob Skaria" wrote:

Check out the formual array range .. Post sample data and formula which is
erroneous.

If this post helps click Yes
---------------
Jacob Skaria


"dannaz" wrote:

In spreadsheet A I enter a part number. I then have a formula that does a
look up of that part number in spreadsheet B and returns the description,
cost and price of the part number back into spreadsheet A.

This has been working perfectly until today. I found an error with a part
number in spreadsheet B and corrected the error (changed from ":CFF-8" to
"CFF-8"). I have done corrections in the past and added items to spreadsheet
B with no problems.

However, today after correcting the part number in spreadsheet B, all I get
in spreadsheet A for description, cost or price is "#N/A". If I change the
part number back to the old one, then everything works fine. Even if I
insert a new line with the correct part number, it still will not return any
information.

Any ideas what is going on?

Thanks in advance for any help.

OK. Check whether you are having a space before or after ":CFF-8" in cell $A53.


Inorder to handle the additional : in your lookup table you can adjust the
vlookup to the below...which will lookup "CFF-8" and return the results for
":CFF-8" ....

=VLOOKUP("*" & TRIM($A53) & "*",Sheet2!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria


"dannaz" wrote:

Array range is fine.
if I enter ":CFF-8" in cell os spreadsheet A, then I get the proper
description of this part from spreadsheet B. However, if I go into
spreadsheet B and change te part numberfrom ":CFF-8" to "CFF-8), enter "CFF-8
" into cell in spreadsheet A, all I get for a description is #N/A.

Here is the formula that I am using for the lookup:
=IF($A530,VLOOKUP($A53,'C:\Users\Dan\Documents\Ex cel Files\Information for
project costing sheets.xlsm'!Inventory,2,FALSE)," ")

Thanks

Dan

"Jacob Skaria" wrote:

Check out the formual array range .. Post sample data and formula which is
erroneous.

If this post helps click Yes
---------------
Jacob Skaria


"dannaz" wrote:

In spreadsheet A I enter a part number. I then have a formula that does a
look up of that part number in spreadsheet B and returns the description,
cost and price of the part number back into spreadsheet A.

This has been working perfectly until today. I found an error with a part
number in spreadsheet B and corrected the error (changed from ":CFF-8" to
"CFF-8"). I have done corrections in the past and added items to spreadsheet
B with no problems.

However, today after correcting the part number in spreadsheet B, all I get
in spreadsheet A for description, cost or price is "#N/A". If I change the
part number back to the old one, then everything works fine. Even if I
insert a new line with the correct part number, it still will not return any
information.

Any ideas what is going on?

Thanks in advance for any help.

No spaces before or after. I have tried copying the part number from
spreadsheet B and inserting it into spreadsheet A to make sure the number are
identical. Still will not return the description. Also, if I insert a new
line in spreadsheet B and enter a new part number and description, then try
to recall this in spreadsheet A, it still will not find that new part number
description.

Dan

"Jacob Skaria" wrote:

OK. Check whether you are having a space before or after ":CFF-8" in cell $A53.


Inorder to handle the additional : in your lookup table you can adjust the
vlookup to the below...which will lookup "CFF-8" and return the results for
":CFF-8" ....

=VLOOKUP("*" & TRIM($A53) & "*",Sheet2!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria

--Have you tried the below version
=VLOOKUP("*" & TRIM($A53) & "*",Sheet2!A:B,2,0)

--I am sure this is something simple which we are unable to track. Check
your named range; Copy paste a small set of data to another workbook and try
out sample VLOOKUPS() to find whats happening..

If this post helps click Yes
---------------
Jacob Skaria


"dannaz" wrote:

No spaces before or after. I have tried copying the part number from
spreadsheet B and inserting it into spreadsheet A to make sure the number are
identical. Still will not return the description. Also, if I insert a new
line in spreadsheet B and enter a new part number and description, then try
to recall this in spreadsheet A, it still will not find that new part number
description.

Dan

"Jacob Skaria" wrote:

OK. Check whether you are having a space before or after ":CFF-8" in cell $A53.


Inorder to handle the additional : in your lookup table you can adjust the
vlookup to the below...which will lookup "CFF-8" and return the results for
":CFF-8" ....

=VLOOKUP("*" & TRIM($A53) & "*",Sheet2!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria