Calc: external references of multiple files; extend selection [closed]

asked 2014-09-02 02:17:41 +0200

alexholver gravatar image

updated 2014-09-02 02:46:20 +0200

Hi all,

I write my invoices in Calc (daggy I know) all saved as individual files, and I'm doing a summary Calc of all my invoices.

So in my summary file, I've got external references to each individual invoice, and referencing to my Date, Client, Job Title and $Total cells. These cell locations don't change in each invoice.

Each invoice file is "Invoice 2014-xxx.ods" where xxx is incremented with each invoice.

Now that I've got my first couple of lines populated with the cells from the first few invoices, how can I do extend selection so the file number portion of the reference increments, instead of the row number (standard behaviour).

Where do I put '$' into the reference so I get the behaviour I need, instead of having to manually edit each line?

[a little more...] So far, I've tried Edit>Fill>Series which only works on the resultant output, not the reference formula The simplest is to have $ everywhere in the reference, ie eg, ='file:///D:/invoices/Invoice 2014-001.xls'#$Invoice.$E$13 CTRL+copy down all my columns, then search and replace the "-001." with incrementing "-xxx." for each row. Slow, but reliable.

An easier, lazier method of doing this would be much appreciated!

Thanks in advance, Alex.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2016-03-03 11:33:50.997864