Excel brainbox needed.

[stevezodiac]

I have started an Excel spreadsheet to catalogue my vast collection (and thanks to the notes boxes can pinpoint all special issues and free gifts). Problem is with titles like Buster and Valiant which are not numbered. Is there a way of writing in a couple of dates in a column and then dragging down so that Excel automatically insets all the rest in seven day increments? We do it in work with batch numbers in incrememts of 20 but this is a bit more complex. I am using Excel 2007.

Wayne Rooney has just joined Twitter, Wazaroon08 is his name according to Stan Collymore on talksport.

[Al]

1: start with a blank sheet
2: in cell A1 put in a date (ie 01/01/2011)
3: in cell A2 put in a date 7 days later (ie 08/01/2011)
4: highlight the two cells
5: with the mouse pointer hover over the bottom corner of the second cell until a plus sign is displayed.
6: left click this corner and drag downwards.
7: the dates should now increment in 7 day intervals.

I'm writing this from memory as I watch Carry On Cleo.

Hope it helps.

Al

[stevezodiac]

That's exactly what i have been doing but the new dates that appear are the same two but a year later. Where did I put my sonic screwdriver?

[Niblet]

This is the way I do it...

1: start with a blank sheet
2: in cell A1 put in a date (ie 01/01/2011)
3: Click in cell A2, then type the equals sign, then click in cell A1 again, then type +7. Ensure that cell A2 now reads =A1+7, then hit enter
4: Contents of cell A2 should now show a date one week later than the date in A1
5: Click in cell A2 ONLY, grab the 'handle' at the bottom right of the cell (mouse pointer turns to smaller + sign), and drag down. Your column should fill with dates incremented by 7 days.

I don't know much about notes (I have only ever used them rarely), but personally I would have a separate column to identify free gift issues and such. That way by setting up an Autofilter, you can search your worksheet for free gift issues, or any other 'issue type' designations you may want to use.

[stevezodiac]

As my spreadsheet is up and running I can't start with a blank sheet would a blank column suffice?

I'll have a go anyway.

Thanks to you both.

Just tried it and getting gibberish.

Tried again on a blank page and the word "value" appears in the columns. Could you make it clear which fields i use in each of step 3? One i put the equals sign in the second box I can't type the +7 in the box above it as the +7 writes in the second box.

[Niblet]

I'd suggest trying it on a blank worksheet, then once you've got the hang of it there, you can apply it to your comic worksheet.

Let's try it a slightly simpler way, I may have confused you with the bit about clicking in the cell when entering your formula.

1: Enter the earliest date you want. Get the cell reference for the cell you just entered the date in. For simplicity, we'll assume it's A1.

2: Click in cell A2. Type =A1+7 in cell A2 (this is the formula for calculating the date one week later than that in cell A1) then hit the enter (aka return) key. Cell A2 should now show a date 7 days later than the date in A1.

Then do the drag down thing, i.e. click in cell A2 ONLY, grab the 'handle' at the bottom right of the cell (mouse pointer turns to smaller + sign), and drag down. Your column should fill with dates incremented by 7 days.

Make sure you have a valid date format in cell A1; for example if you enter 01/01/201 in A1 by mistake, you'll get a #VALUE error in cell A2.

Let us know if that works.

[stevezodiac]

Yep! I'm cooking with gas now. Ta.

[Niblet]

[grumpy old man]

I’d advise you to go with Al’s method using the Fill Series function because this will take into account leap years. Just writing a formula for (cell +7) won’t.

[ISPYSHHHGUY]

1: start with a blank sheet
2: in cell A1 put in a date (ie 01/01/2011)
3: in cell A2 put in a date 7 days later (ie 08/01/2011)
4: highlight the two cells
5: with the mouse pointer hover over the bottom corner of the second cell until a plus sign is displayed.
6: left click this corner and drag downwards.
7: the dates should now increment in 7 day intervals.

I'm writing this from memory as I watch Carry On Cleo.

Hope it helps.

Al

'Infamy, Infamy---they've all got it in for me!"

[Niblet]

I’d advise you to go with Al’s method using the Fill Series function because this will take into account leap years. Just writing a formula for (cell +7) won’t.

Eh?

Both methods give the same results, leap years or not. I use the +7 method as it will give you a date 7 days later than the first. By relying on one's mathematical abilities and calendar knowledge to calculate the second date, especially if it's across a month boundary, one could introduce an error that would propagate through the worksheet.