I kind of crossposted over on the Access forums, but I need some opinions.

I'm in a new job with a college sports team and there is an existing roster
of team members on Excel. It contains all the usual info name, address,
ssn, position played, hght., weight.. etc. There are other spreadsheets with
team member with other info, such as home of record, parents names, summer
address, etc. There are other spreadsheets with
possible recruits with similar information. The coaches are familiar with
the excel format.

I'd like all the info to be in one place. But it seems that if I put it all
into one spreadsheet, it is going to have a heck of a lot of fields and would
be unweldy to view. Plus the possible recruits would be mixed in with the
current team members (even though I'd have a field that could be checked
"team or recruit".

Is there a way to connect the various separate excel spreadsheets somehow so
that they'd work together?
Or is the only way to have these connections by
using a database program such as access?

I use the spreadsheets to generate correspondence, roster lists for the
coaches, labels, nametags, etc. I also use it for reports (names of recruits
who visited etc),The coaches will often use the info by using "save as" and
then deleting the stuff they don't need and adding stuff they do. ( One
example, the "lifting coach" will copy the names and heights and weights and
then add his own columns for their workouts.)

If I switch this stuff over to access, will what I gain in flexiblity be
lost in the coaches ability to manipulate the data themselves? (I could do
the same thing that the lifting coach does by using a directory merge with
word, but the coaches aren't as familiar with mail merge as I am.) There are
many other examples of times when the coaches would need to grab this data,
so I'm wondering if I ought to just leave it all in excel.

So what are the pros/cons access/excel in my situation?

TIA,
Jo

Access will give you the advantages of a database, relational tables et al,
which with all of the different data types you mention, could be very
useful. Access is actually two products, a database and a data
query/analyser. You could use Access per se to do everything, but you might
find it simpler to create the data in an Access database, and use Excel to
query that database to pull back the data into Excel reports. This would
work well for you coaches as well, their query could be specific to the data
they are interested in, no more deleting data.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jo4321" wrote in message
...
I kind of crossposted over on the Access forums, but I need some opinions.

I'm in a new job with a college sports team and there is an existing
roster
of team members on Excel. It contains all the usual info name, address,
ssn, position played, hght., weight.. etc. There are other spreadsheets
with
team member with other info, such as home of record, parents names, summer
address, etc. There are other spreadsheets with
possible recruits with similar information. The coaches are familiar with
the excel format.

I'd like all the info to be in one place. But it seems that if I put it
all
into one spreadsheet, it is going to have a heck of a lot of fields and
would
be unweldy to view. Plus the possible recruits would be mixed in with the
current team members (even though I'd have a field that could be checked
"team or recruit".

Is there a way to connect the various separate excel spreadsheets somehow
so
that they'd work together?
Or is the only way to have these connections by
using a database program such as access?

I use the spreadsheets to generate correspondence, roster lists for the
coaches, labels, nametags, etc. I also use it for reports (names of
recruits
who visited etc),The coaches will often use the info by using "save as"
and
then deleting the stuff they don't need and adding stuff they do. ( One
example, the "lifting coach" will copy the names and heights and weights
and
then add his own columns for their workouts.)

If I switch this stuff over to access, will what I gain in flexiblity be
lost in the coaches ability to manipulate the data themselves? (I could
do
the same thing that the lifting coach does by using a directory merge with
word, but the coaches aren't as familiar with mail merge as I am.) There
are
many other examples of times when the coaches would need to grab this
data,
so I'm wondering if I ought to just leave it all in excel.

So what are the pros/cons access/excel in my situation?

TIA,
Jo

Personally, I would stick with Excel. Of course I'm more familiar with it
than I am with Access...(same as your users). Unless you can define a
significant benefit to THEM by switching to Access, they don't need the
aggravation of having to learn new software just to make a change. It just
takes time away from their job.....and in the case of Access, it will take a
lot of time. Technical benefits be dammed.....unless the USER percieves a
benefit, there is none. I once designed an extensive Training Matrix Program
for a Client in Excel..... well over 1000 employees, both active and
inactive. "Someone" convinced them to change the whole thing over to Access
during my absence. The users hated it, too hard for them to
use/learn.....they being Excel people. When I returned, they had me re-do it
again, back in to Excel. And incidently, I did it in Excel '97, because that
was the version most of the users had on their computers. I found that some
of the macros I had written in XL2k would not work in '97 and was having to
constantly make repairs.....so I dropped down to '97 from then on....no more
problems. Some of the users have upgraded to some newer versions of Excel
and the program still works fine. For what it's worth, such has been my
experience.

Vaya con Dios,
Chuck, CABGx3



"Jo4321" wrote:

I kind of crossposted over on the Access forums, but I need some opinions.

I'm in a new job with a college sports team and there is an existing roster
of team members on Excel. It contains all the usual info name, address,
ssn, position played, hght., weight.. etc. There are other spreadsheets with
team member with other info, such as home of record, parents names, summer
address, etc. There are other spreadsheets with
possible recruits with similar information. The coaches are familiar with
the excel format.

I'd like all the info to be in one place. But it seems that if I put it all
into one spreadsheet, it is going to have a heck of a lot of fields and would
be unweldy to view. Plus the possible recruits would be mixed in with the
current team members (even though I'd have a field that could be checked
"team or recruit".

Is there a way to connect the various separate excel spreadsheets somehow so
that they'd work together?
Or is the only way to have these connections by
using a database program such as access?

I use the spreadsheets to generate correspondence, roster lists for the
coaches, labels, nametags, etc. I also use it for reports (names of recruits
who visited etc),The coaches will often use the info by using "save as" and
then deleting the stuff they don't need and adding stuff they do. ( One
example, the "lifting coach" will copy the names and heights and weights and
then add his own columns for their workouts.)

If I switch this stuff over to access, will what I gain in flexiblity be
lost in the coaches ability to manipulate the data themselves? (I could do
the same thing that the lifting coach does by using a directory merge with
word, but the coaches aren't as familiar with mail merge as I am.) There are
many other examples of times when the coaches would need to grab this data,
so I'm wondering if I ought to just leave it all in excel.

So what are the pros/cons access/excel in my situation?

TIA,
Jo

Makes sense.

Does excel have a way to link two different spreadsheets where changes made
to one will go over to the other? (I don't need instructions on how to do it
yet, I can do more research later, I just need to know IF it can be done)

For example, if I have one spreadsheet listing names, addresses, email
addresses and phone numbers, and another spreadsheet that lists the name
again, the position they play, the high school they attended, their major.
If I delete a record on one spreadsheet, can I set it up to automatically
delete it on the other?

Because the problem I'm having right now in keeping it ALL in Excel is that
there are two many spreadsheets with different info that aren't linked. That
means if one person deletes or adds a player, he may change it on one
spreadsheet but not on the other.

Jo


"CLR" wrote:

Personally, I would stick with Excel. Of course I'm more familiar with it
than I am with Access...(same as your users). Unless you can define a
significant benefit to THEM by switching to Access, they don't need the
aggravation of having to learn new software just to make a change. It just
takes time away from their job.....and in the case of Access, it will take a
lot of time. Technical benefits be dammed.....unless the USER percieves a
benefit, there is none. I once designed an extensive Training Matrix Program
for a Client in Excel..... well over 1000 employees, both active and
inactive. "Someone" convinced them to change the whole thing over to Access
during my absence. The users hated it, too hard for them to
use/learn.....they being Excel people. When I returned, they had me re-do it
again, back in to Excel. And incidently, I did it in Excel '97, because that
was the version most of the users had on their computers. I found that some
of the macros I had written in XL2k would not work in '97 and was having to
constantly make repairs.....so I dropped down to '97 from then on....no more
problems. Some of the users have upgraded to some newer versions of Excel
and the program still works fine. For what it's worth, such has been my
experience.

Vaya con Dios,
Chuck, CABGx3



"Jo4321" wrote:

I kind of crossposted over on the Access forums, but I need some opinions.

I'm in a new job with a college sports team and there is an existing roster
of team members on Excel. It contains all the usual info name, address,
ssn, position played, hght., weight.. etc. There are other spreadsheets with
team member with other info, such as home of record, parents names, summer
address, etc. There are other spreadsheets with
possible recruits with similar information. The coaches are familiar with
the excel format.

I'd like all the info to be in one place. But it seems that if I put it all
into one spreadsheet, it is going to have a heck of a lot of fields and would
be unweldy to view. Plus the possible recruits would be mixed in with the
current team members (even though I'd have a field that could be checked
"team or recruit".

Is there a way to connect the various separate excel spreadsheets somehow so
that they'd work together?
Or is the only way to have these connections by
using a database program such as access?

I use the spreadsheets to generate correspondence, roster lists for the
coaches, labels, nametags, etc. I also use it for reports (names of recruits
who visited etc),The coaches will often use the info by using "save as" and
then deleting the stuff they don't need and adding stuff they do. ( One
example, the "lifting coach" will copy the names and heights and weights and
then add his own columns for their workouts.)

If I switch this stuff over to access, will what I gain in flexiblity be
lost in the coaches ability to manipulate the data themselves? (I could do
the same thing that the lifting coach does by using a directory merge with
word, but the coaches aren't as familiar with mail merge as I am.) There are
many other examples of times when the coaches would need to grab this data,
so I'm wondering if I ought to just leave it all in excel.

So what are the pros/cons access/excel in my situation?

TIA,
Jo

Personally, I would combine as many of the different spreadsheets as I could
into one master file. You have over 250 columns and 65000 rows to play with.
Viewing is no problem if taken into the design. For instance, you can have
a main menu that would have buttons for various categories of information,
such as "Personal", "SkillSet", or whatever your various different files now
separate, and when each button is pressed, only the columns for that category
are visible, the rest are hidden.......or the whole thing can be viewed at
once if desired. As for Reporting, or Analysis, specific columns and or rows
can be pulled out of this database and moved to a "working" sheet for
processing and formatting that does not affect the database. If you must
deal with information in multiple Excel files, it is doable with VBA.....each
file can be opened from the master file, when needed, Edited, or information
extracted, and then the file closed, all under program (VBA) control.
Virtually anything you can imagine doing by manual means, can be automated
with VBA and Excel can do at the push of a button.

I have written small programs in Excel whereby the user gets a downloaded
file from the company computers. They open my program, retrieve the
downloaded file, and extract data from it, format the data for presentation,
run calculations and other analysis on the data, and even email portions of
it to others. The downloaded file can be in the form of an Excel file, a
text file, a .pdf, or it could be the result of a MSQuery of and external
database from Access, Excel, or other database programs.......the
possibilities are endless. It does not matter if Excel is THE most efficient
program to do all the things one might want to do in, but that it CAN do
those things and the user is comfortable with it, so thereby will use it.
The pureists will argue that point, but who cares? No matter how nice a
program is or how efficient, if the user is uncomfortable with it, they won't
use it.

Unfortunately, some Management folks are oblivious to the benefits that
Excel can provide their organization, or have been swayed by someone that
"Excel is not a database program so therefore they should be using Access",
when actually they don't really understand either one. Some are afraid of
anything that runs under "those evil macros".......thereby shun them as they
would a virus. Well granted, the company does need access to someone with a
working knowledge of VBA to modify and update those programs, but today
they're not too hard to find. Anyway, most of the programs I've developed
are still running under their original revision, or at least one within a
couple of weeks of orignal issue.......that's the beauty of macros, they do
the same thing every time.

Well, I hope my ramblings have given you some answers you seek......good
luck with your project, and if you run in to difficulties, you can always
come back here for help.

Vaya con Dios,
Chuck, CABGx3







"Jo4321" wrote:

Makes sense.

Does excel have a way to link two different spreadsheets where changes made
to one will go over to the other? (I don't need instructions on how to do it
yet, I can do more research later, I just need to know IF it can be done)

For example, if I have one spreadsheet listing names, addresses, email
addresses and phone numbers, and another spreadsheet that lists the name
again, the position they play, the high school they attended, their major.
If I delete a record on one spreadsheet, can I set it up to automatically
delete it on the other?

Because the problem I'm having right now in keeping it ALL in Excel is that
there are two many spreadsheets with different info that aren't linked. That
means if one person deletes or adds a player, he may change it on one
spreadsheet but not on the other.

Jo


"CLR" wrote:

Personally, I would stick with Excel. Of course I'm more familiar with it
than I am with Access...(same as your users). Unless you can define a
significant benefit to THEM by switching to Access, they don't need the
aggravation of having to learn new software just to make a change. It just
takes time away from their job.....and in the case of Access, it will take a
lot of time. Technical benefits be dammed.....unless the USER percieves a
benefit, there is none. I once designed an extensive Training Matrix Program
for a Client in Excel..... well over 1000 employees, both active and
inactive. "Someone" convinced them to change the whole thing over to Access
during my absence. The users hated it, too hard for them to
use/learn.....they being Excel people. When I returned, they had me re-do it
again, back in to Excel. And incidently, I did it in Excel '97, because that
was the version most of the users had on their computers. I found that some
of the macros I had written in XL2k would not work in '97 and was having to
constantly make repairs.....so I dropped down to '97 from then on....no more
problems. Some of the users have upgraded to some newer versions of Excel
and the program still works fine. For what it's worth, such has been my
experience.

Vaya con Dios,
Chuck, CABGx3



"Jo4321" wrote:

I kind of crossposted over on the Access forums, but I need some opinions.

I'm in a new job with a college sports team and there is an existing roster
of team members on Excel. It contains all the usual info name, address,
ssn, position played, hght., weight.. etc. There are other spreadsheets with
team member with other info, such as home of record, parents names, summer
address, etc. There are other spreadsheets with
possible recruits with similar information. The coaches are familiar with
the excel format.

I'd like all the info to be in one place. But it seems that if I put it all
into one spreadsheet, it is going to have a heck of a lot of fields and would
be unweldy to view. Plus the possible recruits would be mixed in with the
current team members (even though I'd have a field that could be checked
"team or recruit".

Is there a way to connect the various separate excel spreadsheets somehow so
that they'd work together?
Or is the only way to have these connections by
using a database program such as access?

I use the spreadsheets to generate correspondence, roster lists for the
coaches, labels, nametags, etc. I also use it for reports (names of recruits
who visited etc),The coaches will often use the info by using "save as" and
then deleting the stuff they don't need and adding stuff they do. ( One
example, the "lifting coach" will copy the names and heights and weights and
then add his own columns for their workouts.)

If I switch this stuff over to access, will what I gain in flexiblity be
lost in the coaches ability to manipulate the data themselves? (I could do
the same thing that the lifting coach does by using a directory merge with
word, but the coaches aren't as familiar with mail merge as I am.) There are
many other examples of times when the coaches would need to grab this data,
so I'm wondering if I ought to just leave it all in excel.

So what are the pros/cons access/excel in my situation?

TIA,
Jo

Awesome CLR. That would work for us, I think, if I could combine the info
into one spreadsheet but still have the data easy to view. I didn't know
that there was a way to view unweildy spreadsheets to "hide" some of the
columns

Now, so I can look up tutorials on how to do this, what would that process
be called? (making buttons that have some kind of code to hide the columns)

Also, what does the acronym VBA mean that you mentioned a few times.

Thank you.
Jo

"CLR" wrote:

Personally, I would combine as many of the different spreadsheets as I could
into one master file. You have over 250 columns and 65000 rows to play with.
Viewing is no problem if taken into the design. For instance, you can have
a main menu that would have buttons for various categories of information,
such as "Personal", "SkillSet", or whatever your various different files now
separate, and when each button is pressed, only the columns for that category
are visible, the rest are hidden.......or the whole thing can be viewed at
once if desired. As for Reporting, or Analysis, specific columns and or rows
can be pulled out of this database and moved to a "working" sheet for
processing and formatting that does not affect the database. If you must
deal with information in multiple Excel files, it is doable with VBA.....each
file can be opened from the master file, when needed, Edited, or information
extracted, and then the file closed, all under program (VBA) control.
Virtually anything you can imagine doing by manual means, can be automated
with VBA and Excel can do at the push of a button.

I have written small programs in Excel whereby the user gets a downloaded
file from the company computers. They open my program, retrieve the
downloaded file, and extract data from it, format the data for presentation,
run calculations and other analysis on the data, and even email portions of
it to others. The downloaded file can be in the form of an Excel file, a
text file, a .pdf, or it could be the result of a MSQuery of and external
database from Access, Excel, or other database programs.......the
possibilities are endless. It does not matter if Excel is THE most efficient
program to do all the things one might want to do in, but that it CAN do
those things and the user is comfortable with it, so thereby will use it.
The pureists will argue that point, but who cares? No matter how nice a
program is or how efficient, if the user is uncomfortable with it, they won't
use it.

Hi Jo

in addition to the excellent advice Chuck has given you, you can hide
columns, rows etc without using VBA (Visual Basic for Applications) if you
wish by using
ViewsCustom Views.

Select the columns you wish to see, by hiding the ones that are not relevant
for that particular purpose, then select ViewsCustom ViewsAddgive it a
meaningful name.
Unhide all the columns and do the same thing, but call that Normal or Full.
You can create as many views as you wish in this manner, and select them at
will.

To make selection easier, add Custom Views to your toolbar.
ViewToolbarsCustomisechoose View in the left hand panedrag Custom View
from the right hand panedrop in somewhere on your toolbar
(Note it must be somewhere before the final dropdown at the end of the
toolbar)

Now, you will have the different views available as a dropdown at all times.

If you also mark the top row (header row) of your sheet, and choose
DataFilterAutofilter, you will find that you can easily filter your data
within any view, to show only the rows that you wish.

--

Regards
Roger Govier

"Jo4321" wrote in message
...
Awesome CLR. That would work for us, I think, if I could combine the info
into one spreadsheet but still have the data easy to view. I didn't
know
that there was a way to view unweildy spreadsheets to "hide" some of the
columns

Now, so I can look up tutorials on how to do this, what would that process
be called? (making buttons that have some kind of code to hide the
columns)

Also, what does the acronym VBA mean that you mentioned a few times.

Thank you.
Jo

"CLR" wrote:

Personally, I would combine as many of the different spreadsheets as I
could
into one master file. You have over 250 columns and 65000 rows to play
with.
Viewing is no problem if taken into the design. For instance, you can
have
a main menu that would have buttons for various categories of
information,
such as "Personal", "SkillSet", or whatever your various different files
now
separate, and when each button is pressed, only the columns for that
category
are visible, the rest are hidden.......or the whole thing can be viewed
at
once if desired. As for Reporting, or Analysis, specific columns and or
rows
can be pulled out of this database and moved to a "working" sheet for
processing and formatting that does not affect the database. If you must
deal with information in multiple Excel files, it is doable with
VBA.....each
file can be opened from the master file, when needed, Edited, or
information
extracted, and then the file closed, all under program (VBA) control.
Virtually anything you can imagine doing by manual means, can be
automated
with VBA and Excel can do at the push of a button.

I have written small programs in Excel whereby the user gets a downloaded
file from the company computers. They open my program, retrieve the
downloaded file, and extract data from it, format the data for
presentation,
run calculations and other analysis on the data, and even email portions
of
it to others. The downloaded file can be in the form of an Excel file, a
text file, a .pdf, or it could be the result of a MSQuery of and external
database from Access, Excel, or other database programs.......the
possibilities are endless. It does not matter if Excel is THE most
efficient
program to do all the things one might want to do in, but that it CAN do
those things and the user is comfortable with it, so thereby will use it.
The pureists will argue that point, but who cares? No matter how nice a
program is or how efficient, if the user is uncomfortable with it, they
won't
use it.

VBA stands for "Visual Basic for Applications". It is the programming
language that Excel, and some other Application Programs use for writing
their Macros. Macros are really miniature "Computer Programs", and are the
"code" that would be applied to the buttons to perform the hiding, as well as
other tasks. If you're new to VBA, then you have a ways to go to get to
where you are able to work there........but it's much worth the trip if you
intend to stay in Excel. On the other hand, Roger's suggestion about using
the Custom Views feature to do your column hiding may be the way to start
out. Personally, I'm not familiar with that feature but it should work fine
as Roger describes, and you can always switch over to VBA later if desired.

Incidently, on large databases I usually do a Windows FreezePanes about
row 10, and use the area above the Freeze Line for my Title Block, Navigation
buttons to go quickly to the top or bottom of the database, toggle Autofilter
off and on, etc etc.

Now for some much-needed coffee.......Good Luck with your project, and be
sure and post back if you have more questions.........

Vaya con Dios,
Chuck, CABGx3



"Jo4321" wrote:

Awesome CLR. That would work for us, I think, if I could combine the info
into one spreadsheet but still have the data easy to view. I didn't know
that there was a way to view unweildy spreadsheets to "hide" some of the
columns

Now, so I can look up tutorials on how to do this, what would that process
be called? (making buttons that have some kind of code to hide the columns)

Also, what does the acronym VBA mean that you mentioned a few times.

Thank you.
Jo

"CLR" wrote:

Personally, I would combine as many of the different spreadsheets as I could
into one master file. You have over 250 columns and 65000 rows to play with.
Viewing is no problem if taken into the design. For instance, you can have
a main menu that would have buttons for various categories of information,
such as "Personal", "SkillSet", or whatever your various different files now
separate, and when each button is pressed, only the columns for that category
are visible, the rest are hidden.......or the whole thing can be viewed at
once if desired. As for Reporting, or Analysis, specific columns and or rows
can be pulled out of this database and moved to a "working" sheet for
processing and formatting that does not affect the database. If you must
deal with information in multiple Excel files, it is doable with VBA.....each
file can be opened from the master file, when needed, Edited, or information
extracted, and then the file closed, all under program (VBA) control.
Virtually anything you can imagine doing by manual means, can be automated
with VBA and Excel can do at the push of a button.

I have written small programs in Excel whereby the user gets a downloaded
file from the company computers. They open my program, retrieve the
downloaded file, and extract data from it, format the data for presentation,
run calculations and other analysis on the data, and even email portions of
it to others. The downloaded file can be in the form of an Excel file, a
text file, a .pdf, or it could be the result of a MSQuery of and external
database from Access, Excel, or other database programs.......the
possibilities are endless. It does not matter if Excel is THE most efficient
program to do all the things one might want to do in, but that it CAN do
those things and the user is comfortable with it, so thereby will use it.
The pureists will argue that point, but who cares? No matter how nice a
program is or how efficient, if the user is uncomfortable with it, they won't
use it.

I know this is an old post, however I feel access has been a bit
misrepresented!
Access wuold be able to achieve everything set out here and can be really
easy to use if set up correctly, with forms displaying data however you want.
and quereying the data can be made to be very straightforward.
It's a different way of thinking to set up than excel. but once you've made
the change, you'll never go back!

A database is really what is required here not a spreadsheet...
--
steve adlam


"Jo4321" wrote:

I kind of crossposted over on the Access forums, but I need some opinions.

I'm in a new job with a college sports team and there is an existing roster
of team members on Excel. It contains all the usual info name, address,
ssn, position played, hght., weight.. etc. There are other spreadsheets with
team member with other info, such as home of record, parents names, summer
address, etc. There are other spreadsheets with
possible recruits with similar information. The coaches are familiar with
the excel format.

I'd like all the info to be in one place. But it seems that if I put it all
into one spreadsheet, it is going to have a heck of a lot of fields and would
be unweldy to view. Plus the possible recruits would be mixed in with the
current team members (even though I'd have a field that could be checked
"team or recruit".

Is there a way to connect the various separate excel spreadsheets somehow so
that they'd work together?
Or is the only way to have these connections by
using a database program such as access?

I use the spreadsheets to generate correspondence, roster lists for the
coaches, labels, nametags, etc. I also use it for reports (names of recruits
who visited etc),The coaches will often use the info by using "save as" and
then deleting the stuff they don't need and adding stuff they do. ( One
example, the "lifting coach" will copy the names and heights and weights and
then add his own columns for their workouts.)

If I switch this stuff over to access, will what I gain in flexiblity be
lost in the coaches ability to manipulate the data themselves? (I could do
the same thing that the lifting coach does by using a directory merge with
word, but the coaches aren't as familiar with mail merge as I am.) There are
many other examples of times when the coaches would need to grab this data,
so I'm wondering if I ought to just leave it all in excel.

So what are the pros/cons access/excel in my situation?

TIA,
Jo