Best Approach to Worksheet Synchronization

[mjones]

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

 

[Otto Moehrbach]

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]

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]

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
(e-mail address removed). Remove the "nop" from this address. HTH Otto

 

[mjones]

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]

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, 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]

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]

Michele
I have not received anything from you. Otto