4 Replies Latest reply: May 15, 2014 10:00 AM by vcrow _ RSS

    External Lookup Error - Please help!

    vcrow _

      I am working with the same information that is being used on a co-worker's computer.  He sent me the model, report and the excel file to use in the external lookup.  The same files work properly on his computer.  Upon trying it on mine, I edited the external lookup to find where my excel file was saved, etc.  However, I keep getting the message that "the source and table window columns must be of the same type."  He and I have been through every detail - formatting, etc. over the telephone while we're each at our computers.  There is absolutely NO difference in anything, yet, the external lookup will not work for me. 


      One other detail that might help someone. . . when viewing the sample data while editing the external lookup - there are 4 decimal places after the numbers in my columns.  They do not appear in his, nor is my file saved that way.


      I would greatly appreciate any information you all may be willing to share.  He and I have both worked on this for hours trying to figure out what is wrong.


      Thanks in advance.

        • External Lookup Error - Please help!
          Nigel Winton

          Hi There

          I have had similar problems with Excel, I found it was converting a text field to number when saving on a different machine. Excel was showing a cell as number even if it was formatted as text, hence your decimal places.

          Try opening your Excel file and in a seperate column use the formula =Text('The number cell',0). This will take your number cell and convert it to text. Then copy this cell over the original number cell and paste as values. Repeat this for all cells in the offending column.

          Hopefully then the Monarch Lookup should find a text field it is looking for and work OK.





            • External Lookup Error - Please help!
              RalphB _

              Hi, and welcome to the forum.


              I just want to offer another option to Nigel's great option of converting numbers to text that I have used is to hi-lite the  entire column or just the range numbers and then on the toolbar, select "DATA", under that, select "Text to Columns".  This opens the "Text to Columns Wizard". In the first step select "fixed width", then hit next twice to get to step 3.  There, under "Column data format", select "Text" and then press "Finish".


              This is quick and easy method and if you want, it is easy to write a macro or script to automate this.


              Try both methods and decide which works best for you.



            • External Lookup Error - Please help!
              vcrow _

              I finally had a chance to try the suggestions.  Nigel, I couldn't get yours to work.  however, I only tried once before moving on to the next option and it worked.  Thank you both so much for your help!  Now I know where to come if I have any more problems rather than wasting so much time trying to figure it out myself.


              Thanks again!