• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup Working but only till Row 130, at row 131 stops working?

A

AJExcelStar

New Member
Hi,


If you know any way to resolve this, PLEASE send in your suggestions:


I am using a VLOOKUP formula to populate the G Column with Yes or No in a new Excel 2010 Report. The number of rows change every week and each week a new report is prepared by comparing with the last week's report. The VLOOKUP formula I am using works fine till Row 130, beyond that it does not work at all and gives the same value in all cells thereafter, a date that is 31/0/1900. The formula is


=VLOOKUP(A127,'d:dataAJBanerjiDesktop[ThirdPartyConnectionsReport 76 Jan 21 2012.xlsx]ThirdPartyConnectionsReport'!$A$1:$G$65536,7,FALSE)


PLEASE ADVICE


Thank you for your help


AJ
 
Hi AJ ,


A date such as 31/0/1900 appears strange ; can you format the cell as General or Number , and see what it displays ?


Secondly , you say everything works fine till row 130 ; can you post the contents of the following cells , after formatting all of them as General / Number / Text ?


A130 , A131 , and where these values can be found in 'd:dataAJBanerjiDesktop[ThirdPartyConnectionsReport 76 Jan 21 2012.xlsx]ThirdPartyConnectionsReport'!$A$1:$A$65536


and what are the corresponding values in column G of the above workbook.


Otherwise , if it is possible , delete all rows beyond 130 , format row 131 as General , enter fresh data into A131 , enter the formula where ever it is supposed to be entered , and see what you get ?


Narayan
 
AJ


I have renamed your post getting rid of the "HELP PLEASE" and "!!!"


All posts get equal and free attention and in fact I generally find I ignore or delay reading posts when they are marked URGENT, HELP PLEASE etc.


I would refer you to read the Chandoo.org Forum Rules and Etiquette for more guidance: http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting
 
Hi Narayan,


Thank you for your prompt response. Changing the format to Text or General makes it the number 0, and changing the format to Number makes it 0.00


Sorry about that date, it is not 31/0/1900 but just 1/0/1900.


These reports exceed 6200 rows and each week, the number of rows goes up by an arbitrary number mostly in the 100+ range.


The data and the files exist on Sharepoint but I am using the VLOOKUP formula on my local drive. So the reference file as well as the new report are copied on to my hard disk.


I am doubtful whether Row 130 is a limit row for Excel 2010, as I am seeing such posts in other blogs as well.


I will look forward to your suggestions. Thank you very much for your help!


AJ


Hui - I understand what you wanted to convey, I have gone through the link, thanks for your guidance.
 
Hi Narayan,


Just forgot to add that the format of all cells in column G is actually the very same, from G2 till G6378 it is format Custom - [$-10409]m/d/yyyy
 
Hi AJ ,


Basically , the value is 0 ; the format is just to display whatever we want. That answers my first doubt ; there were two more , can you reply ?


Secondly , you say everything works fine till row 130 ; can you post the contents of the following cells , after formatting all of them as General / Number / Text ?


A130 , A131 , and where these values can be found in 'd:dataAJBanerjiDesktop[ThirdPartyConnectionsReport 76 Jan 21 2012.xlsx]ThirdPartyConnectionsReport'!$A$1:$A$65536


and what are the corresponding values in column G of the above workbook.


Otherwise , if it is possible , delete all rows beyond 130 , format row 131 as General , enter fresh data into A131 , enter the formula where ever it is supposed to be entered , and see what you get ?


Narayan
 
Hi Narayan,

Thanks again for your prompt response! Column G is populated either with Yes or No. To answer your second question, if the cells 131 till the end of the report are formatted General or Text, the result is that the value shown in 0, where as if the same cells are formatted Number, value shown is 0.00


A130, A131 values are product codes UKA1 and UKA3 and corresponding values in G should be No and Yes, but when I use the formula the corresponding values in G show No and 1/0/1900.


Deleting all data below Row 130 and entering data freshly, leaves the G Column data blank, there is nothing that comes in there.


Cannot just figure out what is going wrong and where.


Thanks for your help again!


AJ
 
Hi AJ ,


Each time one question is answered , leaving one more for the next time !


A130 contains UKA1

A131 contains UKA3


Where do these values appear in the lookup range :


'd:dataAJBanerjiDesktop[ThirdPartyConnectionsReport 76 Jan 21 2012.xlsx]ThirdPartyConnectionsReport'!$A$1:$A$65536


Suppose we assume that UKA1 appears in A2431 in the above workbook / worksheet ; what does G2431 in the above workbook / worksheet contain ? This is the value we are going to pull in , into G130 in our working sheet ; will G2431 contain the text No or Yes ?


Narayan
 
Hi Narayan,

Thanks again for your help. I thought I answered all your questions, sorry about missing out the last one. As I said in my last response, if A2431 contains UKA1 in the reference report, G2431 contains contains No and hence the formula populates the G column at G130 with No in the new report. So your saying "This is the value we are going to pull in, in to G130 in our working sheet;" is something that the formula has done already correctly.


However, the formula is not working beyond that row, so though A2432 contains the value UKA3 and though G2432 contains Yes in the reference report, the formula populates the G131 with 1/0/1900 only. Irrespective of whether G column value in reference report an Yes or a No, the corresponding G cell contains the same value after row 130, it is only 1/0/1900.


The format of all cells in reference report and in new report are same all through are same, from first row to last row.


Is there any other way of doing this? If no, why is the formula not working correctly?

I just hope this time all your queries were answered properly.


Thanks for your help.


AJ
 
Hi ,


I really cannot imagine such a scenario , where Excel does its calculations correctly till a particular row , and not thereafter ; can you use the Evaluate Formula to step through the evaluation of the formulae in the two cells A130 and A131 , to see at what step the formula in A131 does not work the way it is supposed to work ?


If even this does not give any pointers , then the only way is to upload both the workbooks , the one which has the formula , and the one into which you are doing the VLOOKUP.


Narayan
 
You must log in or register to reply here.
Back
Top