Linking Question

In order to prevent excessive entries of the same information my company
wants to link a master account list to the individual lists that we currently
updated for the purposes of using the master spreadsheet in Access for
reporting of information. For example:

Spreadsheets X234, X235 and X236 are all individual sheets; X234 contains 25
accounts, X235 contains 500 accounts and X236 contains 67 accounts. The
master spreadsheet contains all 592 accounts. We receive a notice on client
#56 on spreadsheet X235. Instead of updating the information twice (on the
individual and the master) we enter it on the individual only, but it updates
automatically on the master. The director doesn't want to use only the
master sheet because it will not include ALL the aspects that the individual
spreadsheets contain.

Is this possible?? Even if it is a lengthy process we would like to do this
(it's a temporary fix until our database is completed, but that won't be for
another few months).

What you propose to do is error prone and impractical. You should always
enter your data in one place and only one place, then use Excel's filtering
and sorting capabilities to generate reports, etc.

So...my advice is to enter ALL the data on the master sheet. When the
database app is done, the person who is developing that should be able to
easily put the appropriate Excel columns into the correct Access tables.

"akemeny" wrote:

In order to prevent excessive entries of the same information my company
wants to link a master account list to the individual lists that we currently
updated for the purposes of using the master spreadsheet in Access for
reporting of information. For example:

Spreadsheets X234, X235 and X236 are all individual sheets; X234 contains 25
accounts, X235 contains 500 accounts and X236 contains 67 accounts. The
master spreadsheet contains all 592 accounts. We receive a notice on client
#56 on spreadsheet X235. Instead of updating the information twice (on the
individual and the master) we enter it on the individual only, but it updates
automatically on the master. The director doesn't want to use only the
master sheet because it will not include ALL the aspects that the individual
spreadsheets contain.

Is this possible?? Even if it is a lengthy process we would like to do this
(it's a temporary fix until our database is completed, but that won't be for
another few months).

I realize that it seems impractical, but it is a project that I have been
told to complete before our meeting next week so that we can present it for
the director and a few higher bosses. If it is possible to do it, but
impractical then that is what we will tell them. But first I must know the
process to put it together so that I can show them where things could go
wrong.

Thanks.

"Duke Carey" wrote:

What you propose to do is error prone and impractical. You should always
enter your data in one place and only one place, then use Excel's filtering
and sorting capabilities to generate reports, etc.

So...my advice is to enter ALL the data on the master sheet. When the
database app is done, the person who is developing that should be able to
easily put the appropriate Excel columns into the correct Access tables.

"akemeny" wrote:

In order to prevent excessive entries of the same information my company
wants to link a master account list to the individual lists that we currently
updated for the purposes of using the master spreadsheet in Access for
reporting of information. For example:

Spreadsheets X234, X235 and X236 are all individual sheets; X234 contains 25
accounts, X235 contains 500 accounts and X236 contains 67 accounts. The
master spreadsheet contains all 592 accounts. We receive a notice on client
#56 on spreadsheet X235. Instead of updating the information twice (on the
individual and the master) we enter it on the individual only, but it updates
automatically on the master. The director doesn't want to use only the
master sheet because it will not include ALL the aspects that the individual
spreadsheets contain.

Is this possible?? Even if it is a lengthy process we would like to do this
(it's a temporary fix until our database is completed, but that won't be for
another few months).

You would need to write a macro that either runs automatically when data is
changed on one of your 'subsidiary' sheets, and copies that data to the
master sheet. This is impractical, because you have to build in tests so
that it doesn't run before an individual "entry" is completed, and that is
messy unless you can determine programmatically when an entry is valid and
complete. I suspect the code will be messy regardless of your business rules.

Alternatively, you can write a macro that the user manually invokes when the
entry is complete, and trust that the macro will be run once only, and that
it won't be run prematurely before the entry is done.

Next is the question of correcting mistakes. Does the user fix it in the
subsidiary sheet, then go to master sheet, find the entry, and fix it? What
if they enter different info on one sheet than on the other.

With more thought there's certainly more objections, but that's enough to
get you started.

Can all these issues by

"akemeny" wrote:

I realize that it seems impractical, but it is a project that I have been
told to complete before our meeting next week so that we can present it for
the director and a few higher bosses. If it is possible to do it, but
impractical then that is what we will tell them. But first I must know the
process to put it together so that I can show them where things could go
wrong.

Thanks.

"Duke Carey" wrote:

What you propose to do is error prone and impractical. You should always
enter your data in one place and only one place, then use Excel's filtering
and sorting capabilities to generate reports, etc.

So...my advice is to enter ALL the data on the master sheet. When the
database app is done, the person who is developing that should be able to
easily put the appropriate Excel columns into the correct Access tables.

"akemeny" wrote:

In order to prevent excessive entries of the same information my company
wants to link a master account list to the individual lists that we currently
updated for the purposes of using the master spreadsheet in Access for
reporting of information. For example:

Spreadsheets X234, X235 and X236 are all individual sheets; X234 contains 25
accounts, X235 contains 500 accounts and X236 contains 67 accounts. The
master spreadsheet contains all 592 accounts. We receive a notice on client
#56 on spreadsheet X235. Instead of updating the information twice (on the
individual and the master) we enter it on the individual only, but it updates
automatically on the master. The director doesn't want to use only the
master sheet because it will not include ALL the aspects that the individual
spreadsheets contain.

Is this possible?? Even if it is a lengthy process we would like to do this
(it's a temporary fix until our database is completed, but that won't be for
another few months).

The thing that gets me is you said:
"my company wants to ...[use] the master spreadsheet in Access"

And:
"the master sheet ... will not include ALL the aspects"

I think there's a problem, there. Sounds like the current plan will
lose some data when your final database is engineered.


Is this supposed to be one file, with many sheets? Or many files?


Not a good long-term solution, but in a short-term pinch:
If your client list is fairly static, not being expanded greatly, you
could enter the name or number (something unique and sortable) in the
master sheet and use VLOOKUPs for the other desired data. Esp if this
is all one file.

Or you could add a column of straight 1,2,3 numbers in a new Column A
of all the files (hide it if you don't want it visible) and do
VLOOKUPs on that, this way you can add a whole bunch of extra rows for
each file to cover new clients that will be entered. Maybe base the
numbers on the file name to make them more unique:
X234_001,X234_002,etc.

The Vlookups can be sorted (perhaps automatically by event code) in
the master sheet just like regular data as long as the first column of
these numbers gets sorted with it. And if you have some additional
programming to do later, you can always sort on Col A again.


The places where things can go wrong are the same as any other Excel
file. User error/stupidity. Can try protection to limit damage.