I have a spreadsheet which I need to make idiot proof; where only text can be entered in certain cells, and numbers in others.
The number bit is easy, but how do you make cells text only? I dont want to limit the amount characters or prevent repetition, further I cannot specify (in a list) what text must be entered.
I need it so any permutation of text can be entered.
Whihc Excel genius can answer this?!?
Cheers
2007-09-27 07:40:54 · 5 answers · asked by Anonymous in Computers & Internet ➔ Software
Not possible using normal Excel functions. You would need to program the worksheet using visual basic. There is no easy answer to explain to you how to do it through Visual Basic.
The only solution is like the posting before mine. This will allow you to quantify your data. If you don't want to quantify the data, then perhaps it would be easiest just to have better instructions specifying to the person(s) that numbers are not accepted in the given columns.
Sorry.
2007-09-27 08:52:05 · answer #1 · answered by Jackson D 3 · 0⤊ 2⤋
If you want what is entered to be treated as text or just treated as numbers, then format the cells as either text or numbers. Then go back and protect the sheet (Tools > Protection > Protect Sheet), checking the correct boxes to allow the cells to be selected but the format to not be changed.
If you can absolutely not have roman numerals at all then that would mean you'd have to write some VBA code to prevent that.
2007-09-27 10:04:59 · answer #2 · answered by devilishblueyes 7 · 0⤊ 1⤋
In some area of your worksheet write down the values you will settle for, enable's say E2 F2 • From documents Menu, opt for Validation • opt for "placing" tab • below the enable drop down menu, opt for "checklist" • below source: pass and decide the cells with the enable values • opt for enter Message in case you desire to teach a message to ask for documents • opt for blunders Alert tab to furnish the blunders message to exhibit • click ok attempt distinctive thoughts.
2016-10-20 03:56:59 · answer #3 · answered by furne 4 · 0⤊ 0⤋
to validate anything to any cell and make a list of that valid figure to be dropped, first select that cell, go to data then validation then in Allow slecet list then in source type refernce of that which u need to validate via drop down list, this can be done via writing that data in one culunm or one row of same work sheet or work book.. then press ok
2007-09-27 08:08:30 · answer #4 · answered by Sadzoum 2 · 0⤊ 2⤋
This is the nastiest thing I have ever seen. If you place it in a cell, it works everytime. I can't seem to get it into the data validation box. Good Luck.
=SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("97:122")),0))))+SUMPRODUCT(--(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("65:90")),0))))=LEN(A1)
2007-09-27 11:03:36 · answer #5 · answered by Frank Pytel 4 · 0⤊ 0⤋