In this week's Office challenge, learn the solution to last week's Excel challenge on Data Validation and test your skills on a new Excel challenge.
I frequently see users press [Ctrl]+' to copy the contents of the previous cell to the current cell. It's a shortcut for entering the same data from row to row, as you enter data. But what if you want to copy the contents of a cell to a larger range, simultaneously? For instance, suppose the selected range is A1:A5. Furthermore, A1 is the active cell and you want to copy its contents to cells A2:A5—how would you accomplish this?Last week we asked…
How would you inhibit the entry of weekend dates without using VBA. In no time at all, Andrewsa posted the solution I had in mind:
- Select the cell or range in question.
- Choose Validation from the Data menu. In Excel 2007 and 2010, click the Data tab. Then, choose Data Validation from the Data Validation dropdown in the Data Tools group.
- On the Settings tab, choose Custom from the Allow dropdown.
- Then, enter the following formula: =AND(WEEKDAY($A2)<>1, WEEKDAY($A2)<>7). (You don't have to make the column reference absolute for this formula to work.)
- Click OK.
Jbenton offered a more efficient formula, =WEEKDAY(A1,2)<6. It certainly looks sound and after a bit of testing, I found it works just as well.
Thanks to everyone for another great challenge!