• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Placing Excel sheets in InDesign with functions .

New Here ,
Aug 05, 2020 Aug 05, 2020

Copy link to clipboard

Copied

Hi, 

 

I am trying to place a linked Excel file into InDesign where information can be updated on my Excel file and then update automatically in my InDesign file by refreshing the link. My Excel file has active functions that pull data from other Excel files. When I try to place the Excel file, that has the correct info listed, in InDesign the info is replaced with -1 or -1.00. Some of the missing info that is listed as -1 or -1.00 is text and some are numbers. I have added an example of the info is being placed below. My Excel program is closed and in InDesign I have changed preferences file handling to create links when placing text and spreadsheet files. I also choose show import options when placing the linked excel file in InDesign. I have tried changing the size of the text along with making the cells bigger in InDesign. Is this possible to place a linked Excel file with functions? 

Examples below. 

 

Thanks Nic

 

- Correct info that is listed in Excel that is being pulled from another Excel file. 

Indesign help1.jpg

 

- How the info is being placed in Indesign. 

Indesign help2.jpgIndesign help3.jpg

TOPICS
How to , Import and export

Views

1.0K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 07, 2020 Aug 07, 2020

Copy link to clipboard

Copied

What OS and what version of InDesign? Do you have the latest?

Mike Witherell

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 07, 2020 Aug 07, 2020

Copy link to clipboard

Copied

I am on a HP and have InDesign 2020 and Excel 2016. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 07, 2020 Aug 07, 2020

Copy link to clipboard

Copied

Nic said: "…Is this possible to place a linked Excel file with functions?"

No. Best "flatten" your cells so that the right values are there without using any function.

 

Regards,
Uwe Laubender

( ACP )

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 07, 2020 Aug 07, 2020

Copy link to clipboard

Copied

So InDesign can only imput/place data from Excel that is listed without functions or formulas? 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 07, 2020 Aug 07, 2020

Copy link to clipboard

Copied

Hello, you should have a look at Docsflow from Emsoftware 😉

http://emsoftware.com/products/wordsflow/

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 06, 2020 Aug 06, 2020

Copy link to clipboard

Copied

What does -1 mean in a placed excel chart? I keep getting that instead of my info when I try place the linked excel file in indesign. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 06, 2020 Aug 06, 2020

Copy link to clipboard

Copied

This potentially could be a bug. There was an issue with the numbers importing as 0 in earlier versions of ID 15: 

https://indesign.uservoice.com/forums/601180-adobe-indesign-bugs/suggestions/38971090--bug-indesign-...

What's your OS and ID version?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 06, 2020 Aug 06, 2020

Copy link to clipboard

Copied

Im using a hp. My Indesign version is 2020 and is updated. Does that answer your question? 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 06, 2020 Aug 06, 2020

Copy link to clipboard

Copied

Can you paste the excel file here and identify the cells that are importing as -1? I would attempt to change the formatting of the problem cells in Excel from whatever format they are to Text, or if they are formulas, to convert them to values only. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 06, 2020 Aug 06, 2020

Copy link to clipboard

Copied

Pretty much all the cells are importing as -1. 

Excel wont allow me to change the fromatting since I am pulling this data from another Excel file. I pulled this data from the master list ( Excel file 1) that has more info than I need. I then created another Excel file( Excel file 2) that pulls the data from the master list (Excel file 1) that represents the data/layout I need for my chart that I am designing in InDesign. I want InDesign to automatically update my design chart that is pulling data from Excel file 2. I want Excel file 2 to automatically update the info that is being pulled from Excel file 1 that is manually entered. 

 

My goal is to be able to have a person that does not have InDesign to be able to make changes on the data in the master Excel file. Once they hit save on their master Excel file, my Indesign chart links will then need to be refreshed and all the correct data is listed in the correct spot because the data is being pulled from the design excel file that I created (Excel file 2).  

 

Master Excel file(1) > Design Excel file (2) > InDesign Charts > Refresh Links

 

Right now when I do this, my data comes in as -1 for numbers and text.

 

Excel file correct layout. Datat being pulled from master list

Excel FileExcel File

 

InDesign File. Placed the excel data and data comes in as -1. 

Imported into Indesign. Placed the dataImported into Indesign. Placed the data

 

Settings I have for placing linked files

settings showing it was linkedsettings showing it was linked

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Aug 08, 2020 Aug 08, 2020

Copy link to clipboard

Copied

Hi Nic,

my assumption here is that value -1 is always used when InDesign's import filter discovers a value that cannot be interpreted.

A standard procedure developers do to enforce a default value. That is going wrong here, because -1 instead of the default, the calculated values from your Excel formula, is used.

 

As I said, best do not use formulas.

 

Regards,
Uwe Laubender

( ACP )

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 26, 2020 Nov 26, 2020

Copy link to clipboard

Copied

Hi Uwe,

same here on Mac with InDesign 2020. So I cannot use Excel with formulas.

Bildschirmfoto 2020-11-26 um 12.08.35.pngBildschirmfoto 2020-11-26 um 12.08.52.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 26, 2020 Nov 26, 2020

Copy link to clipboard

Copied

Import with Formatting only it looks like

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 26, 2020 Nov 26, 2020

Copy link to clipboard

Copied

Hi steverocket,

I see this as well with my Excel 2010 and InDesign 2021 on Windows 10.

 

Workarounds:

[1] Save the spreadsheet file as *.xls from Excel and place the *.xls file.

*.xlsx will not work.

[2] Select the cells in Excel, copy, select one cell in InDesign, paste.

 

Regards,
Uwe Laubender

( ACP )

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 27, 2020 Nov 27, 2020

Copy link to clipboard

Copied

Hi Uwe,

unfortunately, resaving to .xls is not an option.

I have found the following workaround for InDesign 2020 (Mac).

Since, the "-1" shows only when importing WITHOUT formatting,

I imported WITH formatting and then converted the table to text.

This way the content for linked content in Excel will show also in InDesign.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 27, 2020 Nov 27, 2020

Copy link to clipboard

Copied

Hi Steve,

thanks for the next workaround!

Hm. I'd prefer to save to *.xls as interim format, because placing with formatting could spoil my styles and swatches panels.

 

All in all this is not an acceptable situation.

I'd look for a bug report at InDesign UserVoice and start supporting a fix there or do my own report:

https://indesign.uservoice.com/forums/601180-adobe-indesign-bugs

 

Regards,
Uwe Laubender

( ACP )

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 22, 2022 Sep 22, 2022

Copy link to clipboard

Copied

LATEST

There is 1 other work around not mentioned here that is relatively simple. (Although you wish Adobe would just fix this).

 

Basically keep 2 sheets (or however many you need). Label the first "ABC - Forumlas" and the second "ABC - Production"

 

Keep to two sheets exactly the same. Except keep your second sheet where you copy and paste the values only. Whenever you need to update, just open up the excel and do a quick copy and paste over.

 

Not an ideal solution, but could be depending on what your use case is.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines