Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help making the next step, please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help making the next step, please
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 "Don" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help making the next step, please
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/1...l-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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help making the next step, please
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) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=40542 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help making the next step, please
On Dec 13, 1:08*pm, Simon Lloyd
wrote: 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) ------------------------------------------------------------------------ Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=40542 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help making the next step, please
Don feel free to join our forum (shown below) where you can attach a dummy workbook that we can help you with, as long as you post in this thread http://tinyurl.com/6g3s6k all the help and suggestions you get will still appear here in the newsgroup where they too can see the progress and help you forge on! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=40542 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help making the next step, please
On Dec 14, 3:06*am, Simon Lloyd
wrote: 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 Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=40542 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Drop Down List with Step by Step Instructions for 2007 | Excel Worksheet Functions | |||
Need step by step to add invoice numbering to excel template | New Users to Excel | |||
Step by step tell me cube root (2197) which I know is 13 | Excel Discussion (Misc queries) | |||
What is the step-by-step procedure for making a data list? | Excel Discussion (Misc queries) | |||
I need step by step instructions to create a macro for 10 imbedde. | Excel Worksheet Functions |