Copy and Drag formula!!

**quikrich3** · 06-16-2011, 01:21 PM

Hello,
I'm trying to copy numbers 1-5 across, then two zeros, then start with 1-5 again. it's corresponding to operating days during the week. On saturday and sunday i just need them to be blank or zeros, doesn't matter. So essentially i need it to be like this:

1 2 3 4 5 0 0 1 2 3 4 5 0 0

But i need it to be drag-able because i will do it for months and months. Is there a formula that could make that possible? I've tried searching around and can't seem to find anything i need. Any help would be greatly appreciated!!! Thanks for looking.

**tigeravatar** · 06-16-2011, 03:20 PM

quikrich3,

You can use the following:
=CHOOSE(IF(MOD(COLUMN()-3,7)=0,7,MOD(COLUMN()-3,7)),1,2,3,4,5,"","")

Note:
Be sure to change the number 3 in the COLUMN()-3 to be the one less than the column number of the cell that the formula is starting in. So in the example. the cell the formula is starting in is D1. D is the 4th letter, so column D is the 4th column. 4-1 = 3. If you were to start in column A, it would be COLUMN()-0 (or you could omit it: COLUMN() with no -#), starting in column B would be COLUMN()-1, etc.

Hope that helps,
~tigeravatar

**quikrich3** · 06-16-2011, 05:31 PM

Thanks tigeravatar!! Works great! It took a minute to understand the way you had the formula set up to know what it was doing, but i appreciate it!

**BarryTSL** · 06-17-2011, 12:41 PM

I know this is solved but the attachment shows a nice way to create a date and just add 1 so it can be incremented, the cell above shows the Day for that date and the cell below shows the day number slightly modified to return 0 for the week end. These cells can also be easily dragged across.