MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Kimmy on February 11, 2002 5:51 PM

I work with the American Foundation for Suicide Prevention Central TX chapter. I need help in making a spread sheet that will total & keep track of all the training and presentations that our members do. I would need to break it down but what type they are doing
I am no pro in excel but willing to learn.
is there a excel queen or King in the house.
Thanks for the help

Posted by anno on February 12, 2002 1:27 AM


asking the best way to develop a spreadsheet is a bit like asking "how long is a piece of string?", because you can design and build it in as many ways as anyone can imagine, but in my opinion regardless of design and user needs the three crucial concepts are planning, planning, and planning. functions and macros are stuff you can pick up and glue in later, and with pages like this one that part is easier than you may think.

really, reeeelly think about what you need to use the spreadsheet for, and ask others who'll be using it what they need. you can then work backwards to translate user needs into functions of your spreadsheet. if everyone agrees that it just needs to be a rough guide that people can eyeball to see who did what for whom and when, then you probably don't have to do too much thinking beyond what your column headings will be.

however, if there's any chance that you'll need to use it for anything more complex (reports, summaries, calendar/date/person/training course tracking or combinations) you need to establish what people's needs are beforehand, otherwise you'll probably end up with something that's cobbled together without a coherent purpose and which doesn't serve anyone's needs properly.

people write books on this sort of stuff and i could start one here, but for now a couple of tips, which might be complete overkill if you indeed do only want something as simple as a list, but i hope they help:

- keeping in mind the primary purpose(s) of the spreadsheet, identify your information needs, making a distinction between information you just want, what you actually need and what you really have. if there is a big disparity between what you want and what you have, think about why. it may be because you can't actually get it, that no-one's ever thought of it, or it's actually not that useful to the primary purpose of the spreadsheet. think about what you already have too - when you figure out what everyone needs, do you need everything you have? in short, keep it simple.

- this site (not just the message board - the whole thing) has heaps of useful tips and design ideas but you'll be able to pick and choose what you need once you have a better idea what you are trying to do.

- don't reinvent the wheel. see if you can find something that someone else has built which is similar to what you need and adapt it. again, you won't know what to look for though until you establish what you need. a word of caution though - adapting someone else's model (especially if your needs are many or complex) can be a trap because by the time you've adapted the thing it may have been easier to build your own. but excel users are generous folk and judging from the traffic on this page would be happy to contribute their designs and assistance.

-don't try to do it all at once. if you've identified that you need lots of different types of functions, get one working properly before you move on to the next. try to prioritise your immediate needs and ones that can wait for a while. if something doesn't work it's easier to find the bugs if there aren't a bunch of other things hanging off it which could be causing the problem.

-document what you do as you go, again useful for tracking down errors. documentation also means there's a better chance that knowledge about how your spreadsheet works can be shared, which is especially important if the person who uses it the most leaves.

I hope this is helpful although it doesn't answer your immediate question, and i'm happy to assist further, especially if you can come back with some specifics on what information it is you need to store and how it needs to be retrieved and presented.