How to auto-fill a simple excel formula down an entire column

Question

I have an excel file with health records above (immunizations to be exact) that looks like the attached image.

The format I am needing is to have the below record dates going all across the file in a single row. So, it would look like:

dtap dtap dtap dtap dtap tdap MMR MMR

So for the first student, Cox, every date needs to be filled across a single row, with a column for each immunization date.

The formula (series) I'm trying to use is:

dtap 1      dtap 2
=B5         =B6
=B22        =B23
=B39        =B40

Every disease in the data set skips by 17, and that is uniform across the entire file, so I think this is a simple/efficient way to do it. What I cannot figure out is how to fill that series all the way down the file. When I say =B5 for instance, it fills in the proper date, but I cannot get it to fill that "formula series", if you will, all the way down. The "Fill down" option seem to want to fill random dates in a series all the way down. Have tried it a few different way, I cannot get it to fill my increment by 17 formula down the entire file. I am using excel 2011 for MAC OSX if that helps.

Answer 1

This will do what you want:

=INDIRECT("B"&(17*(ROW(C5)-5))+5)

Indirect allows us to construct a string of text to represent an address. We are taking the row it's on and taking away 5 then multiplying the result by 17 then adding 5.

On row 5 this will be 5-5=0 * 17 = 0 + 5 = 5, on Row 6 this will be 6-5 = 1 * 17 = 17 + 5 = 22 and so on.

You can adjust the amount added for DTap2 etc