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.
The AND operator combines two simple expressions, which check each entry for the weekday value. If the weekday value of the current input isn’t 1 or 7 (Sunday and Saturday, respectively) Excel accepts the input value. If you try to enter a weekend date (1 or 7) Excel displays a warning and rejects the entry. Use the Input Message and Error Alert tabs to customize the message and Excel’s response.
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!
