How to space increment formula

I am trying to fill out passes for appointments from an excel list I have made out without going one by one plugging them in, I tried using = to show the related area I need to show on the pass but I can't figure out a way to drag that formula down to fill in all of the spaces I need.

Here is a example of my list (I filled in the first 2 passes to show what I need) Book1.xlsx

I want to be able to use this formula if I ever change the way my appointments look too, so I'm looking for a universal one if possible.

You wouldn't want to have to copulate that much, would you? I mean, you'd be exhausted!

Joking apart, I can't see an obvious way of copying a formula down with the layout that you have chosen.

I am thinking of completing formula in first 2 blocks (in horizontal) K2:R9 then copy it downwards.

L3=INDEX($B$3:$B$20,COUNTIF($K$1:$P1,"Appointment")+1)

L4=INDEX($A$3:$A$20,COUNTIF($K$1:$P1,"Appointment")+1)

L5=INDEX($C$3:$C$20,COUNTIF($K$1:$P1,"Appointment")+1)

Complete necessary formatting work of block K2:M9. Copy K2:M9 to P2, change +1 to +2 (to get 2 blocks)

Copy K2:R9 to K12 (to get 4 blocks)

Copy K3:R19 to K22 (to get 8 blocks)

and so on...

VBA version

Please Login or Register  to view this content.