Link fields from Excel to Indesgin?

[davids70642166]

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.

[BobLevine]

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.

[davids70642166]

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.

[ck_ny]

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.

[davids70642166]

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.

[Rishabh_Tiwari]

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

[Creamer Training]

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. 

[davids70642166]

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.

[Creamer Training · Accepted solution]

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.)

[BobLevine]

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

[davids70642166]

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

[Creamer Training]

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. 

[davids70642166]

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)

[davids70642166]

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.

[davids70642166]

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

[BobLevine]

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.