Vlookup not working the same after 2010 & Windows 7 upgrade

[stumpedagain]

I have created a company document that pulls data, mainly with vlookup functions, from what i call master files located on a network drive. A new 'component', 'test method', 'customer', ect. could be added to the master file and it would update all documents in circulation (once opened and links enabled).

It has worked spot on for over 10 years - until recently. Last month our company upgraded from office 97, Windows 2000 to Office 2010 nad Windows 7.

here is the problem...

I enter a new component in the master file (named YIDB) by inserting a line (alpha numerical order) and save the file. Then i open the document but it does not recognise the insertion of the new component.

this is an example of one of the tables (YIDB) pre and post update

<TABLE style="WIDTH: 351pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=470><COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1251" width=35><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5262" width=148><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1251" width=35><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1194" width=34><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1251" width=35><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5262" width=148><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1251" width=35><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 26pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=148>original lookup table</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=34></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 111pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=148>updated lookup table</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=35></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>Item</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Description</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Yield</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Item</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Description</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Yield</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl63 height=19>Y3327</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl64>YN PLAC TX 150/68 BRT RD</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl65>29842</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl63>Y3327</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl64>YN PLAC TX 150/68 BRT RD</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red 0.5pt solid; BORDER-RIGHT: red 0.5pt solid" class=xl65>29842</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl63 height=19>Y3329</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl64>YN PLAC RS 36/1 INGEO SD RD</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl65>30240</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl66>Y3328</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl67>YN POLY TX 100/96 SD KS</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl68>43985</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl63 height=19>Y3330</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl64>YN PLAC RS 24/1 INGEO BRT RD</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl65>20160</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl63>Y3329</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl64>YN PLAC RS 36/1 INGEO SD RD</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl65>30240</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl63 height=19>Y3331</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl64>YN PLAC RS 8/1 INGEO SD RD</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl65>6720</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl63>Y3330</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl64>YN PLAC RS 24/1 INGEO BRT RD</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl65>20160</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl63>Y3331</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl64>YN PLAC RS 8/1 INGEO SD RD</TD><TD style="BORDER-BOTTOM: red 0.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: transparent; BORDER-TOP: red; BORDER-RIGHT: red 0.5pt solid" class=xl65>6720</TD></TR></TBODY></TABLE>

The document allows for entry of the Item in cell D11 with the following lookup in cell G11 which will pull the item description into the document.

=IF(ISBLANK(D11),"",(VLOOKUP(D11,YIDB,2,FALSE)))

since the upgrade, when i insert a row in the YIDB Table (blue) and enter Y3329 in the document, it still pulls from row 2, which now reads Y3228.

i just cant get past it......... any help would be appreciated.

 

[Asator]

Call me crazy.. but have you tried pressing F9 to recalculate? What you're doing should work.

If that fixes it, check and see if you have excel set to calculate automatically.

 

[stumpedagain]

LOL, im beginning to think i am the crazy one.

yes, have tried F9, Data-Refresh All, there is just no rhyme or reason to it.

i neglected to mention that the document is saved as .xlsm, a macro enabled doucment but the lookup tables are still .xls.

Not everyone in the company is saving as the same file time - I Not sure if that could be causing the problem or not.

I wanted to try saving all worksheets as the same file type but when i save the tables as .xlsm it saves a 2nd copy on the network drive with my documents vlookup still pointing to the original.

Is there an easy way to get all the lookups to point to the new table?

 

[henrik2h]

Could it be that the option to update external links (or whatever the name is) is unchecked?

Couldn´t you use Find / Replace for the entire workbook to change the formulas with vlookup?

 

[stumpedagain]

connect links is checked...

since everything else is checked and linked the only other thing i could think of is that maybe it is a glitch because the master document is now .xlsm (2010) but the look-up tables are all .xls (2000). ???

The thing that really has me hung up is when i try to save the table as xlsm it creates a 2nd document with the same name but the new extension. How do i change the lookup to point to a file with a differnt file extension ?? YIDB.xls to YIDB.xlsm? do i have to change the lookup table name and then find and replace everything?

Yes could use find and replace but i have 4 different lookup tables so it will be quite an undertaking.

Thanks for your suggestion though

 

[henrik2h]

If the only change is from .xls to .xlsm then one find and replace (find YIDB.xls and replace all with YIDB.xlsm would fix all the formulas in seconds? Wouldn't it?

 

[stumpedagain]

yes, but that is not the case - most formulas look like this...there is no file extension. that is my dilemma.....
<TABLE style="WIDTH: 36pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=48><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1706" width=48><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl120 height=20 width=48> </TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 36pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=48><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1706" width=48><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 36pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl122 height=20 width=48> </TD></TR></TBODY></TABLE>=IF(ISBLANK(D8)," ",IF(AG2=TRUE," ",IF(M$3=999," ",IF(M$3="PUR"," ",VLOOKUP(D8,YIDB,19,FALSE)))))