Daily posts of Excel tips…and other stuff
Archive for the ‘Data Validation’ Category.
It is possible to get a cell to display one thing, but store another.
Create a Custom Format by right-clicking the cell, select Format Cells…, then from the Number tab select Custom.
In the Type box use whatever text you want displayed, but be sure to enclose it in “double-quotes”.
I admit, this is a pretty dirty trick, [...]
You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.
What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, [...]
This post introduces several new items on the www.tushar-mehta.com website.
You can use Data Validation to force your users to enter text in upper case. Select the cell where you want to force upper case and select Validation from the Data menu. Assuming you selected cell A3, make your validation screens look like the pictures below. I don’t have any input message, [...]
I’ve been rewriting some user spreadsheets.
One thing I noticed after a while was that there isn’t an easy way to manage Conditional Formats.
As you may know, Conditional Formats use formulas too, so they can be very important to get right - especially if these cells are relied on to highlight errors.
Excel provides some limited ways [...]
In Conditional Data Validation, I described how to set up conditional formatting to alert you when your list validation was no longer valid. I also noticed that this is covered in Professional Excel Development (and much better, I might add) so if you haven’t ordered your copy yet, I suggest you do.
I recently had [...]
Data Validation is pretty useful for making sure that appropriate data is entered into a cell. If you’ve been reading this blog, you know I’m fond of DV despite its problems.
One of the things that I like best about DV is the In Cell Dropdown. That little dropdown arrow that goes away when [...]
