Using: Microsoft Excel 2010
I have run into a pesky little problem when charting a set of data that I need to sort multiple times. I have a set of clinical patient data, that resembles the data below. I need to chart Test 1 Results, but separate charts for different groups of patients. For example, I need one chart with Test 1 Results for Type of Disease= 1 patients, one for Type of Disease= 2 patients, and so on. I have a lot more data than what is below, and I need to group the data like this (by sorting it) in MANY different ways. Obviously this means copying the table over and over and over again, and re-sorting it. What I need to know is, is there a way to use only one set of data to create all of these charts, so that when I sort the data in the table it doesn't change the previously created charts? I understand that the data is graphed based on a range, so isn't it possible to force Excel 2010 to reference a specific value instead of generically referencing a cell where the value of that cell could potentially change? I've searched all over for answers to this, and have had no luck thus far.
[TABLE="width: 733"]
[tr][td]Study ID
[/td][td]Gender
[/td][td]Type of Disease
[/td][td]Number of Genes
[/td][td]Date of test
[/td][td]Visit Type
[/td][td]Visit Number
[/td][td]Test 1 Results
[/td][td][/td][/tr][tr][td]3
[/td][td]Male
[/td][td]3
[/td][td]2
[/td][td]7/28/2000
[/td][td]S
[/td][td]1
[/td][td]13
[/td][td][/td][/tr][tr][td]3
[/td][td]Male
[/td][td]1
[/td][td]2
[/td][td]8/28/2000
[/td][td]S
[/td][td]2
[/td][td]14
[/td][td][/td][/tr][tr][td]4
[/td][td]Female
[/td][td]2
[/td][td]3
[/td][td]9/1/2000
[/td][td]S
[/td][td]1
[/td][td]15
[/td][td][/td][/tr][tr][td]4
[/td][td]Female
[/td][td]1
[/td][td]3
[/td][td]8/17/2001
[/td][td]S
[/td][td]3
[/td][td]16
[/td][td][/td][/tr][tr][td]7
[/td][td]Female
[/td][td]2
[/td][td]2
[/td][td]11/17/2000
[/td][td]S
[/td][td]1
[/td][td]17
[/td][td][/td][/tr][tr][td]7
[/td][td]Female
[/td][td]2
[/td][td]2
[/td][td]8/10/2001
[/td][td]S
[/td][td]2
[/td][td]18
[/td][td][/td][/tr][tr][td]7
[/td][td]Female
[/td][td]2
[/td][td]4
[/td][td]4/16/2002
[/td][td]S
[/td][td]3
[/td][td]19
[/td][td][/td][/tr][tr][td]7
[/td][td]Female
[/td][td]1
[/td][td]2
[/td][td]11/12/2002
[/td][td]S
[/td][td]4
[/td][td]20
[/td][td][/td][/tr][tr][td]7
[/td][td]Female
[/td][td]3
[/td][td]2
[/td][td]9/19/2003
[/td][td]S
[/td][td]6
[/td][td]21
[/td][td][/td][/tr][tr][td]7
[/td][td]Female
[/td][td]3
[/td][td]2
[/td][td]12/16/2003
[/td][td]V
[/td][td]1
[/td][td]22
[/td][td][/td][/tr][tr][td]7
[/td][td]Female
[/td][td]2
[/td][td]2
[/td][td]5/26/2004
[/td][td]V
[/td][td]1
[/td][td]23
[/td][td][/td][/tr][tr][td]7
[/td][td]Female
[/td][td]1
[/td][td]2
[/td][td]3/16/2004
[/td][td]V
[/td][td]2
[/td][td]24
[/td][td][/td][/tr][tr][td]9
[/td][td]Female
[/td][td]3
[/td][td]2
[/td][td]2/2/2001
[/td][td]S
[/td][td]1
[/td][td]25
[/td][td][/td][/tr][tr][td]9
[/td][td]Female
[/td][td]2
[/td][td]2
[/td][td]4/17/2001
[/td][td]S
[/td][td]2
[/td][td]26
[/td][td][/td][/tr][tr][td]10
[/td][td]Female
[/td][td]1
[/td][td]4
[/td][td]4/13/2001
[/td][td]S
[/td][td]1
[/td][td]27
[/td][td][/td][/tr][tr][td]10
[/td][td]Female
[/td][td]1
[/td][td]4
[/td][td]6/13/2001
[/td][td]S
[/td][td]2
[/td][td]28
[/td][td][/td][/tr][tr][td]15
[/td][td]Male
[/td][td]2
[/td][td]2
[/td][td]8/1/2001
[/td][td]S
[/td][td]1
[/td][td]29
[/td][td][/td][/tr][tr][td]15
[/td][td]Male
[/td][td]3
[/td][td]2
[/td][td]9/25/2001
[/td][td]S
[/td][td]2
[/td][td]30
[/td][td][/td][/tr][tr][td]16
[/td][td]Male
[/td][td]2
[/td][td]2
[/td][td]12/19/2001
[/td][td]S
[/td][td]1
[/td][td]31
[/td][td][/td][/tr][tr][td]16
[/td][td]Male
[/td][td]1
[/td][td]3
[/td][td]7/18/2002
[/td][td]S
[/td][td]2
[/td][td]32
[/td][td][/td][/tr][tr][td]16
[/td][td]Male
[/td][td]1
[/td][td]3
[/td][td]6/12/2003
[/td][td]S
[/td][td]3
[/td][td]33
[/td][td][/td][/tr][tr][td]20
[/td][td]Male
[/td][td]1
[/td][td]2
[/td][td]2/14/2002
[/td][td]S
[/td][td]1
[/td][td]34
[/td][td][/td][/tr][tr][td]20
[/td][td]Male
[/td][td]2
[/td][td]2
[/td][td]3/8/2002
[/td][td]S
[/td][td]2
[/td][td]35
[/td][td][/td][/tr][tr][td]26
[/td][td]Female
[/td][td]2
[/td][td]3
[/td][td]4/4/2002
[/td][td]S
[/td][td]1
[/td][td]36
[/td][td][/td][/tr][tr][td]28
[/td][td]Male
[/td][td]1
[/td][td]2
[/td][td]4/23/2002
[/td][td]S
[/td][td]1
[/td][td]37
[/td][td][/td][/tr][tr][td]28
[/td][td]Male
[/td][td]3
[/td][td]2
[/td][td]9/23/2003
[/td][td]S
[/td][td]2
[/td][td]38
[/td][td][/td][/tr]
[/TABLE]