Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 4th row from a master list of modifiable length
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 . . . . |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 4th row from a master list of modifiable length
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. "RiotLoadTime" wrote: 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 . . . . . |------|---------|------|---------|----1----|--S--|----19----|--------------- |M----|---176--|-19--|---178--|----2----|--M--|-----35---|--------------- |------|---------|------|---------|----3----|--Mo-|---29----|--------------- |------|---------|------|---------|----4----|--S--|----18----|--------------- |------|---------|------|---------|----1----|--S--|----19----|--------------- |F-----|--158--|--25--|---138--|----2----|--M--|----35----|--------------- |------|---------|------|---------|----3----|--Mo-|---29----|--------------- |------|---------|------|---------|----4----|---S--|---18----|--------------- -- RiotLoadTime ------------------------------------------------------------------------ RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956 View this thread: http://www.excelforum.com/showthread...hreadid=567423 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference every 4th row from a master list of modifiable length
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 "RiotLoadTime" wrote: 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 . . . . . |------|---------|------|---------|----1----|--S--|----19----|--------------- |M----|---176--|-19--|---178--|----2----|--M--|-----35---|--------------- |------|---------|------|---------|----3----|--Mo-|---29----|--------------- |------|---------|------|---------|----4----|--S--|----18----|--------------- |------|---------|------|---------|----1----|--S--|----19----|--------------- |F-----|--158--|--25--|---138--|----2----|--M--|----35----|--------------- |------|---------|------|---------|----3----|--Mo-|---29----|--------------- |------|---------|------|---------|----4----|---S--|---18----|--------------- -- RiotLoadTime ------------------------------------------------------------------------ RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956 View this thread: http://www.excelforum.com/showthread...hreadid=567423 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals in a list | Excel Discussion (Misc queries) | |||
How to Change List Based on Value Chosen in Another List | Excel Worksheet Functions | |||
Data Validation List Length | Excel Worksheet Functions | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
list box length | Excel Worksheet Functions |