Spreadsheets

Nefarious · 5 Apr 2007 at 13:21

I've been asked to look into reorganising the way my company file and organise internal data (like timesheets and expenses). The two options that I'm considering are either to buy an off the shelf database (which can be shockingly expensesive and not always tailored to the company's needs) or some cunningly structured spreadsheet.

The problem with Excel is that when one person is editing a file no one else can.

Can anyone think of another (relatively simple) option? Does the OpenOffice spreadsheet offer any solutions? Can anyone think of a spreadsheet that does offer the functionality I'm after?

Nefarious · 5 Apr 2007 at 14:36

Excellent - thanks for everyone's speedy input!

Hmm, setting Excel to multi-user - news to me I shall investigate. Does anyone know about security functions? There are parts of the spreadsheet that I don't want people to see (e.g. salary calcs et al) is it possible to lockout/hide some workbooks or else have data cross-linked between files and then password protect the sensitive files (quite how the system would access it with password protection locking the document I don't know).

With Access I'd have to learn how to drive it, are the functions fairly similar to Excel?? (do you know what flavours of MS Office it comes with?)

Outlook forms????? I'm googling it now and will let you know as I have no idea what you mean.

Nefarious · 5 Apr 2007 at 14:54

Just checked out Outlook forms. It's going to take me sometime to work out exactly what's going on with them but it'll be larf I'm sure (reconfiguring VSFTPD is going to have to go on a back burner). As far as I can see the way to get it to process data input is through macros, yes?

Nefarious · 5 Apr 2007 at 18:58

Right...

We've had a meeting and decided what we actually need the process to produce. It seems that what they really want is project management software that will catalogue expenses (including personnel time sheets) from project start to finish and producing various analyses and displays along the way.

It needs to take people's expenses and put them to a project or as a general company purchase and include a project related time sheet. This is then collated per project and provides per project analysis but is intergrated with all the other active project data and produces intergrated analyses....

This is already making my head hurt but:

- Excel cannot cope with this level of complexity without interlinking far too many files to be sensible.

- Outlook forms can't provide the multi-user level of functionality required

So far this leaves Access, the other option is a web based system but there are two problems with that,

1. It would be difficult to create a customisable system with the same level of functionality without creating a funky Flash system that would take me about a year to write

2. This is the wrong forum for an HTML system

As regards Access I'm going to have to have a look at it and see what it can do and may end up having to look at off the shelf versions.

Nefarious · 6 Apr 2007 at 13:09

Robbie G said:
I can't believe excel couldn't handle this. Some of the spreadsheets we use at work are extremely complex and they work well with sufficient hardware. Excel is a very powerful and versatile tool and from reading what you need I'd imagine this kind of analysis could be done relatively easily.

The desired system should be such that each member of staff has their own spreadsheet with a simple format into which they can put expenses and hours. Any non-project related spending should go onto an asset register as a company purchase and any project spending should be flagged as such and feed into the project's spreadsheet.

The HR manager has a resource allocation sheet where he sets out who is working on which project and where (he is the person who generates project numbers). The system should be able to notice if people have put in a different number of days to the HR manager and flag both him and the project manager. I was also thinking of a percentage bar that shows how upto date each projects data sheets are should be available.

A profit analysis sheet should show a 'real-time' display of what has been spent on a job and how much funds there are left for further work.

It should be possible from the setup to extract from the data facts and figures such as... how many site days a particular person has worked or how much has been spent on fuel, etc, etc.

Nefarious · 6 Apr 2007 at 13:13

To get Excel to do what I described, without having so many sheets in a workbook as to be unusable, it would require multiple files.

If I had a macro that would generate a worksheet in a person's excel file for each month and get other files to inter-relate data between files it might just be possible but strikes me as rather messy.

I'm installing a trial version of MSAccess and will see what I get out of it.

Mark M, what was the software that your company bought and does it sound like it would be able to do what I have described?

Nefarious · 7 Apr 2007 at 15:26

With Access can I set it so that people without Access can input via HTML without it being installed?

We only have so many licences and with spending a fortune on new licences it would be cheaper to buy Linus Torvalds for a week to do it for us!

Nefarious · 7 Apr 2007 at 16:05

Looks like we have enough MS Office2000 licences to cover it (or atleast MS were less security concious back then...

)

I shall go down the Access route though I may have bitten off more than I can chew. I'm a reasonable programmer but have zero experience with Access - does anyone know any good sources I can look at? I'm checking on books that I can order to muddle through with but would like to hit the ground running on this and am desparately short of time.

Nefarious · 7 Apr 2007 at 16:22

Just checked on the books available. The obvious one to go for would be the 'Dummies' book but I'm not sure I could stand the shame of it being on my shelves.

I've never had any of the 'Dummy' books as I either went for Linux texts or M$ own brand to get MCPs can anyone recommend which MS Access 2K books are best?

* Just looked at reviews of the Dummy series - apparently next to useless for those wishing to write databases but invaluable to budding cartoonists

** Arrghghgh!!!! - I've read a few reviews for SAMS, Dummies and Osbourne: they are all utterly slated by most of the people who've read them!