If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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). |
#2
|
|||
|
|||
Linking Question
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). |
#3
|
|||
|
|||
Linking Question
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). |
#4
|
|||
|
|||
Linking Question
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). |
#5
|
|||
|
|||
Linking Question
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. |
Thread Tools | |
Display Modes | |
|
|