Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help / Recommendation
This past summer you all were wonderfully helpful with a calculation problem
I was having. Now I am seeking help again. I need to know if I am using the best vehicle to track these stats for a client. The client needs to track stats on five sales reps (potential of more reps in the future). Data needs to be recorded on a number of items on a daily basis for the entire school year with week end results, month end results, quarter end results and year end results for each rep and also for all the reps combined. Hope I am clear here. What I have done since Excel will not give me enough room for an entire calendar year is I have created a workbook with a worksheet for each quarter and a year end for each individual rep plus another set for the combined totals...for a whopping total of 25 sheets. I am currently looking at almost 3.5 meg on this workbook. As the school year turns over the dates need to be readjusted along with checking to make sure week end, month end, etc. all changes accordingly. Fine for someone that knows how to work with Excel not for someone who doesn't. The other wrinkle, which I just found out about a month or so back, is that the person who does all of the data entry at the school uses a Mac whereas I use an IBM so we have had some interesting translation issues...and I thought I was creating the problems!! Anyway, is Excel the best tool to use? Should we be looking at something else like a data base or statistical software? Any thoughts, recommendations would be greatly appreciated. Thanks so much in advance! Pat |
#3
|
|||
|
|||
Pat,
When your only tool is a hammer, all problems look like nails. Excel will work fine, though a database program would be better. But the learning curve is steep for the transition, and Excel will actually do a pretty good job. First off: Do NOT use multiple sheets. Set up a database on a single sheet - there will be room for 65,000 entries, so I doubt space will be a limitation. Use columns for all your data: Rep, items, prices, amounts, Sold to, etc., as well as columns for week, month, quarter, and year ( all of which can be generated using formulas from the date value). Then you will be able to use one of the most powerful features of Excel - Pivot Tables. You will be able to quickly summarize your data any way that you want (given the constraints of the data that you've entered). For example, you could look at one rep (or all reps), one week, one month, one school, one item, etc. etc. with just a few clicks. See http://www.cpearson.com/excel/pivots.htm for a quick introduction to pivot tables. There are other sources as well - simply playing around with them will help, too. HTH, Bernie MS Excel MVP "Pat J Maine" <Pat J wrote in message ... This past summer you all were wonderfully helpful with a calculation problem I was having. Now I am seeking help again. I need to know if I am using the best vehicle to track these stats for a client. The client needs to track stats on five sales reps (potential of more reps in the future). Data needs to be recorded on a number of items on a daily basis for the entire school year with week end results, month end results, quarter end results and year end results for each rep and also for all the reps combined. Hope I am clear here. What I have done since Excel will not give me enough room for an entire calendar year is I have created a workbook with a worksheet for each quarter and a year end for each individual rep plus another set for the combined totals...for a whopping total of 25 sheets. I am currently looking at almost 3.5 meg on this workbook. As the school year turns over the dates need to be readjusted along with checking to make sure week end, month end, etc. all changes accordingly. Fine for someone that knows how to work with Excel not for someone who doesn't. The other wrinkle, which I just found out about a month or so back, is that the person who does all of the data entry at the school uses a Mac whereas I use an IBM so we have had some interesting translation issues...and I thought I was creating the problems!! Anyway, is Excel the best tool to use? Should we be looking at something else like a data base or statistical software? Any thoughts, recommendations would be greatly appreciated. Thanks so much in advance! Pat |
#4
|
|||
|
|||
Thanks JE and Bernie for responding. This workbook went from something
originally designed as small and simple to something much larger and more complex. If only I had known, I would never have set this up the way it is. Hindsight!! They have also added a ton of data conversions (i.e., looking at ratios of applicants to enrollments, etc...way too many to list off here at this point). I know that I am not going to be around forever and someone else is going to have to maintain this so I am now trying to take a look at how to simplify it or move it or something. I will definitely take a look into the pivot tables. I have not used them before but I will read up on it. Thanks again. It is appreciated. "Pat J Maine" wrote: This past summer you all were wonderfully helpful with a calculation problem I was having. Now I am seeking help again. I need to know if I am using the best vehicle to track these stats for a client. The client needs to track stats on five sales reps (potential of more reps in the future). Data needs to be recorded on a number of items on a daily basis for the entire school year with week end results, month end results, quarter end results and year end results for each rep and also for all the reps combined. Hope I am clear here. What I have done since Excel will not give me enough room for an entire calendar year is I have created a workbook with a worksheet for each quarter and a year end for each individual rep plus another set for the combined totals...for a whopping total of 25 sheets. I am currently looking at almost 3.5 meg on this workbook. As the school year turns over the dates need to be readjusted along with checking to make sure week end, month end, etc. all changes accordingly. Fine for someone that knows how to work with Excel not for someone who doesn't. The other wrinkle, which I just found out about a month or so back, is that the person who does all of the data entry at the school uses a Mac whereas I use an IBM so we have had some interesting translation issues...and I thought I was creating the problems!! Anyway, is Excel the best tool to use? Should we be looking at something else like a data base or statistical software? Any thoughts, recommendations would be greatly appreciated. Thanks so much in advance! Pat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|