Excel help please!

[RobM77]

Could anyone help with an Excel query I have please. I'm totally stuck - I've looked on the Internet and asked at work but have got nowhere!

I have a worksheet in a workbook containing around 1300 rows and about 15 columns. Every cell contains data. One of the columns in this first sheet is populated by using a lookup to another spreadsheet, which also has around 1300 rows, but this time with only 2 columns. The lookup function in each cell in the aforementioned column of the first spreadsheet looks for the value in that row's column 1 (a kind of 'key field'), then goes and looks for this value in the other spreadsheet's column 1. When it finds the value in column 1 of the second spreadsheet, it grabs the value in the 2nd column and inserts it into the cell in question in the second sheet.

Example:

1st sheet (theone with the lookup):
A (lookup in this cell resolves to 3 - see 2nd sheet below)
B (as above, this equals 5)
etc...

2nd sheet (the one that holds the data):
B 5
Q 9
S 8
A 3
etc...

Now, my problem is that this lookup works fine up until the 966th row in sheet 2, and then it just stops working!! After that there are plenty of values in the 1st column that appear in the 1st sheet, but it refuses to look them up! You can put what you like in the cells after 966 and it just won't pick it up. Any thoughts? Does lookup have a size limitation or something?

I'd be very grateful for any help!

[Vee]

You need to 'fix' the lookup range.

ie.
=vlookup(A1,[OtherWorkbookName]SheetName!$A$2:$D$16,2,0)

Ypu'll be missing the $

Basically the lookup range is moving to blank cells.

[FunkyNige]

Does the format change at that row?

[RobM77]

Vee said:

You need to 'fix' the lookup range.

ie.
=vlookup(A1,[OtherWorkbookName]SheetName!$A$2:$D$16,2,0)

Ypu'll be missing the $

Basically the lookup range is moving to blank cells.

I've got the dollars, and the lookup range is therefore fixed.

[RobM77]

FunkyNige said:

Does the format change at that row?

Good point, I'll check that. I know that the format of the colum it's feeding back doesn't change (as it is boolean, and I can see a colum of 'yes' and 'no', where 1 and 0 have been inputted). The other column has alphanumeric codes in it, so I doubt the format changes there, but I'll have a look - thanks.

[vee]

Try using =len(A1) where A1 is the cell you want to look up.
Copy it down and see if it changes drastically.
email me the file if you want and I'll take a look - [e-mail]

[FunkyGibbon]

have you got duplicate values in your lookup column?

the look-up function can get very confused if there are multiple values it thinks are the same.

if can also get confused if your haven't sorted the lookup data.

[Miles_stylus]

does it make any difference if you leave it for a while - lookups can be pretty intensive and on large sheets I've just sat for 5 mins waiting for them to update!

What is shown in the destination cells?

As an asside I would name the target range rather than using $a$1:$b$3000 as it avoids any issues etc.

HTH

Miles

[RobM77]

Thank you for your replies

vee: The codes that are cross referenced are between 3 and 8 digits long, and the value that's copied is a 1 or a 0. Thank you very much for your offer of taking a look, but I'm sorry, I can't e-mail it to you as I work in a secure environment with no external e-mail or removeable media.

FunkyGibbon (what a great name!): There are no duplicates and data is in alphabetical order with respect to the cross referenced codes.

Miles: Leaving it for a while doesn't do much - besides my version of Excel has a progress bar for updates in the bottom left corner of the window. Also, updates up to cell 966 occur instantly - no dealy at all. Beyond that cell they don't work at all...

I really need this to work still, so if anyone has any ideas I'm all ears! thanks for the responses so far.