Vlookup - intermittent problem

  • Thread starter Thread starter Jo P
  • Start date Start date
J

Jo P

I am using the following formula

=VLOOKUP(W125,'Despatches January 2003.xls'!ship,18,FALSE)

to take a date from the spreadsheet 'Despatches' and put
it into my main spreadsheet.

The date column in both spreadsheets has been formatted
for 'date' and the base information is in order. I get 2
different results. One result is the date that I want,
the other result is a zero value.

Both results are from the same formula. Does anyone know
why it does this? I have checked that the information I
want appears in the 'Despatches' spreadsheet and it is
open behind my main spreadsheet.
 
Jo P said:
I am using the following formula

=VLOOKUP(W125,'Despatches January 2003.xls'!ship,18,FALSE)

to take a date from the spreadsheet 'Despatches' and put
it into my main spreadsheet.

The date column in both spreadsheets has been formatted
for 'date' and the base information is in order. I get 2
different results. One result is the date that I want,
the other result is a zero value.

Both results are from the same formula. Does anyone know
why it does this? I have checked that the information I
want appears in the 'Despatches' spreadsheet and it is
open behind my main spreadsheet.
Click to expand...

=VLOOKUP(W125,'Despatches January 2003.xls'!ship,18,FALSE)
doesn't contain a proper reference to another file.

Assuming "ship" is the name of the range in which you want to do the lookup,
it should be of the form
=VLOOKUP(W125,'[Despatches January 2003.xls]SheetName'!ship,18,FALSE)

Does this fix the problem?
 
No it hasn't solved the problem, it just changes the
formula back to what is shown below.

Jo
-----Original Message-----
in message
I am using the following formula

=VLOOKUP(W125,'Despatches January 2003.xls'! ship,18,FALSE)

to take a date from the spreadsheet 'Despatches' and put
it into my main spreadsheet.

The date column in both spreadsheets has been formatted
for 'date' and the base information is in order. I get 2
different results. One result is the date that I want,
the other result is a zero value.

Both results are from the same formula. Does anyone know
why it does this? I have checked that the information I
want appears in the 'Despatches' spreadsheet and it is
open behind my main spreadsheet.
Click to expand...

=VLOOKUP(W125,'Despatches January 2003.xls'!ship,18,FALSE)
doesn't contain a proper reference to another file.

Assuming "ship" is the name of the range in which you want to do the lookup,
it should be of the form
=VLOOKUP(W125,'[Despatches January 2003.xls]SheetName'! ship,18,FALSE)

Does this fix the problem?


.
Click to expand...
 
Anon,
Assuming "ship" is the name of the range in which you want to do the lookup,
it should be of the form
=VLOOKUP(W125,'[Despatches January 2003.xls]SheetName'!ship,18,FALSE)
Click to expand...

I don't think that is right. If ship is a workbook name, you don't need to
qualify with the sheet name. The OP's syntax works for me.

Bob
 
Bob Phillips said:
Anon,
Assuming "ship" is the name of the range in which you want to do the lookup,
it should be of the form
=VLOOKUP(W125,'[Despatches January 2003.xls]SheetName'!ship,18,FALSE)
Click to expand...

I don't think that is right. If ship is a workbook name, you don't need to
qualify with the sheet name. The OP's syntax works for me.

Bob
Click to expand...

My apologies. I was confusing myself as regards names in each workbook. Yes,
as long as the name is a workbook-level name, the OP's syntax is correct.
 
Jo P said:
-----Original Message-----
in message
I am using the following formula

=VLOOKUP(W125,'Despatches January 2003.xls'! ship,18,FALSE)

to take a date from the spreadsheet 'Despatches' and put
it into my main spreadsheet.

The date column in both spreadsheets has been formatted
for 'date' and the base information is in order. I get 2
different results. One result is the date that I want,
the other result is a zero value.

Both results are from the same formula. Does anyone know
why it does this? I have checked that the information I
want appears in the 'Despatches' spreadsheet and it is
open behind my main spreadsheet.
Click to expand...

=VLOOKUP(W125,'Despatches January 2003.xls'!ship,18,FALSE)
doesn't contain a proper reference to another file.

Assuming "ship" is the name of the range in which you want to do the lookup,
it should be of the form
=VLOOKUP(W125,'[Despatches January 2003.xls]SheetName'! ship,18,FALSE)

Does this fix the problem?
Click to expand...
No it hasn't solved the problem, it just changes the
formula back to what is shown below.

Jo
Click to expand...

