G
Guest
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.
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.