Ok, another question.

Glyder

New Member
Joined
Nov 4, 2002
Messages
11
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?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Nov 4, 2002
Messages
11
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

New Member
Joined
Nov 4, 2002
Messages
11
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

New Member
Joined
Nov 4, 2002
Messages
11

ADVERTISEMENT

bump
 

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102
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

New Member
Joined
Nov 4, 2002
Messages
11

ADVERTISEMENT

Thanks Alriemer, will give this a shot after lunch!
 

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102
Glyder, btw, since you have 11 columns of data are you going to have 11 charts?
 

Glyder

New Member
Joined
Nov 4, 2002
Messages
11
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

Board Regular
Joined
Aug 18, 2002
Messages
102
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
 

Forum statistics

Threads
1,148,277
Messages
5,745,810
Members
423,979
Latest member
Nuzuki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top