I have a file that is shared by many user. The file contains certain line
items.. Let's say Col A Row 1 has the amount of $100, A2 has $150 and A3 has
$200. Col B1 belongs to user X,C1 belongs to user Y and D1 for user Z. I want
user X,Y and Z to agreed to claim the amount of Col A. They must enter a
secret code. This secret code should identify the user name or intial.

Is there any formula can do this? or anything that I can work around to it?

I appreciate your help to resolve this. Please advise.

Thank you.

Dinesh

I think we need a little more information here. Exactly where are they going
to enter the secret codes? Are those going to go into B1, C1 and D1?

Here is one possible way to do it with some "helper" cells:
Step 1, important to keep the secret codes hidden:
Choose all the cells on the sheet - you can do this quickly by clicking the
blank gray square above the #1 for Row 1 and to the left of the A column
header. Then choose Format | Cells from the menu bar and go to the
[Protection] tab and put a check in the box next to the word Hidden. Both
boxes should have checks next to them then. Close the dialog box.

Next, choose cells B1, C1 and D1 and then once again choose
Format | Cells but this time on the [Protection] tab, clear the checkbox
next to the word Locked. Don't close the box yet, go to the [Number] tab,
scroll all the way to the bottom of the list on the left and choose Custom
format. Then in the text entry area labeled Type: simply enter 3 semicolons
( ;;; ) no spaces or anything between them and no parenthesis around them as
I showed, just 3 semi-colons. Click OK and close the dialog box.

Lets presume that you want them to type their code into the cells, B1, C1 or
D1.

Now in cell B2 (right below B1), enter a formula similar to this:
=IF(B1="code4userX","CLAIMED","")
do something similar in C2 and D2, but referencing the appropriate cells for
each user. Enter the actual Secret code you've provided them where I've
typed in "code4userX" in the formula.

When you are ready for them to use the workbook/sheet, you need to protect
the sheet to keep prying eyes from seeing what is going on. Choose Tools |
Protection | Protect Sheet and give the worksheet a strong password that only
you know. Write that down so you don't forget it. Try it out by entering
the secret words into cells B1, C1 and D1 and watch what happens.

What SHOULD happen - you'll never see what you've typed into cells B1, C1
and D1 except while it is being typed in. That's what the ;;; trick did for
you. By setting the Locked and Hidden attributes AND then protecting the
worksheet, you've prevented anyone from looking at the formula in B2, C2 and
D2 and finding out what the other passwords/secret codes are.

Now a word of warning. Passwords assigned to both worksheets and workbooks
are fairly easy to crack. There are a lot of tools available on the internet
to do exactly that - they may not give you the exact password used, but
they'll give you one that will unlock the workbook/sheet. So if this is a
seriously for-real deal, you may want to look elsewhere for your security
needs.

A better way would be to do this in code and lock down the VB code project
with a password - those are harder to crack than workbook/sheet passwords.

I hope this at least gives you a start in doing what you need to get done.




"Dinesh" wrote:

I have a file that is shared by many user. The file contains certain line
items.. Let's say Col A Row 1 has the amount of $100, A2 has $150 and A3 has
$200. Col B1 belongs to user X,C1 belongs to user Y and D1 for user Z. I want
user X,Y and Z to agreed to claim the amount of Col A. They must enter a
secret code. This secret code should identify the user name or intial.

Is there any formula can do this? or anything that I can work around to it?

I appreciate your help to resolve this. Please advise.

Thank you.

Dinesh

This is the type of answer I was looking for. Thanks you so much. The
solution that you provided is perfect.

"JLatham" wrote:

I think we need a little more information here. Exactly where are they going
to enter the secret codes? Are those going to go into B1, C1 and D1?

Here is one possible way to do it with some "helper" cells:
Step 1, important to keep the secret codes hidden:
Choose all the cells on the sheet - you can do this quickly by clicking the
blank gray square above the #1 for Row 1 and to the left of the A column
header. Then choose Format | Cells from the menu bar and go to the
[Protection] tab and put a check in the box next to the word Hidden. Both
boxes should have checks next to them then. Close the dialog box.

Next, choose cells B1, C1 and D1 and then once again choose
Format | Cells but this time on the [Protection] tab, clear the checkbox
next to the word Locked. Don't close the box yet, go to the [Number] tab,
scroll all the way to the bottom of the list on the left and choose Custom
format. Then in the text entry area labeled Type: simply enter 3 semicolons
( ;;; ) no spaces or anything between them and no parenthesis around them as
I showed, just 3 semi-colons. Click OK and close the dialog box.

Lets presume that you want them to type their code into the cells, B1, C1 or
D1.

Now in cell B2 (right below B1), enter a formula similar to this:
=IF(B1="code4userX","CLAIMED","")
do something similar in C2 and D2, but referencing the appropriate cells for
each user. Enter the actual Secret code you've provided them where I've
typed in "code4userX" in the formula.

