Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Paula G
 
Posts: n/a
Default How do I Plot Data Interactively in Excel

I'm using John Walkenbach wonderful Excel 2000 Formulas book, but I'm not
following along, apparantly with how to get my Excel workbook to allow me to
use data on different rows to plot a new chart based on the data in that
particular row. Any help would be greatly appreciated.

Paula
  #2   Report Post  
John Mansfield
 
Posts: n/a
Default

Paula,

First assume your file is named €śTest.xls€ť. Next, assume that you have the
following data set in Sheet1 - range A1:E7. For further reference, the
letter €śA€ť occupies cell B2 and €śItem 1€ť occupies cell A3.

Chart Data
A B C D E
Item 1 8 6 7 8 9
Item 2 5 6 5 7 4
Item 3 3 7 6 5 3
Item 4 4 6 4 6 5
Item 5 5 5 5 7 6

Put your curser in cell A3 (this step is very important).

Go to the standard toolbar and hit Insert - Name - Define. Add the name
€śData€ť and define it with this formula:

=OFFSET(Sheet1!$A3,0,1,1,5)

Next, add the name €śTitle€ť and define it with this formula:

=OFFSET(Sheet1!$A3,0,0)

Create a line or clustered column chart based on the data in the range A2:F3.

Click once on the series. Replace the original series formula:

=SERIES(Sheet1!$A$3,Sheet1!$B$2:$F$2,Sheet1!$B$3:$ F$3,1)

with this formula:

=SERIES(Test.xls!Title,Sheet1!$B$2:$F$2,Test.xls!D ata,1)

Press the F9 key as you move your curser through the range A3:A7. The
formulas should cause the chart to update with the offsetting row of data
from the active cell each time the F9 key is pressed i.e. each time the sheet
recalculates.

Hopefully I didn't miss any steps.

----
Regards,
John Mansfield
http://www.pdbook.com



"Paula G" wrote:

I'm using John Walkenbach wonderful Excel 2000 Formulas book, but I'm not
following along, apparantly with how to get my Excel workbook to allow me to
use data on different rows to plot a new chart based on the data in that
particular row. Any help would be greatly appreciated.

Paula

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I look up data in a different workbook in an excel spread. DoctorG Excel Worksheet Functions 10 January 13th 06 10:18 PM
Moving blocks of data in excel Yasmin Excel Discussion (Misc queries) 1 March 17th 05 11:39 PM
Layout/Formatting problems of Data Queries in Excel? Andrew Harding Excel Discussion (Misc queries) 0 December 23rd 04 04:51 AM
How do I plot data in Excel that is captured on separate sheets; . krwegner Excel Discussion (Misc queries) 0 November 30th 04 06:43 PM
getting data from 2 excel sheets automatically pinar Excel Worksheet Functions 0 November 9th 04 12:47 PM


All times are GMT +1. The time now is 06:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"