How can I format cells in Excel to accept numbers but return text related to those numbers in other columns?

Question

Here's the intent: Column 'A' will be for entry of a four digit pin number. Column 'B' will have these four digit numbers listed in order. Beside these numbers column 'C' will have a name assigned to each of these numbers. When the numbers are entered into cells in column 'A' I want it to search for the corresponding assigned name in column 'C' and return that name (text value) to the cell in column 'A' where the pin number was originally entered. (If there is a way to hide text in excel when entering the data such as a password entry into most websites being hidden with ******, I can accomplish the above by adding one extra colum and using the LOOKUP function!)

Answer 1

To make the PIN number show up as a wildcard string:



Select the desired cells (Column A) right click and select "Format Cells", Select the "Number" tab, and go to "Custom".



In the cell that now says "General" type:

"****";;;@

exactly as shown



As for the other part, I think your best idea is to use an additional column and use the LOOKUP function. Don't forget that you can always hide the extra columns by right clicking and selecting "Hide"



Hope this helps!



PS If none of this works, or if you have additional questions, email me at [e-mail], RE: Yahoo Answers - Excel. I'll do my best to help you out!

Answer 2

From what you're describing, it sounds like you're going to hide column B and C in your finished worksheet. I say this because I assume you want to keep the full list from the user view and only reveal results for single entries. Therefore I will suggest a different approach.



I recommend using two different worksheets within the same workbook.



Sheet 1 : Contains data entry cell and lookup formula.

Sheet 2 : Contains complete list of pin numbers and names



If you do this then you can show the result of lookup in a different cell than the one used for the pin number entry. Obviously you would hide sheet two and protect the workbook with a password.



However, the above does not solve the problem whereby you want the result of lookup to appear in same cell as the data entry. You can do this using a macro.



In sheet 1, you have the lookup in a cell that is not the same as the cell where user keyed value for lookup. I would record a macro that copies value from lookup cell and then paste VALUE into data entry cell. Assign a button to the macro. Then hide lookup cell and protect sheet.



You should be Okay with these approaches as long as my assumptions are accurate. I don't understand the benefit of displaying a result in same cell as entry was. Maybe you could explain more about why you described it that way and then we could come up with yet another approach?



Hope this helps,

Kind regards,

QwertyKPH @ Yahoo!

Answer 3

This answer is for Microsoft workplace 2002, yet must be appropriate to maximum variations of Excel. good click on the cellular and choose format Cells... go with the decision Tab. lower than classification, go with decision. make confident that Decimal places is keen to 2.

Answer 4

Change the text color to white so you can not see that text, but you can still utilize it.....just a simple way, I'm sure there is a better way.