0

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.

Joe
  • 512
  • 1
  • 3
  • 16
  • This is very nearly a duplicate in terms of underlying question. http://stackoverflow.com/questions/30376437/. I give several approaches there which might be useful even though this is answered. – Byron Wall Jun 10 '15 at 23:40

1 Answers1

2

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

Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
  • This was it ! Thanks Dan. Exactly what I was looking for. I need to brush up on my excel skills. Should have done a little more research on the built-in functions. – Joe Jun 10 '15 at 23:10
  • 2
    You can get the same result using `INDEX` which is generally preferred over `INDIRECT`. `INDIRECT` is a volatile formula which means it is recalculated with every change to the `Workbook` even if the change is unrelated to the cells in the formula. Replacement: `=INDEX(B:B, 5+17*(ROW(C5)-5))`. Still a good answer. – Byron Wall Jun 10 '15 at 23:46