When you are ready for them to use the workbook/sheet, you need to protect
the sheet to keep prying eyes from seeing what is going on. Choose Tools |
Protection | Protect Sheet and give the worksheet a strong password that only
you know. Write that down so you don't forget it. Try it out by entering
the secret words into cells B1, C1 and D1 and watch what happens.

What SHOULD happen - you'll never see what you've typed into cells B1, C1
and D1 except while it is being typed in. That's what the ;;; trick did for
you. By setting the Locked and Hidden attributes AND then protecting the
worksheet, you've prevented anyone from looking at the formula in B2, C2 and
D2 and finding out what the other passwords/secret codes are.

Now a word of warning. Passwords assigned to both worksheets and workbooks
are fairly easy to crack. There are a lot of tools available on the internet
to do exactly that - they may not give you the exact password used, but
they'll give you one that will unlock the workbook/sheet. So if this is a
seriously for-real deal, you may want to look elsewhere for your security
needs.

A better way would be to do this in code and lock down the VB code project
with a password - those are harder to crack than workbook/sheet passwords.

I hope this at least gives you a start in doing what you need to get done.




"Dinesh" wrote:

I have a file that is shared by many user. The file contains certain line
items.. Let's say Col A Row 1 has the amount of $100, A2 has $150 and A3 has
$200. Col B1 belongs to user X,C1 belongs to user Y and D1 for user Z. I want
user X,Y and Z to agreed to claim the amount of Col A. They must enter a
secret code. This secret code should identify the user name or intial.

Is there any formula can do this? or anything that I can work around to it?

I appreciate your help to resolve this. Please advise.

Thank you.

Dinesh

JLathan,

Is there anyway to lock/unlock the cell once user accept or change the mind?


"Dinesh" wrote:

This is the type of answer I was looking for. Thanks you so much. The
solution that you provided is perfect.

"JLatham" wrote:

I think we need a little more information here. Exactly where are they going
to enter the secret codes? Are those going to go into B1, C1 and D1?

Here is one possible way to do it with some "helper" cells:
Step 1, important to keep the secret codes hidden:
Choose all the cells on the sheet - you can do this quickly by clicking the
blank gray square above the #1 for Row 1 and to the left of the A column
header. Then choose Format | Cells from the menu bar and go to the
[Protection] tab and put a check in the box next to the word Hidden. Both
boxes should have checks next to them then. Close the dialog box.

Next, choose cells B1, C1 and D1 and then once again choose
Format | Cells but this time on the [Protection] tab, clear the checkbox
next to the word Locked. Don't close the box yet, go to the [Number] tab,
scroll all the way to the bottom of the list on the left and choose Custom
format. Then in the text entry area labeled Type: simply enter 3 semicolons
( ;;; ) no spaces or anything between them and no parenthesis around them as
I showed, just 3 semi-colons. Click OK and close the dialog box.

Lets presume that you want them to type their code into the cells, B1, C1 or
D1.

Now in cell B2 (right below B1), enter a formula similar to this:
=IF(B1="code4userX","CLAIMED","")
do something similar in C2 and D2, but referencing the appropriate cells for
each user. Enter the actual Secret code you've provided them where I've
typed in "code4userX" in the formula.

When you are ready for them to use the workbook/sheet, you need to protect
the sheet to keep prying eyes from seeing what is going on. Choose Tools |
Protection | Protect Sheet and give the worksheet a strong password that only
you know. Write that down so you don't forget it. Try it out by entering
the secret words into cells B1, C1 and D1 and watch what happens.

What SHOULD happen - you'll never see what you've typed into cells B1, C1
and D1 except while it is being typed in. That's what the ;;; trick did for
you. By setting the Locked and Hidden attributes AND then protecting the
worksheet, you've prevented anyone from looking at the formula in B2, C2 and
D2 and finding out what the other passwords/secret codes are.

Now a word of warning. Passwords assigned to both worksheets and workbooks
are fairly easy to crack. There are a lot of tools available on the internet
to do exactly that - they may not give you the exact password used, but
they'll give you one that will unlock the workbook/sheet. So if this is a
seriously for-real deal, you may want to look elsewhere for your security
needs.

A better way would be to do this in code and lock down the VB code project
with a password - those are harder to crack than workbook/sheet passwords.

I hope this at least gives you a start in doing what you need to get done.




"Dinesh" wrote:

I have a file that is shared by many user. The file contains certain line
items.. Let's say Col A Row 1 has the amount of $100, A2 has $150 and A3 has
$200. Col B1 belongs to user X,C1 belongs to user Y and D1 for user Z. I want
user X,Y and Z to agreed to claim the amount of Col A. They must enter a
secret code. This secret code should identify the user name or intial.

Is there any formula can do this? or anything that I can work around to it?

I appreciate your help to resolve this. Please advise.

Thank you.

Dinesh