Thread: 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

It's hard to make any specific suggestions given the general description
in our post.

You can certainly keep track of the items you list in a single XL
workbook, though 3.5 MB seems a bit steep for 5 reps x 365 days.

Using Pivot Tables to summarize your data instead of fixed summary cells
may make your sheets more manageable - you can group date-related fields
in Months, Quarters, etc.

Mac vs PC should be little or no problem - the file formats of
MacXL98/01/v.X/04 and WinXL97/00/02/03 are identical.

Whether XL would be better than a database app largely depends on what
you're trying to do with the data - for simple reporting, a DBMS would
probably be preferable if you have the funding and training. FileMaker
Pro and 4D are both excellent cross-platform database managers (and both
are generally more robust than Win-only Access).


In article <74BEE9C9-F9FD-41D9-BB2C-CA8765B2D082@microsoft.com>,
"Pat J Maine" <Pat J Maine@discussions.microsoft.com> 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

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 Maine@discussions.microsoft.com> wrote in message
news:74BEE9C9-F9FD-41D9-BB2C-CA8765B2D082@microsoft.com...
> 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

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