[Solved] Combine ODS workbooks and resolve external links

Discuss the spreadsheet application

[Solved] Combine ODS workbooks and resolve external links

Postby KirkWard » Fri Nov 29, 2019 6:46 pm

I have two calc ods workbooks. I want to import all the sheets of one into another so that when I distribute the main worksheets in the file, I will not have to worry about updating or not updating links. However, when I import the second file into the first, the imported worksheets wind up referencing the formerly external workbook. These are very large files, and the forum limits the size, so I cannot upload them to show what is happening.

Can someone tell me how to import worksheets with data and formulas intact, avoiding any references to an external file?

Thanks,
Kirk Ward
Last edited by MrProgrammer on Tue Dec 03, 2019 12:57 am, edited 2 times in total.
Reason: tagged solved; Edited title, was: Importing Workbook
Ellijay, Georgia USA
Marco Island, Florida USA
US Eastern Time Zone (GMT -4, or 5)
Aoo 4.1.6
Lo 6.2.7.1 (x64)
Windows 10 Home
KirkWard
 
Posts: 57
Joined: Fri Mar 26, 2010 3:44 am

Re: Importing Workbook

Postby Zizi64 » Fri Nov 29, 2019 7:59 pm

These are very large files, and the forum limits the size, so I cannot upload them to show what is happening.

But you can share them on a free file sharing service like the Google drive...
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.4; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8646
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Importing Workbook

Postby KirkWard » Fri Nov 29, 2019 9:04 pm

Thank you, but I found a solution in LibreOffice forum. I used "Find and Replace" to remove the file name and path, leaving just the cell and page references. It worked very easily and removed any error messages.
Ellijay, Georgia USA
Marco Island, Florida USA
US Eastern Time Zone (GMT -4, or 5)
Aoo 4.1.6
Lo 6.2.7.1 (x64)
Windows 10 Home
KirkWard
 
Posts: 57
Joined: Fri Mar 26, 2010 3:44 am

Re: Importing Workbook

Postby KirkWard » Fri Nov 29, 2019 9:06 pm

Also, some of us older folks don't know all the latest things, and are too old to handle so many new things every day. I don't even understand Google Drive. Seems the older I get, the simpler things need to be.
Ellijay, Georgia USA
Marco Island, Florida USA
US Eastern Time Zone (GMT -4, or 5)
Aoo 4.1.6
Lo 6.2.7.1 (x64)
Windows 10 Home
KirkWard
 
Posts: 57
Joined: Fri Mar 26, 2010 3:44 am

Re: [Solved] Importing Workbook

Postby Villeroy » Sat Nov 30, 2019 3:18 pm

Even the youngest and most intelligent among us can not solve any problem without knowing the details, preferably through actual documents showing some concrete data, formulas, and structures.

1) What exactly do you have?
2) What exactly are you doing with it?
3) What is the result of your action contrary to your expectation?

A description without attached document may look like this:
1) I run OpenOffice 4.1.4 on Windows 10 and one of my spreadsheet has the following formula (simply copied right from the formula bar): =SUM('file:///C:/John/Documents/Data.ods'#$Sheet1.$A$1:$A$999).
2) Now I would prefer to merge the referenced document into the document having the formula.
I right-click the sheet tab and copy the sheet over to the other document. The above SUM formula still refers to the same external file. I get a warning stating that "the new sheet contains absolute references to other sheets which may be incorrect".
3a) The copied SUM formula returns error #NAME! and the formula cahnged to =SUM(''#$Sheet1.D3:H12) [simply copied right from the formula bar]
3b) When I move the sheet instead of copying the result is the same.

What I want is =SUM(#$Sheet1.D3:H12) and by describing the problem precisely I found the solution. I can fix the wrongly transfered formulas by simple find&replace.

If you feel unable to give a detailed description due to age or a lack of technical terms, the one and only way is passing over some document(s) together with a less detailed description. Otherwise there is no way to understand your problem without endless and fruitless discussions.
In order to be able writing this text, I created a spreadsheet with a SUM formula referencing a range in another document, copied and moved that sheet so I can reproduce some issues that may arise. I am too old to remember everything that can go wrong.

P.S: The term "workbook" in your subject line refers to a collection of worksheets. What you try to import is a worksheet into a workbook. In OpenOffice documentation workbooks are "spreadsheet documents" and the sheets are "spreadsheets". Using the Excel terms would be OK if you would use them correctly.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27561
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot], MSN [Bot] and 13 guests