Formatting control and data validation in Excel
Control formatting by others
Q. I have a time recording spreadsheet that other people use. They’re always entering the time in the wrong format. What can I do to prevent this?
A. There are a couple of ways to tackle this. First, you can set up an IF statement to the right of the entry cell, like this: =IF(ISNUMBER(F5),”Valid”,”Please enter time in HH:MM PM format”). Properly formatted times are actually numbers, so this formula will deliver some help if they enter it incorrectly. Or, you can have them enter hours, minutes, and AM or PM in separate cells and recombine them in another cell with this formula =VALUE(A2&”:”&B2&” “&C2). Be sure to format the resulting cell as Time to have it appear correctly. You can hide it in the timesheet and just refer to it in work time calculations.
Is that data validation?
Q. There’s a dropdown list in a spreadsheet that needs to be changed. How do I add or remove items from it?
A. To test, select any one cell that shows the dropdown list. Then, on the Data tab, in the Data Tools group, choose the Data Validation button. If there is data validation active, it will show you the dialog box with the settings. Check the range in the Source Field. That’s what will need to be changed. If, instead of a cell range like $Z$1:$Z$4, you see a named range like =Colors, click up in the name box and select the named range from there. Insert new values in between the existing ones to keep the named range intact. You can always re-sort it to get the pick list in the right order. You can also redefine the extent of the Named range on the Formulas tab in the Defined Names group, by clicking the Name Manager button. Just change the ending cell of the range in the Refers to: field at the bottom of the Name Manager dialog box.
If you want to use a cell reference in the Source field of the Data Validation dialog box, it has to be on the same worksheet. A Named Range can be anywhere in the workbook.
If, when you attempt to navigate to the named range, it seems to go nowhere, the worksheet on which it resides may be hidden. To unhide, right-click on any worksheet tab and choose Unhide. It should present a list of hidden worksheets from which you can select.