Hide Formula and cannot alter

[johnph]

I have two cells,

CELL 1 - Contains a dropdown that has CAT, DOG, RAT
CELL 2 - CONTAINS a VLOOKUP that autopopulates based on CELL 1

For example, If I select CAT from CELL 1 on the dropdown it returns "C"
If I select DOG from CELL 1 on the dropdown it returns "D"
If I select RAT from CELL 1 on the dropdown it returns "R"

Is there anyway I can hide the VLOOKUP formula in CELL 2 and also "protect" the formula so that the user cannot delete it?
Also, I want to allow the user to INPUT whatever they want in CELL 1 and CELL 2 and not use the dropdown, however when they do use the dropdown then the VLOOKUP formula should still work.

Is this possible with EXCEL? or is it one of its limitations? Any method on how to do this would be great.

[Ace_XL]

In Cell2 use
=IFERROR(your_Vlookup_formula,"")

Select cell2
Go to format cells--Protection--Check the 'Locked' and 'Hidden' checkboxes
Protect the worksheet with a password

[johnph]

giving this a shot now

---------- Post added at 09:51 AM ---------- Previous post was at 09:42 AM ----------

Okay, it works however now they cannot edit CELL 2. Is there a way to allow them to edit CELL 2 and still acheive this?

---------- Post added at 09:52 AM ---------- Previous post was at 09:51 AM ----------

Or is this a limitation on excel?

[Ace_XL]

Oops..forgot that bit!

Enusre cell format of Cell1 --protection--both boxes (locked & Hidden) are unchecked!

[johnph]

Yeah, CELL 1 isn't protected and the users are allowed to edit but the users can't edit CELL 2.
Can they edit CELL 2 without messing up the VLOOKUP?

[Ace_XL]

Is there anyway I can hide the VLOOKUP formula in CELL 2 and also "protect" the formula so that the user cannot delete it?

If Cell2 is locked and the worksheet is protected, cell2 cannot be edited at all! Isn't that your requirement?

Can they edit CELL 2 without messing up the VLOOKUP?

Nope. Once its open to editing, they can edit/mess up the vlookup too!

[johnph]

hmm, ok. one more question ACE, is there such thing as an IF clause that can look for a value in a array? For example

IF(A2='LOOK FOR VALUES in RANGE', "X", "not in array")

[Ace_XL]

Tweak this

In Cell2 use
=IFERROR(your_Vlookup_formula,"")

to
=IF(ISERROR(your_Vlookup_formula,"not in array","X")

Does this help?

[johnph]

Sorry, I think I misstyped what I was asking lol.

This is unrelated to the original post, If I had an array, lets say:

A1 B1 C1
CAT DOG RAT
BLUE RED YELLOW


My array is A2:C3 with (CAT,DOG,RAT,BLUE,RED,YELLOW)

My question is... is there an if clause that can detect this array?
Maybe something like: IF(A2='LOOK FOR VALUES in RANGE', "X", "not in array")