First question.
Let’s say I wanted to build an Excel spread sheet with one column that has dates 1 week apart in each cell down the column (A1= 01/01/07 A2= 01/08/07 etc) is there any way to get Excel to populate that column automatically?
Second question.
I use Excel to track enrollment and responses for my classes. When I send out enrollment notices, I cut and paste the names and other data into Excel. When a confirmation comes in, I highlight the row and use a fill color to highlight it. Is there anyway to sort the page so that all the highlighted cells are first? Bonus question. If I get a spread sheet where three different fill colors have been used on a several hundred name sheet, can I sort them by fill color (all the greens, then yellows, then reds)?
I can answer the first question. Put the first date you want into the first cell, making sure that it’s formatted as a date (if it’s a recognisable date, that should happen automatically). Then select all the cells that you want to have dates in. From the top menu, select Edit - Fill - Series, put 7 in the Step value, and click on OK.
On the second question, I don’t think you can sort by colours. You’ll need to have another column with a code for the colour, then sort on that column.
With the cell selected, hover your mouse over the bottom-right corner of the cell, where the little box is, until your mouse cursor turns into a solid black + sign.
RIGHT CLICK and hold, then drag downwards for as far as you need your dates to go.
When you reach as far as you need to go, release the right mouse button, and a small context menu will pop up. Click on “Series…”
When the Series box appears, make sure the following values are selected: Columns; Date, Day. Then, in Step Value, enter the number 7.
Voila! You should have a column with dates a week apart.
A variation on mhendo’s solution: Enter a date in cell A1. Enter the next week’s date in A2. Then select both cells and drag the lower right corner of the selection box downwards. It will automatically populate the rest of the list based on the difference betwen the first two entries, without you having to manually specify the step value.
An alternative to manually color coding the cells is to add a column giving the enrollment status, and use conditional formatting to set the color in the desired cells on the row. That will allow you to sort and have the colors correct without having to write any VBA code. Check Excel Help for “conditional formatting.” Conditional formatting, once set up for a row, can be easily copied to all other rows using the Format Painter toolbar button. When setting up conditional formatting be mindful of using the $ if you really want a relative address.
I had asked the same question once about sorting by color, and here’s the answer I got, verbatim (credit goes to some unnamed Doper, since I can no longer seem to find the thread):
Okay, this is easy – don’t let the number of steps scare you off.
• In your Excel file, choose Tools->Macro->Visual Basic Editor.
• The VB editor will start.
• Now select Insert->Module.
• You’ll have a blank editing area, and the first line of VB code will already be there, and probably be “Option Explicit.”
• Below that text, paste (having copied) the following text:
Code:
Function GetMyColor(theRange As Range) As Long
GetMyColor = theRange.Interior.ColorIndex
End Function
• For colored text, it looks like changing “Interior” to “Font” would do the trick.
• Hit save and exit.
Now the “GetMyColor” function is just a normal Excel function. Here’s how you can sort by color:
• Say all of your color-coded things are in column A, and that column B is available.
• Just put =GetMyColor(A1) into B1. You can copy and paste or fill-down column B to put the same function in every other cell in the .
• Now you’ll have just a bunch of numbers representing the color codes of the cells.
• You can now sort on this column.