 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2007VLOOKUP #N/A error. Sort and format are correct.
I have inherited two spreadsheets that are linked and a VLOOKUP in one is referring to a range in the other. The VLOOKUPs all work on the existing data but as when I add new data, bearing in mind the formatting of the cells and the order of the lookup range, those do not work. I get the #N/A error. Any ideas? What formula? What format and why does that matter? What values in the table and in the search argument?  SignatureKind Regards, Niek Otten Microsoft MVP - Excel >I have inherited two spreadsheets that are linked and a VLOOKUP in one is > referring to a range in the other. The VLOOKUPs all work on the existing [quoted text clipped - 4 lines] > > Any ideas? Sorry for being so vague. I have workbook ONE with a VLOOKUP in column O. The VLOOKUP uses column A and looks for that value in column T in workbook TWO and retrieves the value from column Z in the corresponding row. Workbook ONE Column A Column O 12345 VLOOKUP() 23456 VLOOKUP() Workbook TWO Column T Column Z 12345 XXXXX 23456 YYYYY When making a change to an existing row in workbook TWO, (i.e. changing the XXXXX in column Z to XXXYZ, the change is reflected in column O of workbook ONE. That works as it should. When I add a new row to workbook TWO and a corresponding on in workbook ONE, copying the VLOOKUP formula from the existing row that works as above, I get the #N/A error. The data in column T is sorted as it needs to be for the VLOOKUP to succeed. To follow from the illustration above adding 12789 in both column A of ONE and column T of TWO should give the VLOOKUP for that row a result of XXABC. Instead it gives the #N/A error. All columns are formatted as General, but I've also tried formatting them as Text and it made no difference. Workbook ONE Column A Column O 12345 VLOOKUP() 23456 VLOOKUP() 12789 VLOOKUP() Workbook TWO Column T Column Z 12345 XXXXX 12789 XXABC 23456 YYYYY The VLookup formula is as follows: =VLOOKUP(A15,'\\BBIC\CML\[TWO.xls]One Fund'!$T:$Z,7,FALSE) Hopefully this illustration has answered your questions and you may have some idea what is going on. Charlene > What formula? > What format and why does that matter? [quoted text clipped - 8 lines] > > > > Any ideas? Have you extended the lookup range to include the new data? Often this is caused because you are looking for "123" and the source range is 123. The string "123" does not match the number 123 (regardless of which is the term being looked for and the which is in the source data). If a cell is formatted as Text and you enter 123, you actually get "123" If a cell contains the number 123 and you then format it as text, the numer 123 is still stored in the cell. Perhaps something like this is the problem. SignatureRegards, Tom Ogilvy > I have inherited two spreadsheets that are linked and a VLOOKUP in one is > referring to a range in the other. The VLOOKUPs all work on the existing > data but as when I add new data, bearing in mind the formatting of the cells > and the order of the lookup range, those do not work. I get the #N/A error. > > Any ideas? Format of the cells is identical and the new row is within the range. See my entry in response to Niek Otten for an illustration. Thanks, Charlene > Have you extended the lookup range to include the new data? > [quoted text clipped - 16 lines] > > > > Any ideas? I just tried to tell you that the format of the cells is not the determining factor. The determining factor is how the value is stored in the cell. You can check how with =IsText() and =IsNumber SignatureRegards, Tom Ogilvy Tom, I think you may have been onto something with the istext and isnumber functions. However, I have now discovered what I believe to be the cause. The two spreadsheets were opened in different sessions of Excel and the reference was to the other workbook including the drive assignment. When I open the second spreadsheet as a second workbook in the same Excel session, and do exactly the same changes, I have no problem and no error. I discovered that when I tried to reference the cell of the second workbook for the istext function. Not sure whether the underlying cause was a memory issue or what, but in any case, my problem is resoved as long as I open both workbooks in the same Excel session. Closing off this request for assistance. Thank you very much. Charlene LeBlanc > I just tried to tell you that the format of the cells is not the determining > factor. The determining factor is how the value is stored in the cell. You > can check how with =IsText() and =IsNumber You definitely don't want multiple sessions. Glad you solved your problem. SignatureRegards, Tom Ogilvy > Tom, > [quoted text clipped - 20 lines] > > factor. The determining factor is how the value is stored in the cell. You > > can check how with =IsText() and =IsNumber Tom I believe this is the problem I have just been struggling with. Can you tell me please how I can convert a large chunk of data already entered and stored as numbers into text? Thanks Ann > Have you extended the lookup range to include the new data? > [quoted text clipped - 16 lines] > > > > Any ideas? Select the cells you want to convert and then run the macro. Sub converttotext for each cell in selection v = cell.Text cell.NumberFormat = "@" cell.Value = "'" & cell.Text Next End Sub SignatureRegards, Tom Ogilvy > Tom > I believe this is the problem I have just been struggling with. Can you [quoted text clipped - 23 lines] > > > > > > Any ideas? How do I run the macro below? I have never run a macro. Thank you so much! - JMcFarland > Select the cells you want to convert and then run the macro. > [quoted text clipped - 38 lines] > > > > > > > > Any ideas? Start here: David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm > How do I run the macro below? I have never run a macro. Thank you so much! > - JMcFarland [quoted text clipped - 49 lines] > > > > > > > > > > Any ideas? SignatureDave Peterson After I by right-click on the sheettab, choose 'view code', and then paste in the macro, how do I run the macro and how do I name the macro? Also, when I paste the macro below, Excel automatically puts parenthesis () after the first statement Sub converttotext() I tried putting the column (B) that I need the data converted but that didn't work, so then I tried (B9:B65536) and that didn't work either. Any suggestions? > Start here: > David McRitchie's intro at: [quoted text clipped - 53 lines] > > > > > > > > > > > > Any ideas? Don't rightclick on the sheettab, view code, and paste. If you did this, then remove that code. Open the VBE (alt-f11) Hit ctrl-f4 (to see the project explorer) Select your project Insert|Module And paste into that code window. Then back to excel Select your range to fix Tools|macro|macros|Select converttotext and click Run (alt-f8 will bring up that tools|macro|macros dialog, too.) > After I by right-click on the sheettab, choose 'view code', and then paste in > the macro, how do I run the macro and how do I name the macro? [quoted text clipped - 69 lines] > > > > Dave Peterson SignatureDave Peterson This worked. Thanks SO much. > Don't rightclick on the sheettab, view code, and paste. > [quoted text clipped - 86 lines] > > > > > > Dave Peterson When all else has failed for me, Charlene, I have done this. Insert a new column next to your Vlookup root data column enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2) slide a copy paste of the formula all the way down to copy your whole data column replace your root data with the special paste:values of the new column Charlene, I don't have to tell you to back up your file before attempting anything anyone recommends to you. Don't ask me why this might work, but it has resolved my vlookup issues in the past. Peace > I have inherited two spreadsheets that are linked and a VLOOKUP in one is > referring to a range in the other. The VLOOKUPs all work on the existing > data but as when I add new data, bearing in mind the formatting of the cells > and the order of the lookup range, those do not work. I get the #N/A error. > > Any ideas? If works if you are looking up numbers because the result is a number. See my previous post in this thread. SignatureRegards, Tom Ogilvy > When all else has failed for me, Charlene, I have done this. > [quoted text clipped - 21 lines] > > > > Any ideas? It's worth a try, but I'm not sure what you mean by 'root data'. Using the documented syntax of "VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)" Do you mean doing a copy of the lookup_value column or the table_array, or the range_lookup? I guess I could do all three .... Thanks, Charlene > When all else has failed for me, Charlene, I have done this. > [quoted text clipped - 21 lines] > > > > Any ideas? OK I did the formula to replace the contents of both the lookup_value and the table_array columns with no change in the result. The range_lookup is a text value. Charlene > It's worth a try, but I'm not sure what you mean by 'root data'. Using the > documented syntax of [quoted text clipped - 32 lines] > > > > > > Any ideas? You say everything is fine and is as it should be for vlookup to work, but vlookup doesn't work. Simple logic will tell you that the first part of the statement is then incorrect. SignatureRegards, Tom Ogilvy > OK I did the formula to replace the contents of both the lookup_value and the > table_array columns with no change in the result. The range_lookup is a text [quoted text clipped - 38 lines] > > > > > > > > Any ideas? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.