Link fields from Excel to Indesgin?

New Here ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

I currently use xero for accounting purposes, and generating quotes and invoices. I have a custom template that fits my company branding. I find it inflexible for making it look nice and I wish to move to indesign if possible.

 

I still need to use Xero and my thinking is that I can have all of the data in my excel sheet, and then somehow, this will link to indesign which will make it look pretty. And allow me to add descriptions.

 

I have been sending clients separate documents for quotes, invoices, and project scope but wish to merge the quote and project scope documents.

 

Basically in indesign it should look like this:

 

<Client>

<Client Address>

<Client phone number>

<Project Name>

 

2 pages containing a generic standard project scope

 

then job specific items that form the quote

 

<Item number>  <name> <Cost> 

Description done in indesign manually

 

<Item inumber>  dname> <Cost> 

Description done in indesign manually

 

<Item inumber>  dname> <Cost> 

Description done in indesign manually

 

etc

 

<Total Cost>

 

I found data merge to be inflexible as its design for one line of the linked file to be used per page. And placing a linked table was ugly and didn't provide enough formatting opportunities.

 

I'm ok will linking multiple files - one for client info and one for the lines of the quote but ideally it will be grabing fields from the excel sheet and linking them to whever I choose in Indesign.

 

The problem with my current setup is that each document needs the client details and there is a lot of repetition. Also I wish to include the project scope on the actual quote so that when they accept there is no question that they've agreed to it.

 

I hope that makes sense.

TOPICS
How to , Import and export , Scripting

Views

210

Likes

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
Adobe Community Professional ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

My $0.02 is that you should be using accounting software for this task. Frankly, nobody is going to care how pretty your invoice is and you're making a whole lot of extra work for yourself here trying to automate something that Quickbooks does right out of the box.

Likes

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 ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

I appreaciate most clients don't care but I do. The plan is to continue to use Xero and once the jobs are at the point where I need to invoice, I'll import the excel sheet into Xero. 

 

My example is also simplified a lot - and I have found the built in features in Xero weren't granular enough for my needs. My invoices can be quite complicated.

 

Likes

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 ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

You should revisit the data merge, it's more flexible than what are describing. Each column in excel can be linked to one formatted text box in InDesign or multiple text boxes (<name>). Don't use table, break it up into cells/columns.

Likes

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 ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

How would you do this, below is a simple example where you can see two data merge text objects that unfortunately are identical. You can also see a placed table. The table is in a format that can be imported into Xero. It lacks any other client info.

 

The table isn't flexible enough - ideally I can pick and choose where to link these and can standardise the process.

 

 

Likes

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
Adobe Employee ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

Hi @davids70642166 ,

 

Thanks for reaching out. In addition to the suggestions shared by @ck_ny, if you need more information on Data Merge, please check this article https://helpx.adobe.com/indesign/using/data-merge.html 

 

Regards

Rishabh

Likes

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
Adobe Community Professional ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

I would suggest looking at a database plugin for InDesign. Companies include Em Software, 65bit, and Teacup Software. They offer greater flexibility with some logic options (if/else) available. 

 

David Creamer
Adobe Certified Instructor, Adobe Certified Professional, and Adobe Certified Expert

Likes

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 ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

They definately look like they'd do the trick but I'm hoping for a built in feature if possible. I have heaps of subs and they really add up.

Likes

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
Adobe Community Professional ,
Jul 28, 2022 Jul 28, 2022

Copy link to clipboard

Copied

I can think of only two built-in options:

  • Data Merge
  • Linked Excel file (You could do all the invoice work in a relational Excel workbook and link the invoice worksheet to InDesign; format the ID table with only cell and table styles.)

 

David Creamer
Adobe Certified Instructor, Adobe Certified Professional, and Adobe Certified Expert

Likes

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
Adobe Community Professional ,
Jul 29, 2022 Jul 29, 2022

Copy link to clipboard

Copied

I would also look at fully formatting the Excel file and placing it as a formatted table instead of using table / cell styles. 

Likes

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 ,
Jul 30, 2022 Jul 30, 2022

Copy link to clipboard

Copied

Thanks I'll try these ideas. I didn't realise that you can pre-format the table.

Likes

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
Adobe Community Professional ,
Jul 30, 2022 Jul 30, 2022

Copy link to clipboard

Copied

Certainly, give it a try as it might be all you need. However, wouldn't you be able to print directly from Excel at that point?

 

IMO, Microsoft formatting is good for Excel, but not for publishing. About the only formatting I would do in Excel is merging of cells. 

 

BTW, keep in mind that any text you enter in InDesign is going to be wiped out if you update the link to the Excel file. 

David Creamer
Adobe Certified Instructor, Adobe Certified Professional, and Adobe Certified Expert

Likes

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 ,
Jul 30, 2022 Jul 30, 2022

Copy link to clipboard

Copied

The challenge I've got is that Xero invoices aren't flexible enough for my needs. Plus I wish to merge my project scope document into my quote as I'm often chasing clients to sign this even after they've paid.  It if it's attached to the quote then it's a no brainer. They've seen it an accepted it. 
For invoicing payments are done in stages. 50% deposit, 30% progress payment, and 20% balance. These jobs run over 10-12weeks typically. 

 

it's rare for the scope to be the same at the 30% point. Clients usually buy more images once they see the rough options. 

I need a way to have an invoice showing the totals (without having to make quantities 0.5 for the 50% deposit invoice. 0.3 for progress etc. 


ideally it would be handled by a line at the end under the total. Something like

Total = total

Total incl tax =
payments made already:
Deposit paid = deposit paid,  on date

30% due today = $ 

 

and because I'm fudging it by changing quantities per line I'm having to keep track of this and I'm creating separate invoices in zero for each iteration (50, 30, 20%) which means that on each subsequent invoice it doesn't say $x paid to date. 

And Xero uses preloaded word documents for it's invoicing templates  with fields which aren't flexible to do this or tweak per project. 

I'd ditch Xero in a heartbeat if I found something better that also does payroll and super. 

(typing on iPhone sorry the formatting isn't great)

Likes

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 ,
Jul 30, 2022 Jul 30, 2022

Copy link to clipboard

Copied

The main thing that I can't accept in Xero is that there is no built in function in the invoicing to handle deposit or interim payments.

Likes

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 ,
Jul 30, 2022 Jul 30, 2022

Copy link to clipboard

Copied

I accidentally marked one of my answers as correct too. Seems there isn't an option to edit. 

Likes

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
Adobe Community Professional ,
Jul 31, 2022 Jul 31, 2022

Copy link to clipboard

Copied

LATEST

I would point out that InDesign does not have a built in function for it, either. Look, if this is what you really want to spend your time on, far be it from to discourage you, but you need to be aware that you're in "square peg in a round hole" territory here.

 

That's why I use QuickBooks and I haven't had one client tell me my invoices are ugly. 

 

All of that out of the way, if you come up with something workable, do come back and let us know. It might help someone else in the future.

Likes

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