linking spreadsheets

[captlogic]

I've got a problem linking two calc files and I can't seem to find any info about this particular scenario and I was hoping to get help here.

I've got two spreadsheets linked together.
File A: timecards_2007.ods
File B: payroll_2007.ods

If File B contains a formula in cell a2 that reads
='file:///c:/timecards_2007.ods'#$2007.$a$1+'file:///c:/timecards_2007.ods'#$2007.$a$2+'file:///c:/timecards_2007.ods'#$2007.$a$3

the problem I'm having is that the formula will only add the first two cell references, not the third. I entered the formula by hand, and the 'edit->links' option is greyed out. This works just fine for my 2006 files, but not in the newer files.

All my research on the problem says you need to paste into a spreadsheet to create a link, but I can't, and I didn't on previous files.

Am I missing something terribly obvious?
Thanks in Advance
Patrick

[Villeroy]

=SUM('file:///...'#$2007.$A$2:$A$4)

[captlogic]

Thanks for the reply, however, my example was incorrect. I need to add up non-consecutive cells

i.e. cell a2 + a10 + a18 etc etc.

I also have another formula that fits the following example
If the value of c:/payroll_2007.ods'#$2007.$a10 is more than 40 then payroll is (a10-40)*15 + (40*10) (hours over 40 * overtime rate) +(40 hours * regular rate); if a10 is less than 40 then a10*regular rate.

=if('file:///c:/payroll_2007.ods'#$2007.$a10>40;(40*10)+('file:///c:/payroll_2007.ods'#$2007.$a10-40)*15;('file:///c:/payroll_2007.ods'#$2007.$a10*10))

when I type this formula into the worksheet it does not calculate correctly and the 'edit - links' command is still greyed out.

The most frustrating part is that I have set this up for previous years files and they work just fine, but I don't remember doing anything special to get the 'edit-links' menu command to work.

anybody?

[Villeroy]

How you can simplify the creation of this type of links called sheet links:
Open both files, start a forumula with "=" and point to a cell in the target sheet of the other file.
What goes on actually when using ='file:///path/name.ods'#$SheetX.A1?
You get a data copy of the other file's sheet. The copy is a hidden sheet named 'file:///path/name.ods'#$SheetX. It contains all the data and formattings, but no formulas. See Format>Sheets>Show...
Reference ='file:///path/name.ods'#$SheetX.A1 refers to cell A1 of that hidden sheet in the same document.
Updating the link tries to re-import that hidden sheet. Sheet links have the advantage that you can pass the file over to another system without caring about the other file.
Known problems I'm aware of:
1. You can change the link target file, but the GUI does not let you specify a sheet name. You can switch from file1#sheetX to file2#sheetX but not to sheetY.
2. The path-name of the referred file must not contain single quotes.
3. The file gets bloated. Don't change the targets too often. The unused hidden sheets remain.

Other way of linking to another spreadsheet:
=DDE("soffice";"C:\Path\Name.xls";"SomeRange";1) see help on function DDE.
How you can simplify the creation of this type of links called DDE links:
Open both files, copy from the other file and paste special with option "Link".
The link can update automatically since the source file gets opened invisibly. This implies that the link stops working if the source file is not present.

[captlogic]

That gives me lots to work with thank you very much.

[Villeroy]

That gives me lots to work with thank you very much.

Fine, I tried to give you all the background information to let you hunt down the problem by yourself. I forgot one important problem of sheet links:
The creation of the hidden sheet is triggered by the calculation cascade. When you remove a hidden sheet directly or by "Unlink" button in the dialog and there is still one single formula or name referring to the sheet in question, then the sheet link will be recreated as soon as the calculation cascade stumbles upon a reference to the file.