How to connect Works spreadsheet files

I want to combine information in several Works spreadsheet files into one summary file at the end of the year. In Excel this information would automatically update in the end of year file. Can Works spreadsheet do this?

 

I have worked for many years with Excel, but virtually zero with Works.

My hunch is, probably not.

BTW,
By default, nothing is "automatic" in Excel.
Think of Excel as a giant, unformatted, calculator.
A fresh, blank workbook does nothing automatically.
Not at least until it has been written/programmed/coded by a designer/developer does it start to come to life.
The reason i mention this is that you mentioned an "end of year file ".
Do you already have, or does somebody else already have an Excel monthly/yearly setup which you want to duplicate in Works on your system?
If so, I highly doubt you will be able to duplicate an even remotely complex Excel workbook in a Works spreadsheet.
It stands to reason there is substantial functionality difference between works and Excel.

 

Following on from Bilb0's comments...

If you don't want to splash out for Excel or MS Office, you could give Open Office a try...

With Calc you can combine data from several worksheets but it is far from automatic, at least it is free.

 

Thank you both

Thanks so much for your responses. Several years ago I did create individual payroll files and using Excel's Help section figured out the formulas that automatically (without me entering the info again) updated the final worksheet. The piece I don't remember is how I identified the individual files and connected them to the final worksheet. Once I do that, I can remember the formulas to transfer the data.

You're right ... Works probably won't do it. I do have Open Office but am still stuck on the piece that connects the final worksheet to the individual worksheets.

Again thanks for your help.

flo

 

Sorry for the delay flo, I missed your post.

Using Open Office 3 (and probably the same for earlier versions) to reference a cell in another worksheet use sheetname.cell (note that period) so for example if you wished to total figures in Jan Feb and Mar sheets for quarterly you use something like this.

=sum(Jan.G10;Feb.G10;Mar.G10)

Hope this helps.

 

Works spreadsheets

Hi Wildfire,
That suggestion looks like it should work. I'll try it today and let you know how it turns out. thanks a million

Flo

 

No problem, just remember

wildfireaccount=totalgross*.1

 

When adding individual cells, it is not necessary to use the "sum()" function.

=sum(Jan.G10;Feb.G10;Mar.G10) should be =Jan.G10+Feb.G10+Mar.G10

Unless Calc provides some sort of converter, that naming convention format is COMPLETELY incompatible with Excel which uses Exclamation marks (!) as delimiters.

It is only necessary to use the SUM() function when working with range(s) of cells.

Assuming sheet Jan had values in G2:G32 (1 row for column headers and 31 days in January), to sum the range in a different sheet would be =SUM(G2:G32)
Assuming you have sheets named Jan, Feb, Mar, and data in:
Jan.G2:G32
Feb.G2:G30
Mar.G2:G31

To perform the sum of these ranges, the formula would be
=SUM(Jan.G2:G32;Feb.G2:G30;Mar.G2:G31)
(note: the range delimiter for Excel would be the comma ", ")

However, I get the impression from your posts that you need a seperate file to total OTHER files.
I am unsure if Calc will work with "external" data.

Of course Excel does, and if Calc presumes to be even remotely on the same level with Excel, surely they built that in, but I have never used it for that.

These are some sample syntax formulas for Excel.
=[file_name1.xlsm]sheet_name1!$G$2+[file_name2.xlsm]sheet_name2!$G$2
=SUM([file_name1.xlsm]sheet_name1!$G$2:$G$32,[file_name2.xlsm]sheet_name2!$G$2:$G$30)
note that the I added the "$ ".
These are used to "fix" the reference for copy/paste purposes.
They can be eliminated, but then the "paste" will adjust the reference relative to the pasted position in the workbook.


(RANT ALERT)
If anybody can't tell, I have serious issues with these "knock-off" applications in Open Office.
Frankly, I consider it part and parcel with the whole "Wal-mart" mentality that has
resulted in the nearly complete destruction of American manufacturing capacity.
It takes absolutely ZERO creativity to sit down and simply copy the creativity of others.
And then to give that copy away for free is an insult to the creativity of the people who worked hard to create it, and it risks damage to the actual creative source.
Then to actually USE that free product without patronizing the actual creative company, risks damage to the creative company.
Just like at Wal-Mart, or K-Mart, or Target.
It takes zero creativity to create a knock-off of an American product and since the money to CREATE was saved by the copier, that product can sit on the shelf for a fraction of the original.
Then on the premise of saving a few pennies, "we" buy the copy without spending one whit of thought about the end result.
Huge profits for foreign companies, and the death of American companies who were the original creative force.
Then big surprise, actual ingenuity and improvements stop because the companies who create the copies do not employ creativity, in fact the success of that business model depends on not spending money on creativity.
Then, because they keep getting hammered on price, they start cutting corners, like using cheap lead based paints, or cheap poisonous sweeteners.

Perhaps, I have a different perspective because I actually am a developer of code and spend the time and money to create new things, but in the end, we are all consumers, and frankly have a responsibility to consider the consequences of our buying decisions BEYOND the few pennies or few dollars, we may save in the here and now.

Think I'm blowing it all out of proportion, and making a mountain out of a molehill?
Think this is somehow different because Microsoft is so huge?
How many times do we have to relearn the same old lessons?
Just look at the state of our economy today?
Ever wonder to yourself why the government is grasping at straws to come up with manufacturing jobs for all our unemployed?
Because almost all our private manufacturing capacity has been leveled and turned into shopping malls and parking lots, and condominiums.

So, yes, I take great offense at those who hop in here and so willy nilly suggest others take advantage of the original creativity of Microsoft without recognizing that without Microsoft's original creativity and support, almost none of this would exist today?
Even these forums are called "Windowsbbs ".
Call it loyalty, call it protectionism, call it foolish if you want, but just remember your actions have consequences beyond the next few minutes.
(END OF RANT)

 

BilbO I appreciate your opinion but considering your argument is for business enterprise can I put another take on it.

For the small business sometimes it is not practical to spend hundreds or even thousands of pounds/dollars on software that at the end of the day may mean a very small portion of that small business' productivity.

If Microsoft and others want to be market leaders the answers simple, like in any other business stay ahead of the market. Offer something the others don't.

I believe there is a market for freeware/open source software for precisely the reasons you give above. Linux platforms and Open Source office applications etc are only on a small percentage of home desktops (mainly due to MS tactics BTW - My Opinion).

Here in the UK it is very difficult to buy a system without MS products on it, and if you request it be removed and a refund of the license price chances are you'd be laughed out of the store.

As for my example, it was only an example. Yes there's no need to use SUM().