[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [sheflug] Slightly Off-Topic, Openoffice Help

Ashe wrote:
> Can anyone point me towards a decent reference or bit of info that can 
> tell me how I'd do the following?
> 
> I have a bunch of calc spreadsheets, each of which has a sheet which is 
> an attendance roster. I need some method of iterating through each file, 
>   extracting the data from that one sheet in each file, and writing out 
> another spreadsheet with, for example: Name, Member Number, Count of 
> Attendances.
> 
> Am I better copying all those sheets into another file and working on it 
> from there? Is this something I can do with the built-in functions, or 
> am I going to have to start faffing round with basic and stuff?
> 
> AH


Try this.  Make a new spreadsheet and make up a column of numbers.  Save 
this spreadsheet as sheet1.  Leave sheet1 open.
Now open a new spreadsheet, call it sheet2 and, in the cell of your 
choice, type in '=SUM(' (without the quotes) then go select all the 
cells you entered numbers in sheet1.  A red box should appear around 
them.  Go back to sheet2 and press ) followed by the Enter or return key.

You should get the sum of the numbers in sheet1 appearing in the cell in 
sheet2 and if you position your cursor in that cell you'll see the 
format of cross spreadsheet cell referencing as something like this
'file:///**your path here**/sheet1.ods'#$Sheet1.D15:D19 .  The D15:D19 
is the range of cells I selected.

Cross worksheet cell references within the same spreadsheet are usually 
in this format  'Sheet2.A1:A3'.

I can't say you have to use BASIC or any form of macro programming to 
get to count up the attendances or attendees or whatever data processing 
it is you need, but cross cell referencing is entirely possible.  Other 
than that it's a matter of working out your algorithm and I suggest you 
take a look at the Function Wizard available in Calc.

I am using OO v2.0 btw.

HTH
L.

___________________________________________________________________

Sheffield Linux User's Group -
http://www.sheflug.co.uk/mailfaq.html

  GNU the choice of a complete generation.