Reference every 4th row from a master list of modifiable length

[RiotLoadTime]

I'm creating an excel database for a clinical study on patients with
migraine. Each patients will treat 4 attacks (episodes). This
presents a bit of a problem, since there's some information I'm
collecting that's specific to the patient (demographic information like
sex, height, etc.), and some that is specific to each attack (like the
pain level of that attack, the duration of that attack, etc.). To
account for this, I decided to give each patient four rows--one for
each attack--and I put their deomographic information in the second
row, leaving the other three blank in those fields (see below).

However, this lead to complications when I was programming the
statistics, so I ended up making a new worksheet for each attack
(worksheets "Attack 1", "Attack 2", "Attack 3", and "Attack 4", in
addition to the original "All Data" worksheet). These worksheets have
all the attack-specific information for each patient. For example, the
attack 1 worksheet would be the first row of each patient (i.e. every
4th row, starting with row 2) from column 5 on (i.e. skipping the
demographic stuff).

I don't know how many patients there will be, so I'd like for the user
to be able to add new patients (rows) to the All Data worksheet and
have it automatically update the other worksheets. Is there a way to
do this other than manually going to every single row in each Attack
worksheet and typing "=" and the referenced cell number? Also, if I
have to manually do this, I'll just have to pick a random number of
patients to do it for (i.e. some large number that I don't they'll go
over) instead of catering to the specific number of patients in the
"All Data" worksheet.

(Note: In the example worksheet below, a cell with just "----"
indicates a blank cell. The "--" are just place holders for me to try
to line things up)

| Sex | Height | Age | Weight | Attack | Pain | Duration | Etc . . . .

 

[Miguel Zapico]

One solution may be using the function INDIRECT instead of a direct
reference. The layout I would do is the following:
1.- In one column (say A) put the first row number of the type of attack you
want to track (say value 2 in cell A2), and fill the column with formulas to
add 4 (=A2+4 in cell A3 and drill down)
2.- For each of the fields you want to replicate, use a formula like:
=INDIRECT("All Data!E" & $A2)
This will refer to the value in cell E2 on the "Data" sheet (probably the
Attack number). Fill a whole row with all the fields you want.
3.- Drill down the formulas to the extent you want. Changing the value of
the cell A2 will switch the whole worksheet to a different attack type, so
you can make four copies of the worksheet and change that value for each of
them.

This is not a fully automated solution, as you still need to copy and paste
formulas, If you want that you must use VBA.

Hope this helps,
Miguel.

 

[SimonCC]

I'm assuming for each sheet you'll start from A1 with headers Attack, Pain,
Duration, etc. So the real data starts from A2 and so on. Offset formula is
probably the best way to go. So in cell A2 of the Attack1 sheet, try:
=OFFSET('All Data'!E$2,(ROW()-2)*4,0)
Then just copy the formula as far to the right and as far down as you need.

In cell A2 of sheets Attack2, Attack3, Attack4 respectively, use:
=OFFSET('All Data'!E$2,(ROW()-2)*4+1,0)
=OFFSET('All Data'!E$2,(ROW()-2)*4+2,0)
=OFFSET('All Data'!E$2,(ROW()-2)*4+3,0)
Copy the formula to the right and down the same way.

-Simon