Keeping the format when updating the spreadsheet link

[Attroll]

I have imported a spreadsheet to my InDesign and linked it.

I love this option.

I edit the spreadsheet in excel and save it.

Then when I open my InDesign, it ask me to update the link.

I click UPDATE LINKS, and it does update the spreadsheet in my document.

However my formatting that did earlier to the spreadsheet inside of InDesign is gone.

I am referring to cell spacing.

Is there a way to retain that so that I do not have to fix that ever time I update the link?

[Barb Binder]

Why don't you make a table style (and nested cell styles) so that after you edit, you can reassign the formatting with a single click?

[Barb Binder]

This post from me might help with the concept:

https://www.rockymountaintraining.com/adobe-indesign-tables-that-wont-accept-table-and-cell-styles/

And this one from Adobe will help with the execution:

Table and cell styles in InDesign

[Attroll]

I don't know if I can nest a table with cell styles like you suggested.

The reason being is that all my cells are different.

I have some merged cells throughout the spreadsheet.

Can I still nest the style if I have merged cells through the spreadsheet?

[Barb Binder]

You can nest up to five, but are limited to these 5 positions:

Anything else and you will have to reassign through the Cell Styes panel, manually. You can assign styles to merged cells.

If you want to keep the ability to update the linked excel file, I really think styles are your best bet after an edit.

[Attroll]

IN this case, I do not need headers or footers.

I will attach a small sample of my spreadsheet to try to show what I mean.

The spreadsheet is not a simple spreadsheet.

I have six columns. In some instances the columns are merge.

I have 4,320 rows of data. So you can see why I want to make this work.

I don't want to have to update my settings every time I click on Update Links for this spreadsheet in InDesign.

In this small sample I have highlighted all the merged cells in yellow.

Will I be able to nest something like this?

[Barb Binder]

Can you share the same part of the table in InDesign?

[Attroll]

Barb, I am not sure exactly what you are asking.

My table is just like the sample above except it has 4,320 rows instead of just the 17 in the sample above.

[Barb Binder]

The screen shot you shared is from Excel. I was asking to see the same data in InDesign so that I could see your formatting and answer the question about nesting. That said, 4320 rows is a substantial table, and assigning styles manually after updates is not going to be fast or fun. If there is a plug-in that would work, I would definitely explore that option first.

From the link that Haeme Ulrich shared:

The Pro version of WordsFlow also works with imported Excel spreadsheet files as well as Word files. You can place a spreadsheet file, then format columns/rows or individual cells, etc. in InDesign. Later, if you or someone else makes changes to the original spreadsheet, including moving around rows or columns, WordsFlow will figure out what changed, and merge in the changes without losing the formatting and edits you’ve made in InDesign.

And I see that they offer a trial version. Take a look. And it would be awesome if you could come back and let us know if WordsFlow solves this for you.

[Attroll]

I can send you a screen shot in InDesign later if that will help you help me. I really appreciate this.

The plugin that Haeme was referring to cost $300.00. That is quite expensive. That cost more the the InDesign program.

[Barb Binder]

It might be worth it, over the long run.

I'm just trying to see the formatting of the table, cells and paragraphs on a typical page of the table, in InDesign, not in Excel.

[Attroll]

Barb

I hope this was what you meant.

Here is the two, side by side.

Excel on the left, InDesign on the right.

I left the merged excel cells highlighted just so you knew which ones were merged.

I don’t usually highlight them.

When importing the excel file into InDesign, I use the “place” command and pick “show import options”. Then I pick “format table” in the formatting area. Everything else I leave alone.

If you need anything else, please let me know.

Thank you

Rick

[Barb Binder]

Do the symbols come in correctly? The bold text? The very small text in the 4th column? Or are you adding all that after the table is in InDesign?

What exactly are you losing when you update the spreadsheet?

[Attroll]

Yes all the symbols are coming in correctly with the import. I have no problem with the cells and the table.

Here are the steps I do.

1. I do the original import

2. Tweak it a little to make is span my page properly.

3. Set the row heights spacing as small as I can, like you told me to do in the other thread.

3 Save the file in InDesign.

4. Make an edit to make to the spreadsheet.

5. Open it back up in InDesign.

6. It tells me that the link has an update.

7. I click on Update Link.

It does update the link but the row height spacing that I created in step 3 is all lost.

Every time I make and edit to the spreadsheet and open my file back up in InDesign, I lose the row height spacing. I have to go back and edit the cell top and bottom inset and change the "at least" size.

Is there any way to avoid doing this every time?

[Geоrge]

Row height is not a setting for cell style in InDesign. I don't know why, need to do feature request about it by many people.

Instead to WordsFlow I can recommend to you to use Woodwing SmartStyles plugin to beat this problem, but him too not so cheaper. But this plugin will be helpful to forget about many problems with applying table/cell styles without worrying about changes on the link of xls, just reapply the style after have some changes.

[Barb Binder]

Well, that's way better than I anticipated. If I understand you correctly, after an edit, you need to select the table, reset the width and the cell height. My best advice is to leave the table alone until all of the edits are entered into the Excel file. When all the edits are in place, then make the two adjustments.

[Laubender]

For resetting the width I can recommend a script by Gerald Singelmann, autoColumn 2 :

Set table-columns numerically II | InDesign FAQ

Direct link for the script:

http://indesign-faq.de/files/2009/11/as2.zip

It comes with two files:

AutoSpalte2.jsxbin

loadAutoSpalte.jsx

Regards,
Uwe

[Attroll]

Laubender

I believe the one you referred me to is for setting columns. I want to set my row heights.

[Laubender]

Hi,

yes indeed.

Gerald's script is for adjusting, saving and storing the column width with a Table style (optional).

For the height of the cells or rows (and other formatting options as well) I can only recommend Woodwing's SmartStyles plugin as George Salnik already did in reply # 17.

Regards,
Uwe

[Laubender]

Attroll wrote:

…I want to set my row heights.

Did you set them to fixed height?

If yes I would change that so that the height is growing automatically. Use: Auto grow

You could adjust the minimum height in one go for all the cells to the minimum that is allowed.

The insets and the text then will dictate the height of the rows automatically.

Other formatting issues like applying the cell's background color can be done by using cell styles.

Text formatting could be done by using cell styles perhaps with a clever way of applying paragraph styles with nested and GREP styles. Or with a bunch of GREP Find/Replace actions…

Custom scripting can help as well.

But as I already said, try out Woodwing's SmartStyle plug-in. That could store e.g. table designs (and other things) with InDesign library files. And yes, I think it's worth the investment, if you are not billed by the hour but by the project.

Regards,
Uwe

[Attroll]

I have not found a solution for this yet but to better explain the settings that I am trying to retain, here the the ones I have to continually keep changing back to.

Row Height “At Least” 0.0417

Top Cell Insert 0.02 in

Bottom Cell Insert 0.02 in

[BobLevine]

A combination of Smart Styles and WordsFlow will do exactly what you want.

[Attroll]

Thank you Barb for trying.

I figured I was going to have to do it this way but I was trying to avoid it.

[Barb Binder]

Always happy to help,

[Haeme Ulrich]

Another strategy (other than Table and Cell Styles) would be to use an InDesign Plugin. I would use DocsFlow Pro. With it you can update linked Excel Files very easy without loosing the InDesign styling:

http://emsoftware.com/products/docsflow/