Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Approach to Worksheet Synchronization
Hi All,
I'm hoping to get your opinion on the best approach to this task without having programming skills. I'm pretty good at Excel, use macros and read them some. Sometimes I copy macros from these news groups and do greatly appreciate the help. Here's my assignment. Its a responsibility matrix for all employees matching their job descriptions. Exec worksheet: A B C D 1 Task List VP1 VP2 VP3 2 Task 1 x x 3 Task 2 x 4 Task 3 x x x 5 Task 4 x Dept1 worksheet: A B C D 1 Task List VP1 A1 A2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x Dept2 worksheet: A B C D 1 Task List VP2 B1 B2 2 Task 1 x 3 Task 2 x x x 4 Task 3 x 5 Task 4 x Dept3 worksheet A B C D 1 Task List VP3 C1 C2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x x For Dept1, VP1's task in B2 would be =IF(Exec!$B2<"",Exec!$B2,"") and so on for B3, B4, etc. The point being that the President gets password/edit access to the Exec Worksheet and each of the three VP's get password/edit access to only their department and they cannot change their own task because it comes from the Exec worksheet. All other job positions like A1 or B2 have no relationship to other cells; only the VP's. This all seems well and good; the President and each VP have their own password protected worksheet. But, what do I do if someone wants to add or delete rows? Of course, this senario assumes that every worksheet has every task listed, even if that task is not being done by anyone on that worksheet, but I can't see any way around that. It's actually bigger than I presented here with 6 Executives, 10+VP Dept1, 2+VP Dept2, 5+VP Dept3, 12+VP Dept4 (35 positions). It looks like there will be about 600 tasks. Thank you, Michele |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Approach to Worksheet Synchronization
Michele
I'm not sure what you are asking help with. You are long on explanation (that's good) but short on questions. Are you asking how you can limit access to specific sheets, with passwords? Are you asking how you can limit access to specific cells? Are you asking how you can preclude anyone from adding or deleting a row? Or are you asking how you can accommodate someone adding or deleting a row? Please post back and clarify what you need help with. HTH Otto "mjones" wrote in message ups.com... Hi All, I'm hoping to get your opinion on the best approach to this task without having programming skills. I'm pretty good at Excel, use macros and read them some. Sometimes I copy macros from these news groups and do greatly appreciate the help. Here's my assignment. Its a responsibility matrix for all employees matching their job descriptions. Exec worksheet: A B C D 1 Task List VP1 VP2 VP3 2 Task 1 x x 3 Task 2 x 4 Task 3 x x x 5 Task 4 x Dept1 worksheet: A B C D 1 Task List VP1 A1 A2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x Dept2 worksheet: A B C D 1 Task List VP2 B1 B2 2 Task 1 x 3 Task 2 x x x 4 Task 3 x 5 Task 4 x Dept3 worksheet A B C D 1 Task List VP3 C1 C2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x x For Dept1, VP1's task in B2 would be =IF(Exec!$B2<"",Exec!$B2,"") and so on for B3, B4, etc. The point being that the President gets password/edit access to the Exec Worksheet and each of the three VP's get password/edit access to only their department and they cannot change their own task because it comes from the Exec worksheet. All other job positions like A1 or B2 have no relationship to other cells; only the VP's. This all seems well and good; the President and each VP have their own password protected worksheet. But, what do I do if someone wants to add or delete rows? Of course, this senario assumes that every worksheet has every task listed, even if that task is not being done by anyone on that worksheet, but I can't see any way around that. It's actually bigger than I presented here with 6 Executives, 10+VP Dept1, 2+VP Dept2, 5+VP Dept3, 12+VP Dept4 (35 positions). It looks like there will be about 600 tasks. Thank you, Michele |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Approach to Worksheet Synchronization
Hi Otto,
Thanks for responding. I really need to know how to handle adding/deleting rows in Exec so they will automatically add/delete in the Dept? worksheets. For example, adding a task between 2 and 3 (say 2a) in Exec needs to add the same task in the other 3 Dept? worksheets. I mentioned the passwords only because it would be okay to have all the postions in one spreadsheet, except for the fact that certain columns can only be editted by certain people and I don't think you can have five different passwords on one spreadsheet and I can't think of an alternative solution. I know how to limit access to specific sheets with passwords. I know how to limit access to specifc cells (with only one password). I hope you are able to help with this information. I really need to get this task done, I'm just trying to figure out the best way to get it done. Thanks again, Michele --------------- Otto Moehrbach wrote: Michele I'm not sure what you are asking help with. You are long on explanation (that's good) but short on questions. Are you asking how you can limit access to specific sheets, with passwords? Are you asking how you can limit access to specific cells? Are you asking how you can preclude anyone from adding or deleting a row? Or are you asking how you can accommodate someone adding or deleting a row? Please post back and clarify what you need help with. HTH Otto "mjones" wrote in message ups.com... Hi All, I'm hoping to get your opinion on the best approach to this task without having programming skills. I'm pretty good at Excel, use macros and read them some. Sometimes I copy macros from these news groups and do greatly appreciate the help. Here's my assignment. Its a responsibility matrix for all employees matching their job descriptions. Exec worksheet: A B C D 1 Task List VP1 VP2 VP3 2 Task 1 x x 3 Task 2 x 4 Task 3 x x x 5 Task 4 x Dept1 worksheet: A B C D 1 Task List VP1 A1 A2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x Dept2 worksheet: A B C D 1 Task List VP2 B1 B2 2 Task 1 x 3 Task 2 x x x 4 Task 3 x 5 Task 4 x Dept3 worksheet A B C D 1 Task List VP3 C1 C2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x x For Dept1, VP1's task in B2 would be =IF(Exec!$B2<"",Exec!$B2,"") and so on for B3, B4, etc. The point being that the President gets password/edit access to the Exec Worksheet and each of the three VP's get password/edit access to only their department and they cannot change their own task because it comes from the Exec worksheet. All other job positions like A1 or B2 have no relationship to other cells; only the VP's. This all seems well and good; the President and each VP have their own password protected worksheet. But, what do I do if someone wants to add or delete rows? Of course, this senario assumes that every worksheet has every task listed, even if that task is not being done by anyone on that worksheet, but I can't see any way around that. It's actually bigger than I presented here with 6 Executives, 10+VP Dept1, 2+VP Dept2, 5+VP Dept3, 12+VP Dept4 (35 positions). It looks like there will be about 600 tasks. Thank you, Michele |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Approach to Worksheet Synchronization
Hi Otto,
Thanks so much for the kind offer to help with the password code, but this doesn't need to be protected like Fort Knox. Standard Excel password protection is okay. The users are not sophisticated; I'm happy if they know how to add a row. Yes, you can add and delete rows on a protected sheet by checking the box when protecting it 'Allow all users of this worksheet to' and check insert rows. This works well. Investigating my situation more carefully, I think the thing I'm really stuck on is - can I add a row to one of the four spreadsheets and have Excel add the same row (task) to the other three. It should also duplicate the date from columns A and B, which will contain the task # and the task description. I'm guessing this is too tricky so I'm not holding my breath, but that's what would make this solution work for my client. Thanks again, Michele Otto Moehrbach wrote: Michele The adding/deleting of rows presents a problem if all the positions are on one sheet. By using only one sheet, the sheet would have to be protected to deny access to the locked cells. When the sheet is protected, the user cannot add/delete a row. If each position is on a separate sheet, the entire sheet would be unprotected. But if you need to deny access to some of the cells in that sheet, then we're back to the problem that the sheet has to be protected and therefore, a row cannot be added or deleted. This whole thing would be easier if adding/deleting a row was not a factor. The following gives you an idea of how what you would work. You could have some code that would fire upon opening of the file. That code would present an Input Box and ask the user for a password. If the password is valid, the code would proceed to lock/unlock the necessary sheets so that only the columns/cells/rows that go with the password presented would be unlocked. The code would then protect the sheet and allow the user to do his thing. Subsequent code that would fire upon closing or saving the file would unprotect the sheet, lock all the cells and protect the sheet. That way, no access would be permitted the next time the file is opened without a password. The code can be hidden with a password if you wish so as not to allow someone to view the passwords. I don't know if you are familiar with the security of Excel. Basically, there is none. Excel is not meant to be a secure platform. Someone with knowledge of Excel will be able to break any security scheme you come up with. What I said above will work fine with the casual Excel user. If you think this might be something that would work for you, send me a file with everything laid out as you want, and provide all the details regarding what cells/columns/rows you want locked/unlocked with what password. Also provide a password for hiding the code. If you wish to not provide me the passwords, I'll come up with some and you can change them later in the code. Fake your data as you wish because all I need is the layout. Send me that file and I'll work up something for you. My email address is . Remove the "nop" from this address. HTH Otto "mjones" wrote in message ups.com... Hi Otto, Thanks for responding. I really need to know how to handle adding/deleting rows in Exec so they will automatically add/delete in the Dept? worksheets. For example, adding a task between 2 and 3 (say 2a) in Exec needs to add the same task in the other 3 Dept? worksheets. I mentioned the passwords only because it would be okay to have all the postions in one spreadsheet, except for the fact that certain columns can only be editted by certain people and I don't think you can have five different passwords on one spreadsheet and I can't think of an alternative solution. I know how to limit access to specific sheets with passwords. I know how to limit access to specifc cells (with only one password). I hope you are able to help with this information. I really need to get this task done, I'm just trying to figure out the best way to get it done. Thanks again, Michele --------------- Otto Moehrbach wrote: Michele I'm not sure what you are asking help with. You are long on explanation (that's good) but short on questions. Are you asking how you can limit access to specific sheets, with passwords? Are you asking how you can limit access to specific cells? Are you asking how you can preclude anyone from adding or deleting a row? Or are you asking how you can accommodate someone adding or deleting a row? Please post back and clarify what you need help with. HTH Otto "mjones" wrote in message ups.com... Hi All, I'm hoping to get your opinion on the best approach to this task without having programming skills. I'm pretty good at Excel, use macros and read them some. Sometimes I copy macros from these news groups and do greatly appreciate the help. Here's my assignment. Its a responsibility matrix for all employees matching their job descriptions. Exec worksheet: A B C D 1 Task List VP1 VP2 VP3 2 Task 1 x x 3 Task 2 x 4 Task 3 x x x 5 Task 4 x Dept1 worksheet: A B C D 1 Task List VP1 A1 A2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x Dept2 worksheet: A B C D 1 Task List VP2 B1 B2 2 Task 1 x 3 Task 2 x x x 4 Task 3 x 5 Task 4 x Dept3 worksheet A B C D 1 Task List VP3 C1 C2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x x For Dept1, VP1's task in B2 would be =IF(Exec!$B2<"",Exec!$B2,"") and so on for B3, B4, etc. The point being that the President gets password/edit access to the Exec Worksheet and each of the three VP's get password/edit access to only their department and they cannot change their own task because it comes from the Exec worksheet. All other job positions like A1 or B2 have no relationship to other cells; only the VP's. This all seems well and good; the President and each VP have their own password protected worksheet. But, what do I do if someone wants to add or delete rows? Of course, this senario assumes that every worksheet has every task listed, even if that task is not being done by anyone on that worksheet, but I can't see any way around that. It's actually bigger than I presented here with 6 Executives, 10+VP Dept1, 2+VP Dept2, 5+VP Dept3, 12+VP Dept4 (35 positions). It looks like there will be about 600 tasks. Thank you, Michele |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Approach to Worksheet Synchronization
Michele
The code to detect that a row has been added/deleted is fairly straight forward. Being able to discern which it was, added or deleted, is a bit tricky but it can be done. Doing the same in the other sheets is easy. Copying anything the Exec enters into any cell and pasting that value in the same cells in the other sheets is also easy. I'll come up with some code for you using the data you furnished in your post. One bit of information I need from you is this: Can you give me the maximum range of your data in columns and rows? By this I mean, is it 10 rows, 100 rows, 1000 rows, 10,000 rows, etc ? Five columns, 20, 200? I also need this: From what you say I gather that the only changes made will be made in the Exec sheets. Correct? If that is the case, then you need for the code to copy data from columns A:D, not just Columns A:B. Is that correct? You said that your actual task involves numerous Execs, and VPs, not just one Exec and 3 VPs. Do you want me to develop the code for just the One Exec and 3 VPs and you take it from there? Or do you need more than that? Or is each Exec and his VPs in a different file? Otto "mjones" wrote in message oups.com... Hi Otto, Thanks so much for the kind offer to help with the password code, but this doesn't need to be protected like Fort Knox. Standard Excel password protection is okay. The users are not sophisticated; I'm happy if they know how to add a row. Yes, you can add and delete rows on a protected sheet by checking the box when protecting it 'Allow all users of this worksheet to' and check insert rows. This works well. Investigating my situation more carefully, I think the thing I'm really stuck on is - can I add a row to one of the four spreadsheets and have Excel add the same row (task) to the other three. It should also duplicate the date from columns A and B, which will contain the task # and the task description. I'm guessing this is too tricky so I'm not holding my breath, but that's what would make this solution work for my client. Thanks again, Michele Otto Moehrbach wrote: Michele The adding/deleting of rows presents a problem if all the positions are on one sheet. By using only one sheet, the sheet would have to be protected to deny access to the locked cells. When the sheet is protected, the user cannot add/delete a row. If each position is on a separate sheet, the entire sheet would be unprotected. But if you need to deny access to some of the cells in that sheet, then we're back to the problem that the sheet has to be protected and therefore, a row cannot be added or deleted. This whole thing would be easier if adding/deleting a row was not a factor. The following gives you an idea of how what you would work. You could have some code that would fire upon opening of the file. That code would present an Input Box and ask the user for a password. If the password is valid, the code would proceed to lock/unlock the necessary sheets so that only the columns/cells/rows that go with the password presented would be unlocked. The code would then protect the sheet and allow the user to do his thing. Subsequent code that would fire upon closing or saving the file would unprotect the sheet, lock all the cells and protect the sheet. That way, no access would be permitted the next time the file is opened without a password. The code can be hidden with a password if you wish so as not to allow someone to view the passwords. I don't know if you are familiar with the security of Excel. Basically, there is none. Excel is not meant to be a secure platform. Someone with knowledge of Excel will be able to break any security scheme you come up with. What I said above will work fine with the casual Excel user. If you think this might be something that would work for you, send me a file with everything laid out as you want, and provide all the details regarding what cells/columns/rows you want locked/unlocked with what password. Also provide a password for hiding the code. If you wish to not provide me the passwords, I'll come up with some and you can change them later in the code. Fake your data as you wish because all I need is the layout. Send me that file and I'll work up something for you. My email address is . Remove the "nop" from this address. HTH Otto "mjones" wrote in message ups.com... Hi Otto, Thanks for responding. I really need to know how to handle adding/deleting rows in Exec so they will automatically add/delete in the Dept? worksheets. For example, adding a task between 2 and 3 (say 2a) in Exec needs to add the same task in the other 3 Dept? worksheets. I mentioned the passwords only because it would be okay to have all the postions in one spreadsheet, except for the fact that certain columns can only be editted by certain people and I don't think you can have five different passwords on one spreadsheet and I can't think of an alternative solution. I know how to limit access to specific sheets with passwords. I know how to limit access to specifc cells (with only one password). I hope you are able to help with this information. I really need to get this task done, I'm just trying to figure out the best way to get it done. Thanks again, Michele --------------- Otto Moehrbach wrote: Michele I'm not sure what you are asking help with. You are long on explanation (that's good) but short on questions. Are you asking how you can limit access to specific sheets, with passwords? Are you asking how you can limit access to specific cells? Are you asking how you can preclude anyone from adding or deleting a row? Or are you asking how you can accommodate someone adding or deleting a row? Please post back and clarify what you need help with. HTH Otto "mjones" wrote in message ups.com... Hi All, I'm hoping to get your opinion on the best approach to this task without having programming skills. I'm pretty good at Excel, use macros and read them some. Sometimes I copy macros from these news groups and do greatly appreciate the help. Here's my assignment. Its a responsibility matrix for all employees matching their job descriptions. Exec worksheet: A B C D 1 Task List VP1 VP2 VP3 2 Task 1 x x 3 Task 2 x 4 Task 3 x x x 5 Task 4 x Dept1 worksheet: A B C D 1 Task List VP1 A1 A2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x Dept2 worksheet: A B C D 1 Task List VP2 B1 B2 2 Task 1 x 3 Task 2 x x x 4 Task 3 x 5 Task 4 x Dept3 worksheet A B C D 1 Task List VP3 C1 C2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x x For Dept1, VP1's task in B2 would be =IF(Exec!$B2<"",Exec!$B2,"") and so on for B3, B4, etc. The point being that the President gets password/edit access to the Exec Worksheet and each of the three VP's get password/edit access to only their department and they cannot change their own task because it comes from the Exec worksheet. All other job positions like A1 or B2 have no relationship to other cells; only the VP's. This all seems well and good; the President and each VP have their own password protected worksheet. But, what do I do if someone wants to add or delete rows? Of course, this senario assumes that every worksheet has every task listed, even if that task is not being done by anyone on that worksheet, but I can't see any way around that. It's actually bigger than I presented here with 6 Executives, 10+VP Dept1, 2+VP Dept2, 5+VP Dept3, 12+VP Dept4 (35 positions). It looks like there will be about 600 tasks. Thank you, Michele |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Approach to Worksheet Synchronization
Hi Otto,
Thats very generous of you. There are so many great people on this news group! I'll prepared and send you the spreadsheet with the correct number of rows, columns, etc. with headings. It will be clear when you see it. Thanks again. You're a life saver. Michele --------------- Otto Moehrbach wrote: Michele The code to detect that a row has been added/deleted is fairly straight forward. Being able to discern which it was, added or deleted, is a bit tricky but it can be done. Doing the same in the other sheets is easy. Copying anything the Exec enters into any cell and pasting that value in the same cells in the other sheets is also easy. I'll come up with some code for you using the data you furnished in your post. One bit of information I need from you is this: Can you give me the maximum range of your data in columns and rows? By this I mean, is it 10 rows, 100 rows, 1000 rows, 10,000 rows, etc ? Five columns, 20, 200? I also need this: From what you say I gather that the only changes made will be made in the Exec sheets. Correct? If that is the case, then you need for the code to copy data from columns A:D, not just Columns A:B. Is that correct? You said that your actual task involves numerous Execs, and VPs, not just one Exec and 3 VPs. Do you want me to develop the code for just the One Exec and 3 VPs and you take it from there? Or do you need more than that? Or is each Exec and his VPs in a different file? Otto "mjones" wrote in message oups.com... Hi Otto, Thanks so much for the kind offer to help with the password code, but this doesn't need to be protected like Fort Knox. Standard Excel password protection is okay. The users are not sophisticated; I'm happy if they know how to add a row. Yes, you can add and delete rows on a protected sheet by checking the box when protecting it 'Allow all users of this worksheet to' and check insert rows. This works well. Investigating my situation more carefully, I think the thing I'm really stuck on is - can I add a row to one of the four spreadsheets and have Excel add the same row (task) to the other three. It should also duplicate the date from columns A and B, which will contain the task # and the task description. I'm guessing this is too tricky so I'm not holding my breath, but that's what would make this solution work for my client. Thanks again, Michele Otto Moehrbach wrote: Michele The adding/deleting of rows presents a problem if all the positions are on one sheet. By using only one sheet, the sheet would have to be protected to deny access to the locked cells. When the sheet is protected, the user cannot add/delete a row. If each position is on a separate sheet, the entire sheet would be unprotected. But if you need to deny access to some of the cells in that sheet, then we're back to the problem that the sheet has to be protected and therefore, a row cannot be added or deleted. This whole thing would be easier if adding/deleting a row was not a factor. The following gives you an idea of how what you would work. You could have some code that would fire upon opening of the file. That code would present an Input Box and ask the user for a password. If the password is valid, the code would proceed to lock/unlock the necessary sheets so that only the columns/cells/rows that go with the password presented would be unlocked. The code would then protect the sheet and allow the user to do his thing. Subsequent code that would fire upon closing or saving the file would unprotect the sheet, lock all the cells and protect the sheet. That way, no access would be permitted the next time the file is opened without a password. The code can be hidden with a password if you wish so as not to allow someone to view the passwords. I don't know if you are familiar with the security of Excel. Basically, there is none. Excel is not meant to be a secure platform. Someone with knowledge of Excel will be able to break any security scheme you come up with. What I said above will work fine with the casual Excel user. If you think this might be something that would work for you, send me a file with everything laid out as you want, and provide all the details regarding what cells/columns/rows you want locked/unlocked with what password. Also provide a password for hiding the code. If you wish to not provide me the passwords, I'll come up with some and you can change them later in the code. Fake your data as you wish because all I need is the layout. Send me that file and I'll work up something for you. My email address is . Remove the "nop" from this address. HTH Otto "mjones" wrote in message ups.com... Hi Otto, Thanks for responding. I really need to know how to handle adding/deleting rows in Exec so they will automatically add/delete in the Dept? worksheets. For example, adding a task between 2 and 3 (say 2a) in Exec needs to add the same task in the other 3 Dept? worksheets. I mentioned the passwords only because it would be okay to have all the postions in one spreadsheet, except for the fact that certain columns can only be editted by certain people and I don't think you can have five different passwords on one spreadsheet and I can't think of an alternative solution. I know how to limit access to specific sheets with passwords. I know how to limit access to specifc cells (with only one password). I hope you are able to help with this information. I really need to get this task done, I'm just trying to figure out the best way to get it done. Thanks again, Michele --------------- Otto Moehrbach wrote: Michele I'm not sure what you are asking help with. You are long on explanation (that's good) but short on questions. Are you asking how you can limit access to specific sheets, with passwords? Are you asking how you can limit access to specific cells? Are you asking how you can preclude anyone from adding or deleting a row? Or are you asking how you can accommodate someone adding or deleting a row? Please post back and clarify what you need help with. HTH Otto "mjones" wrote in message ups.com... Hi All, I'm hoping to get your opinion on the best approach to this task without having programming skills. I'm pretty good at Excel, use macros and read them some. Sometimes I copy macros from these news groups and do greatly appreciate the help. Here's my assignment. Its a responsibility matrix for all employees matching their job descriptions. Exec worksheet: A B C D 1 Task List VP1 VP2 VP3 2 Task 1 x x 3 Task 2 x 4 Task 3 x x x 5 Task 4 x Dept1 worksheet: A B C D 1 Task List VP1 A1 A2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x Dept2 worksheet: A B C D 1 Task List VP2 B1 B2 2 Task 1 x 3 Task 2 x x x 4 Task 3 x 5 Task 4 x Dept3 worksheet A B C D 1 Task List VP3 C1 C2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x x For Dept1, VP1's task in B2 would be =IF(Exec!$B2<"",Exec!$B2,"") and so on for B3, B4, etc. The point being that the President gets password/edit access to the Exec Worksheet and each of the three VP's get password/edit access to only their department and they cannot change their own task because it comes from the Exec worksheet. All other job positions like A1 or B2 have no relationship to other cells; only the VP's. This all seems well and good; the President and each VP have their own password protected worksheet. But, what do I do if someone wants to add or delete rows? Of course, this senario assumes that every worksheet has every task listed, even if that task is not being done by anyone on that worksheet, but I can't see any way around that. It's actually bigger than I presented here with 6 Executives, 10+VP Dept1, 2+VP Dept2, 5+VP Dept3, 12+VP Dept4 (35 positions). It looks like there will be about 600 tasks. Thank you, Michele |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best Approach to Worksheet Synchronization
Michele
I have not received anything from you. Otto "mjones" wrote in message oups.com... Hi Otto, Thats very generous of you. There are so many great people on this news group! I'll prepared and send you the spreadsheet with the correct number of rows, columns, etc. with headings. It will be clear when you see it. Thanks again. You're a life saver. Michele --------------- Otto Moehrbach wrote: Michele The code to detect that a row has been added/deleted is fairly straight forward. Being able to discern which it was, added or deleted, is a bit tricky but it can be done. Doing the same in the other sheets is easy. Copying anything the Exec enters into any cell and pasting that value in the same cells in the other sheets is also easy. I'll come up with some code for you using the data you furnished in your post. One bit of information I need from you is this: Can you give me the maximum range of your data in columns and rows? By this I mean, is it 10 rows, 100 rows, 1000 rows, 10,000 rows, etc ? Five columns, 20, 200? I also need this: From what you say I gather that the only changes made will be made in the Exec sheets. Correct? If that is the case, then you need for the code to copy data from columns A:D, not just Columns A:B. Is that correct? You said that your actual task involves numerous Execs, and VPs, not just one Exec and 3 VPs. Do you want me to develop the code for just the One Exec and 3 VPs and you take it from there? Or do you need more than that? Or is each Exec and his VPs in a different file? Otto "mjones" wrote in message oups.com... Hi Otto, Thanks so much for the kind offer to help with the password code, but this doesn't need to be protected like Fort Knox. Standard Excel password protection is okay. The users are not sophisticated; I'm happy if they know how to add a row. Yes, you can add and delete rows on a protected sheet by checking the box when protecting it 'Allow all users of this worksheet to' and check insert rows. This works well. Investigating my situation more carefully, I think the thing I'm really stuck on is - can I add a row to one of the four spreadsheets and have Excel add the same row (task) to the other three. It should also duplicate the date from columns A and B, which will contain the task # and the task description. I'm guessing this is too tricky so I'm not holding my breath, but that's what would make this solution work for my client. Thanks again, Michele Otto Moehrbach wrote: Michele The adding/deleting of rows presents a problem if all the positions are on one sheet. By using only one sheet, the sheet would have to be protected to deny access to the locked cells. When the sheet is protected, the user cannot add/delete a row. If each position is on a separate sheet, the entire sheet would be unprotected. But if you need to deny access to some of the cells in that sheet, then we're back to the problem that the sheet has to be protected and therefore, a row cannot be added or deleted. This whole thing would be easier if adding/deleting a row was not a factor. The following gives you an idea of how what you would work. You could have some code that would fire upon opening of the file. That code would present an Input Box and ask the user for a password. If the password is valid, the code would proceed to lock/unlock the necessary sheets so that only the columns/cells/rows that go with the password presented would be unlocked. The code would then protect the sheet and allow the user to do his thing. Subsequent code that would fire upon closing or saving the file would unprotect the sheet, lock all the cells and protect the sheet. That way, no access would be permitted the next time the file is opened without a password. The code can be hidden with a password if you wish so as not to allow someone to view the passwords. I don't know if you are familiar with the security of Excel. Basically, there is none. Excel is not meant to be a secure platform. Someone with knowledge of Excel will be able to break any security scheme you come up with. What I said above will work fine with the casual Excel user. If you think this might be something that would work for you, send me a file with everything laid out as you want, and provide all the details regarding what cells/columns/rows you want locked/unlocked with what password. Also provide a password for hiding the code. If you wish to not provide me the passwords, I'll come up with some and you can change them later in the code. Fake your data as you wish because all I need is the layout. Send me that file and I'll work up something for you. My email address is . Remove the "nop" from this address. HTH Otto "mjones" wrote in message ups.com... Hi Otto, Thanks for responding. I really need to know how to handle adding/deleting rows in Exec so they will automatically add/delete in the Dept? worksheets. For example, adding a task between 2 and 3 (say 2a) in Exec needs to add the same task in the other 3 Dept? worksheets. I mentioned the passwords only because it would be okay to have all the postions in one spreadsheet, except for the fact that certain columns can only be editted by certain people and I don't think you can have five different passwords on one spreadsheet and I can't think of an alternative solution. I know how to limit access to specific sheets with passwords. I know how to limit access to specifc cells (with only one password). I hope you are able to help with this information. I really need to get this task done, I'm just trying to figure out the best way to get it done. Thanks again, Michele --------------- Otto Moehrbach wrote: Michele I'm not sure what you are asking help with. You are long on explanation (that's good) but short on questions. Are you asking how you can limit access to specific sheets, with passwords? Are you asking how you can limit access to specific cells? Are you asking how you can preclude anyone from adding or deleting a row? Or are you asking how you can accommodate someone adding or deleting a row? Please post back and clarify what you need help with. HTH Otto "mjones" wrote in message ups.com... Hi All, I'm hoping to get your opinion on the best approach to this task without having programming skills. I'm pretty good at Excel, use macros and read them some. Sometimes I copy macros from these news groups and do greatly appreciate the help. Here's my assignment. Its a responsibility matrix for all employees matching their job descriptions. Exec worksheet: A B C D 1 Task List VP1 VP2 VP3 2 Task 1 x x 3 Task 2 x 4 Task 3 x x x 5 Task 4 x Dept1 worksheet: A B C D 1 Task List VP1 A1 A2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x Dept2 worksheet: A B C D 1 Task List VP2 B1 B2 2 Task 1 x 3 Task 2 x x x 4 Task 3 x 5 Task 4 x Dept3 worksheet A B C D 1 Task List VP3 C1 C2 2 Task 1 x 3 Task 2 x x 4 Task 3 x 5 Task 4 x x For Dept1, VP1's task in B2 would be =IF(Exec!$B2<"",Exec!$B2,"") and so on for B3, B4, etc. The point being that the President gets password/edit access to the Exec Worksheet and each of the three VP's get password/edit access to only their department and they cannot change their own task because it comes from the Exec worksheet. All other job positions like A1 or B2 have no relationship to other cells; only the VP's. This all seems well and good; the President and each VP have their own password protected worksheet. But, what do I do if someone wants to add or delete rows? Of course, this senario assumes that every worksheet has every task listed, even if that task is not being done by anyone on that worksheet, but I can't see any way around that. It's actually bigger than I presented here with 6 Executives, 10+VP Dept1, 2+VP Dept2, 5+VP Dept3, 12+VP Dept4 (35 positions). It looks like there will be about 600 tasks. Thank you, Michele |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet not showing up in VBE | Excel Worksheet Functions | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |