Excel Problem

[niwi7]

I have come across a worksheet in excel, where if I type something in and it is the desired value, nothing happens, but if it is wrong an asterik (*) appears next to the cell. How do I reveal the value that the cell "knows", to give me this asterik or not? When I click the cell it appears blank but there must be some sort of embedded function in it to give me this * or not...how do I locate it?

For example if the desired value for the cell is 10B and I type in 10B nothing happens, but if I type in any other value (for example 78), an asterik appears. How do I find out the desired value that it is looking for?

Thanks for your time and help!

 

[mayest]

Never mind.

 

[niwi7]

Thanks for the quick response. I do not see a rule under data validation, is there any way to reveal the VBA rule, or is that a long and complicated process?

 

[mayest]

Ha, niwi7, you're too quick. I just deleted my response. I had changed Data Validation to Conditional Formatting, but I ultimately deleted it because I couldn't see how either could add an asterisk. A VBA solution would be pretty simple. I'd use the Worksheet_SelectionChange event to scan the data for invalid entries and place an asterisk in the cell next to it.

You can check for that solution by launching the VBA editor with Alt-F11. Look in the Project window for your workbook's name. Double click each sheet to see the code, if any. The code may also be located in the ThisWorkbook module or maybe another module has been added.

 

[edcarman]

Is it a protected worksheet?

If so it is possible to lock and hide the formulas in the worksheet. If it is protected, I believe you're out of luck unless you know the password.

For reference, it could be implemented like this:

Say I want cell B1 to display a blank when 'Ed' is entered in A1 and an * if anything else is entered, I put the following formula in B1:

=IF(A1="Ed","","*")

Under B1 protection settings I would set the cell as hidden. I would then password protect the whole worksheet.