Excel - store/retrieve data in file

[RincewindWIZZ]

Hi,

I have a (large) excel spreadsheet that allows people to maintain an individual assessment of a project over time. Its large because there are upwards of 200 criteria that can be assessed on number of occasions during the life of the project.

A number of these projects may be ongoing at any time so I would like to extend the spreadsheet so that it could select a project from a list, load the data associated with the project, allow maintenance of that data (which is the bit the spreadsheet does at the moment) and save it back to the file/database. That way I have one large spreadsheet and (hopefully) 1 relatively small data file.

The data itself will be about 10 fields describing the project (which can happily go on a form) and then the review data. Any of the 200 criteria may be reviewed and there may be a number or reviews during the life of the project. The criteria are maintained as values in the cells in the body of the spreadsheet effectively as a large array with each criteria being a specific row and each review adding a column to that array.

I could really use some help on how to get started on this (maybe some example code I could follow/modify or a tutorial on the www or something). I have a whole bunch of preliminary questions like
- should I use a database or a flat file
- can I store a range of data (the array) easily or do I have to do each element independently
- how do I connect excel to a file/database
- do I have to do anything differently to run this on a network?
etc

Thanks for any suggestions including (if you feel so inclined) 'too hard'.

[natefarm]

I'm a visual person, so am not really picturing what you are needing, but it sounds like you might want to be using Access instead of Excel.

[RincewindWIZZ]

Thanks for the thought

Sadly I cannot visualise how I would give a user access to a grid of 200+ rows and 60 columns; allow values at any intersection; do a lot of relatively simple sums on the results and then displays those results in graphical form in Access. I do think the GUI is much easier in Excel - plus I have a prototype in excel already!!

[natefarm]

You're right about that. So are you saying that you have a large workbook with data for all projects, and want to also have a small separate workbook for each individual project that pulls its data from the large one? If that's the case, then formulas in the small workbooks pointing to the data in the large one should do it, but I can't take you much further than that unless you want to attach some sample data.

[RincewindWIZZ]

Think about each project as 3 year construction
Each construction has 10 main blocks of work each of which can have a number (typically 20 - 30) components
Think about row as a component of that construction
Think about each column as a period in time and then the spreadsheet is a giant matrix with columns beoing a period of time; rowns being a component and the values in the intersections being an assessment of the progress of that component
At any period in time there may be 10 or 20 different components being worked on.
Periodically ( every 2 - 3 months) a review is undertaken of the components of a project and the assessment is entered as a value in the spreadsheet at the intersection of the period and the component

The spreadsheet takes this information; displays it graphically together with a 'standard' and helps identify areas requiring attention.

All this works fine. But rather than have a single spreadsheet per project, I would like to have one spreadsheet in which I can create/maintain the data relating to a particular project and then save it to a file.
I can then acces this historic data for comparative analyses of individual projects.

And the question is how do I create/maintain this data file in Excel.
And no I do not want to use any of the standard project management packages because they are too complex for my purposes.

As always, thanks for you interest and any suggestions/references that you can provide.

[natefarm]

But rather than have a single spreadsheet per project, I would like to have one spreadsheet in which I can create/maintain the data relating to a particular project

Sorry. To me these both sound like the same thing, so I'm still confused. Is it that you want an on-going 'live' file with current data, and then save periodic snapshots of it? If someone else is getting a better picture, feel free to step in.

[RincewindWIZZ]

Sorry for being so unclear.
I am looking for
- one spreadsheet which does all the sums, pictures, graphs etc for maybe 50+ projects
- 1 data file for all projects
- an operational process that involves
...open spreadheet
...select project and load data from this data file
...update data in spreadsheet (evaluation for a new time period)
...save updated project data to back this data file (original project data + data for new time period)

I guess its going to be 10k - 20k of data per project.
So one 8Mb spreadsheet and 50+ records of 10 - 20k in a single data file (as opposed to 50+ 8MB spreadsheets)

And eventually I want to be able to take the data from all the projects from this single file and do some cross-project analysis on them - looking at how different projects have progressed over time.

Yes I could have 50+ 8MB spreadsheets and, if I cant figure out how to do maintain the data on an external file or its too hard/messy, that is what I will end up with. But it makes the subsequent comparative analysis messy because I have to copy all the data out of each spreadsheet into a master spreadsheet
Not hard but just messy.