My apologies. As Bob Phillips has pointed out, as long as the name "ship" is
a workbook-level name (which it appears to be, as you say Excel changes the
formula back), your original syntax is correct.

I can only think that something must be different about the values looked up
in your two situations. Does your formula work for most lookup values (in
W125) and just fail with a specific one? If so, concentrate on the cell
containing the data that is returned as a zero value. If you change the
data, does it change what is returned?
 
I have changed the formula to a different cell reference
number that works, and then the formula is OK and I get
the result. If I change it back to the original cell
reference it comes back with a zero value.

Both have dates in the 'Despatches' spreadsheet in column
18, both have the correct correspondening cell in
the 'Despatches' spreadsheet.

I can't understand why it works for one and not the other
when the formulas are the same. All the information is
there. It's incredibly frustrating.

-----Original Message-----
in message
Click to expand...
I am using the following formula
=VLOOKUP(W125,'Despatches January 2003.xls'! ship,18,FALSE)

to take a date from the spreadsheet 'Despatches' and put
it into my main spreadsheet.

The date column in both spreadsheets has been formatted
for 'date' and the base information is in order. I
Click to expand...
get
2
different results. One result is the date that I want,
the other result is a zero value.

Both results are from the same formula. Does anyone know
why it does this? I have checked that the information I
want appears in the 'Despatches' spreadsheet and it is
open behind my main spreadsheet.

=VLOOKUP(W125,'Despatches January 2003.xls'! ship,18,FALSE)
doesn't contain a proper reference to another file.

Assuming "ship" is the name of the range in which you want to do the lookup,
it should be of the form
=VLOOKUP(W125,'[Despatches January 2003.xls]SheetName'! ship,18,FALSE)

Does this fix the problem?
Click to expand...
No it hasn't solved the problem, it just changes the
formula back to what is shown below.

Jo
Click to expand...

My apologies. As Bob Phillips has pointed out, as long as the name "ship" is
a workbook-level name (which it appears to be, as you say Excel changes the
formula back), your original syntax is correct.

I can only think that something must be different about the values looked up
in your two situations. Does your formula work for most lookup values (in
W125) and just fail with a specific one? If so, concentrate on the cell
containing the data that is returned as a zero value. If you change the
data, does it change what is returned?


.
Click to expand...
 
Jo P said:
-----Original Message-----
in message

I am using the following formula

=VLOOKUP(W125,'Despatches January 2003.xls'!
ship,18,FALSE)

to take a date from the spreadsheet 'Despatches' and put
it into my main spreadsheet.

The date column in both spreadsheets has been formatted
for 'date' and the base information is in order. I get
2
different results. One result is the date that I want,
the other result is a zero value.

Both results are from the same formula. Does anyone
know
why it does this? I have checked that the information I
want appears in the 'Despatches' spreadsheet and it is
open behind my main spreadsheet.

=VLOOKUP(W125,'Despatches January 2003.xls'! ship,18,FALSE)
doesn't contain a proper reference to another file.

Assuming "ship" is the name of the range in which you
want to do the lookup,
it should be of the form
=VLOOKUP(W125,'[Despatches January 2003.xls]SheetName'!
ship,18,FALSE)

Does this fix the problem?

No it hasn't solved the problem, it just changes the
formula back to what is shown below.

Jo
Click to expand...

My apologies. As Bob Phillips has pointed out, as long as
the name "ship" is
a workbook-level name (which it appears to be, as you say
Excel changes the
formula back), your original syntax is correct.

I can only think that something must be different about
the values looked up
in your two situations. Does your formula work for most
lookup values (in
W125) and just fail with a specific one? If so,
concentrate on the cell
containing the data that is returned as a zero value. If
you change the
data, does it change what is returned?
Click to expand...
I have changed the formula to a different cell reference
number that works, and then the formula is OK and I get
the result. If I change it back to the original cell
reference it comes back with a zero value.

Both have dates in the 'Despatches' spreadsheet in column
18, both have the correct correspondening cell in
the 'Despatches' spreadsheet.

I can't understand why it works for one and not the other
when the formulas are the same. All the information is
there. It's incredibly frustrating.
Click to expand...

I assume you mean you change from
=VLOOKUP(W125,....
to
=VLOOKUP(A1,...
or something similar.

What is the format of W125 as compared with A1 (or whatever you used)? If
you copy the format from A1 and apply it to W125, does this make a
difference?
Does W125 contain data or a formula (and what about A1)? If a formula, what
is it?
 
Back
Top