3 Replies Latest reply on Mar 9, 2015 12:26 PM by P. Brady Fish

    Missing "text data" in the imported cells of a linked excel sheet.

    P. Brady Fish

      Hopefully I am explaining this correctly. Here it goes:

       

      I have an Excel workbook that has a large amount of data, calculating many different things. Because of the size of the data, there is not an artful way to showcase all of the data from the particular sheet in the InDesign document.

       

      What I have done, is I have created a separate sheet (Sheet2) in Excel and have populated the Sheet2 via functions--as in I have entered the "=" sign in a selected cell of Sheet2 and have then gone back to Sheet1, selected the appropriate cell of the data I want to show in Sheet2, and hit enter. Excel then takes me back to Sheet2 showing me whatever was in the data cell of Sheet1. This is pretty basic stuff. This works.

       

      My issue is, when placing the Excel sheet (Sheet2) into InDesign as a linked file, any data that is text-based comes into the InDesign document blank. Any data that is numerically-based shows up fine.

       

      Is this an math issue? As in, the formula used in Sheet2 "=ABC" to get the text data from Sheet1, isn't reading as text "ABC" but rather Fx=A*B*C and trying to solve a math problem? And because it cannot make sense of the formula it doesn't put anything in? Is there something I need to put in, after the "=" sign? I have tried "=text(Subject!B87)" and, but I am still getting blank cells when importing into InDesign.

       

      Please save me.

        • 1. Re: Missing "text data" in the imported cells of a linked excel sheet.
          Cari Jansen Adobe Community Professional

          It's not behaviour I'd expect to see but indeed you are correct:

          • Under certain circumstances the Excel data is cleared from the field and an empty cell appears instead when placing Excel data into InDesign.


          By default InDesign will import Excel data as an unformatted table, and if this setting is used I can indeed see the text disappear from the cells as you see it.


          A work-around for the issue:

          • Choose File > Place, select Show Import Options, click select the file and click Open
          • In the Microsoft Excel Import Options dialog box, choose either Table: Formatted Only Once, or Table: Formatted Table in the Formatting section.
          • Click OK.


          In this case the text does appear in the table cells in the tests that I've just done.

           

          Cari

          • 2. Re: Missing "text data" in the imported cells of a linked excel sheet.
            MW Design Level 4

            The formula that should appear in sheet two is

            =Sheet1!B4

            for instance.

             

            I sometimes make a second sheet. Mostly I don't. I also (almost) never link to spreadsheets. The screen shot below is a spreadsheet I happened to be using right now. Calculated fields and plain text. And of course ID realized when I flipped back that I had saved what I was doing in Excel...

             

            capture-002872.png

             

            I don't think I personally have experienced the issue you are having. Does this happen on all spreadsheets or just this one?

             

            If correcting the formulas on a second sheet doesn't fix the problem, and you don't mind sharing the spreadsheet feel free to do so. If so, upload to dropbox.com or the like and, If publicly sharing, post the download link. If sharing privately, PM me the download link.

             

            Mike

            • 3. Re: Missing "text data" in the imported cells of a linked excel sheet.
              P. Brady Fish Level 1

              Thank you! It works!

               

              Perhaps you could advise on my next issue.

               

              Is there a way to format a table, and once I have it the way I like it, select/highlight the table and essentially tell InDesign to make a Table Style based on the selected material? This way I can import my slimmed down data from the excel sheets, highlight the table, and apply the style to it.

               

              Essentially, when the data imports form Excel, it looks like this:

              Capture.JPG

              And I am wanting to have the format like this:

               

              Capture2.JPG

               

              Thanks so much for all the help!