I need help making the next step, please

[Don]

I hope I'm in the right place. Please direct me else where if the is
a more appropriate forum.

Some background: I work for a hospital and keep departmental work
stats--ie # of patients treated, a database of doctors, patients,
etc. I do this all in Excel. I am self taught and I am pretty good
with it. To be sure, I went and took the MOS test in Excel and passed
the expert level test. Not tooting my own horn, just trying to give
you a feel for my ability.

Everything feels disjointed. I have several different workbooks
going. My goal: consolidate where possible, offer a clean, easy to
use interface so management can "ask questions" of the databases
easily (they are far from computer savy

The hospital does not have MS Access on it's machines, so I want to
maximize Excel's capabilities. How do I do that? SQL? OLAP cubes?
Web Page Access? Where do I turn and what hardware/software obstacles
do I face?

I'm more than willing to invest the time/money for research and
learning materials, but I need some real person input about what to
do.

Any suggestions you may have would be very appreciated.

Thank you,
Don

 

[Otto Moehrbach]

Don
Maybe I can help you with this. However, your question is wide open
and, of course, you know that. I think the starting point has to come from
you.
You say:
"My goal: consolidate where possible, offer a clean, easy to use interface
so management can "ask questions" of the databases..."
I think the key here is for you (you're the only one who can do this) to
come up with this "easy to use interface". Never mind what your overall
database (several workbooks, etc) looks like now. That can always be
modified to marry up with the user interface. The user interface can take
many forms. For instance, you could have a number of buttons with
appropriate text for the user to click on, or a UserForm could pop up with a
number of options from which to select, or the screen display could include
your own menu item in the menu across the top with a drop-down menu for the
user to select from.
But the key is going to be:
What does your database contain?
What will the user be asking for?
As to consolidation, Yes. I would recommend that you consolidate all the
workbooks into one if at all possible. If needed (your call), the workbook
could have a splash (opening) sheet with some buttons differentiating
between different facets of data. Click on one button and the workbook can
change (sheets hide and unhide as needed) to present one facet of your data.
Just some ideas. HTH Otto

 

[Socko]

Absolutely yes, you can use excel as a database and i believe if you
are ready to explore the possibilities and to learn, you can use excel
to store huge amount of database.

I sometime use excel to store data and whennever i need data, I
connect the workbook to the excel database. You may find more at the
following url. And I am sure you will be learning a lot if you go
through sites by MVP (Microsoft Most Valuable Professionals").

http://socko.wordpress.com/2008/07/14/connection-query-for-data-in-excel-spreadsheet/


Some sites for excel resources:

OzGrid’s (Dave Hawley) Excel Forum: http://www.ozgrid.com/forum/

MrExcel’s (Bill Jelen) Excel Forum: http://www.mrexcel.com/

Andrew’s Excel Tips: http://andrewsexceltips.com/

Chip Pearson: http://cpearson.com/excel.htm

Ron de Bruin: http://www.rondebruin.nl/

Jon Peltier: http://peltiertech.com/

John Walkenbach: http://www.j-walk.com/ss/

Andy Pope: http://www.andypope.info/

Tushar Mehta: http://www.tushar-mehta.com/

Dave McRitchie: http://www.mvps.org/dmcritchie/excel/excel.htm

Andy Pope: http://www.andypope.info/index.htm

Jan Karel Pieterse: http://www.jkp-ads.com/

Ken Puls: http://www.excelguru.ca

I hope this helps.

Selva V Pasupathy
For some more examples, visit my site:
http://socko.wordpress.com

 

[Don]

It sounds like you just need to create a simple matrix with a userform
as the query tool ---- In what way are the current workbooks
disjointed?, how do you currently query the data? what terms/conditions
are needed to display the data? is there common data between them?

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

Thanks to all who responded. I appreciate you taking the time.
Everyone made some great points so I'll touch on them and hopefully my
responses will help you narrow things down for me.

Otto--I get your point about consolidating into one workbook. Ideally
that would be great. But my workstation is hardly ideal. 256MB ram
with a Celeron processor, and a T1 connection so slow it can take,
literally, 45-50 seconds just to open ONE of my workbooks (I timed
it!!). And that workbook has only 3500+ rows by 10 columns of data.
Yes, I do have a fair number of Sumproduct formulas working in it but
adding more data would slow it down even more.

Socko--a great list of sites--thank you!!!!! I routinely use 75% of
them. The others I don't know about, but I will surely investigate.

Simon--you intrigue me with your statement "simple matrix with a
userform as a query tool". I've used userforms only rarely in the
past, but I'm fuzzy on what you mean by "matrix".

What I need is a way, an easy idiot proof point and click way that my
bosses can gather info from the data I have stored. To illustrate,
when I tried to show them how to get their answers easily by using
Auto Filter you would have thought I asked for a dissertation on
E=MCsquared.

I asked about using a userform on one of the forums Socko mentioned.
The responses I received led me to believe this would be particularly
difficult to use.

I use pivot tables once in a while, but asking/teaching them to use
one is even beyond a simple autofilter.

Here is an example database I have. It is for tracking our doctor
referrals. I enter the drs name, and how many referrals for each
site. There are also 2 different referrals they can make to the site-
PT or OT. It looks something like this:

DrName Month Year Site1 PT Site1 OT Site2 PT Site2 OT etc
(there are 5 sites)

I need an easy way for a non geek to ask "how many PT referrals did
Dr. X make to site 3 in January 2007?" or "how many referrals did Dr C
make to Site 5 in the first 6 months of 2008?'.

I'm sorry if I've droned way too long. I'm trying to be as clear as
possible.

Thanks again for your help.

Don

 

[Don]

Don feel free to join our forum (shown below) where you can attach a
dummy workbook that we canhelpyou with, as long as you post in this
threadhttp://tinyurl.com/6g3s6kall thehelpand suggestions you get
will still appear here in the newsgroup where they too can see the
progress andhelpyou forge on!

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

Simon,

I apologise for the delay. My daughter and granddaughters flew in
from Ca. I've been busy with them.

I have also spent some time reviewing all of the references you and
the others have left. I will be posting back when I can nail down
what I need to ask more clearly.

Thanks to you and all the others who responded. I greatly appreciate
you taking the time to help.

Thanks again,
Don