Help Required

[Akash]

In a Work Sheet there are tow sheets
On Sheet1 i have Five Columns
Employee Code | Employee Name | Model No | Offer Value | Market
Value |
On Sheet2 i have three Columns.

Model No | Offer Value | Market Value |
aaaaaaaa | 5000 | 10000 |
bbbbbbbb | 7000 | 11000 |
cccccccc | 8000 | 12000 |

What i have done is In Sheet2 I have colored the text in white color
and have also protected the sheet with password so that no one can
view the data in that sheet.
What I want in Sheet1 is Employee should enter their details like
Employee Code, Employee Name & Select Model No from the drop down box.
At Sheet1 In the Column of Model No i have added List Validation that
is connected to my Sheet2. So auto drop down is coming.
As soon as the Employee Selects the Model No from the drop down box
automatically the data is fetched and display. I am using vlookup
Function.
As i know that Employee would play with the Vlookup formula, i try to
protect the Sheet1 with the help of Password. But all in vain as the
List Validation gets deactivated. Its giving me a message stating "
The Cell or chart you are trying to change is protected and therefore
read only."

Can i have the remedy of the same.

Awaiting for your help.

Akash Maheshwari

 

[Patrick Molloy]

we do like to see an indication of the problem in the subject - HELP is
pretty useless.

If I unprotext columns A,B,C in sheet1 then protect the sheet then the
dropdown in C works fine and the VLOOKUP in D and E work fine too.

why don't you set the VISIBLE property of sheet2 to xxSheetVeryHidden

 

[Akash]

we do like to see an indication of the problem inthe subject - HELP is
pretty useless.

If I unprotext columns A,B,C in sheet1 then protect the sheet then the
dropdown in C works fine and the VLOOKUP in D and Ework fine too.

why don't you set the VISIBLE property of sheet2 to xxSheetVeryHidden

Hi Patrik,

Thanks for the suggestion.

I think u don't understand my query

I dont want to share the formula of Vlookup with the end users. Whats
is the way through which i can hide them. Is there any macro for
that...

Akash

 

[Patrick Molloy]

when you protect the sheet, you can also prevent the user from selecting
locked cells ...
Tools / Protect Sheet
then uncheck Allow users to 'Select locked cells'


remember though that protection is easily broken - Excel protection per se
is to stop users doing silly things rather that as explicit security.