How Do I Override a Value In the Following Situation

[smw10c]

I don't know how to give a proper description to this problem. Attached is a workbook I created. The ask is when the client receives the workbook, they need to be able to enter in a hardcoded number in sheet "Client" cells B2:B3. When they enter a number into B2:B3, the values on sheet "Employee" cells B2:B3 need to update to these hardcoded values. The issue is that cells B2:B3 in "Client" rely on cells B2:B3 in "Employee." The client cannot touch the "Employee" sheet whatsoever. I was also told I am not allowed to create another sheet that will be hidden and it all needs to come from these two sheets. Any idea?

[Pete_UK]

Suppose you allow the client to put an over-riding value in cells H2 or H3 of the client sheet. Then you can use this formula in B2:

=IF(H2="",Employee!B2,H2)

so that if the override is empty then B2 will show the value from B2 in the Employee sheet, otherwise it will show the value from H2. The cells where the formulae exist (i.e. columns A and B), can be protected so that they can't be changed, and the cells H2 and H3 can be unprotected to allow a user to enter values in them.

Hope this helps.

Pete

[smw10c]

I appreciate it, however, the overriding value needs to be in B2. There is no way around it based on my supervisor's ask. Do you have an alternative to enable this to work?

[smw10c]

Also, how would this change the Employee sheet?

[Pete_UK]

You can't have both a formula and a value in a cell.

Pete

[6StringJazzer]

The only way to do this is with macros. Will your supervisor allow that? Incidentally, how do the values in Employee get entered in the first place?

[smw10c]

My supervisor will allow macros. The values in Employee get entered by the analysts. The point of the Client tab is to allow clients to enter in different scenarios.

[6StringJazzer]

This version will keep the two values in sync. If you update a value on either sheet, it will be updated on the other sheet.

[smw10c]

Thank you so much! This worked splendidly. Just to learn, is there a way to do this if the target addresses weren't the same? Like, if you index the unique key and it pulls the address somehow? Also, thanks for teaching me the workbook change event method! Looked through the code and it is very informative.