Ok, another question.

[Glyder]

This one has a few of us stumped in the office. I am developing this for a nurse who doesn't have time to do much more than enter data. I don't want her to have to make a chart every time she enters data.

I have a range of data from =Sheet1!$A$3:$K$6 that I am using in a chart. Row 3 is attribute data like Date, Pain, Tiredness, Nausea, etc. The first column is the date, which I want to use as the X-axis charted over time.

I specify the range for the chart, and that works fine. The problem is when I want to extend the range to the next row. The date (which i have as Text because she wont be adding in every day, and leaving it as date fills in each missing date) changes to just a numerical number (1, 2, 3, 4, etc.). This then screws up the chart.

I need to use this as a template, as the nurse has multiple patients. Basically the question is, How do I extend the range so it automatically updates the chart?

 

[PaddyD]

In outline, the way t do this is to have the charts refer to dynamic named ranges rather than specific cell references. The named ranges ca be set up to grow as more data is entered - charts based on the named ranges update accordingly. The function at the heart of this procedure is offset(). Check it out in the help files. Tushar has an example of the method here:

http://www.tushar-mehta.com

(Dynamic Chart Tutorial).

paddy

 

[Glyder]

Thanks Paddy, I am looking at it now. Sigh, confusing!!! That link you gave me sure didn't help much, the tutorial was sure sparse on information.

I understand what your meaning though, but where do I enter the Offset(), and what would I put for it to grow? I am just not sure. Do I put it in the graph where I specify the data ranges?

 

[Glyder]

Ok, after reading the help, and trying to figure things out, I understand that I have to add a name, ie the XValues and the YValues as indicated on the website Paddy recommended. Unfortunately I cannot do this? I dont have enough knowledge of Excel to input this info. Here is what the tutorial says.

Create the names

'Basic Range'!XValues =OFFSET('Basic Range'!YValues,0,-1)
'Basic Range'!YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-1,1)

where 'Basic Range' is the name of the worksheet containing this example. The '-1' in the definition of YValues adjusts for the cell containing the word 'Price' (cell B4). Also, one must be careful and ensure that nothing else is entered in any cell in column B -- at least not without adjusting the formula above.

The next and final step is to create a chart with the formula
=SERIES(,'Basic Range'!XValues,'Basic Range'!YValues,1)

____________________________________________

Ok, I mostly understand that, but HOW do I create the names? Each time I do, it wont work. My worksheet is named Test. I have 11 columns, so I know for the second Offset= formula will be something like this...

'Test'!YValues =OFFSET('Test'!$B$5,0,0,COUNTA('Test'!$B:$K)-1,1)

HELP!!!

 

[Glyder]

bump

 

[Alriemer]

Glyder,

Go to Insert > Name > Define. Type a name for your range, like X_Values. Then in the space that says "refers to" type the formula that Paddy described. Click OK.

Then hit F5 for Go To and type the name of your range to test.

Hope this helps.
Alriemer
This message was edited by Alriemer on 2002-11-06 12:03

 

[Glyder]

Thanks Alriemer, will give this a shot after lunch!

 

[Alriemer]

Glyder, btw, since you have 11 columns of data are you going to have 11 charts?

 

[Glyder]

No, just the one chart. Yes, its a bit confusing, but the nurse wants it that way. Its going to print out on one piece of 8.5 x 11, so it will be readable. She only needs to be able to see the change over time, and there will only be about 20-35 entries per patient.

 

[Alriemer]

1 column per patient? 1 column per data set? The reason I ask is that you could modify your named range to select one column or another based on a pulldown menu or other technique... I have "functionality creep."

Alriemer