Spreadsheets

Nefarious

Nefarious

Nefarious

Associate
Joined
1 Aug 2003
Posts
1,053
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?
 
You can set spreadsheets as shared in Excel and multiple people can edit them. The various versions of the sheet are then merged when the document is saved.
 
If you share the work book then more than one person can change things in it at the same time :)
 
Beat me :p

Perhaps use outlook forms? Try a google for it but we use them for milage claims here.
 
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.
 
regarding locking certain parts of a sheet (rows, cells etc) or the sheet itself or even the workbook is possible. Look at tools, protection from the menu, and have a look through the help for examples.
 
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?
 
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.
 
Good luck with this mate :p

We have a similar thing here at work (accountants) but it was an off the shelf package that costs us around £3,000 a year. It seems like a lot of money but for what it does it is priceless and you could save the £3k by not messing around with Excel and Access and any other problems that come along the way.

Its difficult to get that through to the boss men but I would definately recommend it.

:)
 
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.

Impress them and save lots of money! Mr. Excel is a good place to start with any technical questions.
 
We have an in-house timesheet + expenses system at work that was written by one of the guys whos excel knowledge is good but not amazing (with a little help from yours truly). The limitations lie with excel not his code (or maybe not ;)).

The basic setup is that each employee has a excel timesheet for his/her time and expenses that month (plus equipment hire charges etc.). The main focus is the diary page, its a worksheet formatted to look like the outlook diary, the user fills in a job number, a description of the work and the time they spent on that job each day. The sheet uses a central job number/name workbook to look up the name. All values from the diary are split into individual timesheets for each job on the fly. At the end of each month the whole timesheet is submitted by email. A simple macro runs thru the sheets removing the formulas, converting values to number data and deleting unused sheets. Once the sheets arrive with accounts they use a number of custom macros to strip out the data and create a master timesheet for the whole office for that month. Its then possible to filter by job, employee, partner in charge etc.

The system works but there are still bugs, there are people who try and break it (its all protected so you can only modify certain cells) but generally despite the macros there's still too much manual work at the end of the month creating the master sheet and at the beginning of the month changing the diary.

I'd suggest you look at using PHP/MySQL to produce a centralised database that can hold all the data, offer different levels of user access and its generally more flexible than the excel system.
 
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.
Click to expand...


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.
 
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?
 
If you dont want to fork out for a ready made program, Access is your best bet.

I used it for this kind of thing a while ago, and it is very versatile. It will do pretty much anything you could want, and you can also create data access pages so you can create a HTML interface for it as you suggested.

Access comes with Office Pro, but iirc you can either export a program or install a runtime only version of access on any machine without needing a license (can anyone verify that?), which would mean you would only actaully need to buy one copy of access for you to create the program with.

All you'll need is a copy of access, a smattering of VBA and SQL knowledge (i bought the "for dummies" books on vba and sql when i started creating our program), and a fair bit of time to figure it out ;)
 
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!
 
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.
 
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!
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom