posted on behalf of a friend..so sorry if any mistakes..

was wondering if you could post this on to a excel forum.. its driving me
round the bend and i cant manually do it this time becasue the spreadsheet
this is from has 64000 rows and i cant go through each one.

right i will try and explain this the best i can..

1) I have a lookup table which has BL_NO's in one cell and the month in the
cell next to it. e.g 781900109 SEP
2) i have a spreadsheet which has 64000 rows with bill numbers being in the
first column.
3) I have done a VLOOKUP to search for the bill numbers in my lookup table
with a purpose of putting the corresponding month in the month column in my
main data sheet. (as below).
4) however when i copy and paste the formula down the entire spreadsheet,
every row comes up with N/A (as below).
5) however i know that the bills in the lookup table are in the spreadsheet
so it should give me the month of "sep" in the month column for the bills
in question, but it doesnt.
6) BUT when i search for the bill numbers in the main spreadsheet and
double click the cell with the bill number in, it then changes the month
from N/A to Sep.
7) why does double clicking the lookup value cell enable the formula to
work.
8) this is the lookup formula i am using:
=VLOOKUP(A49480,Sheet2!$A$1:$B$103,2,0)
9) theres no way i can go through 64000 rows and double click each lookup
values.

screenshot can be seen here -
http://romseylionsclub.hampshire.org.uk/screenshot.jpg

please help.

It sounds like the calc mode's accidentally set to Manual.
Click Tools Options Calculation tab
Ensure Automatic is checked OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stuart Maclean" wrote:
posted on behalf of a friend..so sorry if any mistakes..

was wondering if you could post this on to a excel forum.. its driving me
round the bend and i cant manually do it this time becasue the spreadsheet
this is from has 64000 rows and i cant go through each one.

right i will try and explain this the best i can..

1) I have a lookup table which has BL_NO's in one cell and the month in the
cell next to it. e.g 781900109 SEP
2) i have a spreadsheet which has 64000 rows with bill numbers being in the
first column.
3) I have done a VLOOKUP to search for the bill numbers in my lookup table
with a purpose of putting the corresponding month in the month column in my
main data sheet. (as below).
4) however when i copy and paste the formula down the entire spreadsheet,
every row comes up with N/A (as below).
5) however i know that the bills in the lookup table are in the spreadsheet
so it should give me the month of "sep" in the month column for the bills
in question, but it doesnt.
6) BUT when i search for the bill numbers in the main spreadsheet and
double click the cell with the bill number in, it then changes the month
from N/A to Sep.
7) why does double clicking the lookup value cell enable the formula to
work.
8) this is the lookup formula i am using:
=VLOOKUP(A49480,Sheet2!$A$1:$B$103,2,0)
9) theres no way i can go through 64000 rows and double click each lookup
values.

screenshot can be seen here -
http://romseylionsclub.hampshire.org.uk/screenshot.jpg

please help.

hi there apparently that didn't solve the problem - any other thoughts..
many Thanks for your help!!

"Max" wrote:

It sounds like the calc mode's accidentally set to Manual.
Click Tools Options Calculation tab
Ensure Automatic is checked OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stuart Maclean" wrote:
posted on behalf of a friend..so sorry if any mistakes..

was wondering if you could post this on to a excel forum.. its driving me
round the bend and i cant manually do it this time becasue the spreadsheet
this is from has 64000 rows and i cant go through each one.

right i will try and explain this the best i can..

1) I have a lookup table which has BL_NO's in one cell and the month in the
cell next to it. e.g 781900109 SEP
2) i have a spreadsheet which has 64000 rows with bill numbers being in the
first column.
3) I have done a VLOOKUP to search for the bill numbers in my lookup table
with a purpose of putting the corresponding month in the month column in my
main data sheet. (as below).
4) however when i copy and paste the formula down the entire spreadsheet,
every row comes up with N/A (as below).
5) however i know that the bills in the lookup table are in the spreadsheet
so it should give me the month of "sep" in the month column for the bills
in question, but it doesnt.
6) BUT when i search for the bill numbers in the main spreadsheet and
double click the cell with the bill number in, it then changes the month
from N/A to Sep.
7) why does double clicking the lookup value cell enable the formula to
work.
8) this is the lookup formula i am using:
=VLOOKUP(A49480,Sheet2!$A$1:$B$103,2,0)
9) theres no way i can go through 64000 rows and double click each lookup
values.

screenshot can be seen here -
http://romseylionsclub.hampshire.org.uk/screenshot.jpg

please help.

Sorry, think I misread it earlier

Try using a little coercion on the lookup value:
=VLOOKUP(A49480+0,Sheet2!$A$1:$B$103,2,0)
The "+0" will coerce text number to a real number for correct matching
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stuart Maclean" wrote:
hi there apparently that didn't solve the problem - any other thoughts..
many Thanks for your help!!

Excellent - apparently that worked.. thanks for your help on this one...
Regards
Stuart

"Max" wrote:

Sorry, think I misread it earlier

Try using a little coercion on the lookup value:
=VLOOKUP(A49480+0,Sheet2!$A$1:$B$103,2,0)
The "+0" will coerce text number to a real number for correct matching
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stuart Maclean" wrote:
hi there apparently that didn't solve the problem - any other thoughts..
many Thanks for your help!!

Great to hear that.
Thanks for feeding back
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stuart Maclean" wrote in message
...
Excellent - apparently that worked.. thanks for your help on this one...
Regards
Stuart