How do I create a dynamic chart

I have a database on sheet "D" with 1000 rows of data 79 columns wide.

How do I create a single line chart on sheet "A" that graphs a single row on 
sheet "D" where the row number is specified in cell H1 on sheet "A"

This doesn't work: 
=D!&ADDRESS(OFFSET(A1,H1,1,1,1),1):ADDRESS(OFFSET(A1,H1,1,1,1),78)

The chart would graph out whatever D! row number is pointed to by cell A1 on 
sheet "A"
0
NickKrill (11)
1/18/2006 10:32:02 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
429 Views

Similar Articles

[PageSpeed] 54

read other posts today

-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Nick Krill" <NickKrill@discussions.microsoft.com> wrote in message 
news:B009AF0A-D4CC-4168-8103-449165A2B934@microsoft.com...
>I have a database on sheet "D" with 1000 rows of data 79 columns wide.
>
> How do I create a single line chart on sheet "A" that graphs a single row 
> on
> sheet "D" where the row number is specified in cell H1 on sheet "A"
>
> This doesn't work:
> =D!&ADDRESS(OFFSET(A1,H1,1,1,1),1):ADDRESS(OFFSET(A1,H1,1,1,1),78)
>
> The chart would graph out whatever D! row number is pointed to by cell A1 
> on
> sheet "A" 


0
bliengme5824 (3040)
1/19/2006 1:28:41 AM
Bernard – 
Thanks for your rapid response. I spent the better part of this morning 
chasing down other posts and have not found anything that will allow me to 
enter a variable for the row number in the graph range formula. I spent 
considerable time trying to make something work but to no avail. 

 

Regards,

Nick Krill


"Bernard Liengme" wrote:

> read other posts today
> 
> -- 
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
> 
> "Nick Krill" <NickKrill@discussions.microsoft.com> wrote in message 
> news:B009AF0A-D4CC-4168-8103-449165A2B934@microsoft.com...
> >I have a database on sheet "D" with 1000 rows of data 79 columns wide.
> >
> > How do I create a single line chart on sheet "A" that graphs a single row 
> > on
> > sheet "D" where the row number is specified in cell H1 on sheet "A"
> >
> > This doesn't work:
> > =D!&ADDRESS(OFFSET(A1,H1,1,1,1),1):ADDRESS(OFFSET(A1,H1,1,1,1),78)
> >
> > The chart would graph out whatever D! row number is pointed to by cell A1 
> > on
> > sheet "A" 
> 
> 
> 
0
NickKrill (11)
1/19/2006 1:52:03 AM
There is conflicting information in the post.  First you indicate that 
the row # is specified by H1; then you indicate the row # is specified 
in A1.

Assuming it is H1 and that you want to plot columns 1 through 78...

Create a named formula (Insert | Name > Define...).  Say you name it 
PlotRng.  It should be =INDIRECT("'D'!$A$" & A!$H$1 & ":$BZ$" & A!$H$1)

Now, create a chart using this named formula.

For more help on creating a named formula or using one in a chart, see 
the links in the first paragraph in
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <B009AF0A-D4CC-4168-8103-449165A2B934@microsoft.com>, 
NickKrill@discussions.microsoft.com says...
> I have a database on sheet "D" with 1000 rows of data 79 columns wide.
> 
> How do I create a single line chart on sheet "A" that graphs a single row on 
> sheet "D" where the row number is specified in cell H1 on sheet "A"
> 
> This doesn't work: 
> =D!&ADDRESS(OFFSET(A1,H1,1,1,1),1):ADDRESS(OFFSET(A1,H1,1,1,1),78)
> 
> The chart would graph out whatever D! row number is pointed to by cell A1 on 
> sheet "A"
> 
0
1/19/2006 5:12:59 AM
Tushar - Thank you for your help, your solution worked marvelously!
Nick Krill

"Tushar Mehta" wrote:

> There is conflicting information in the post.  First you indicate that 
> the row # is specified by H1; then you indicate the row # is specified 
> in A1.
> 
> Assuming it is H1 and that you want to plot columns 1 through 78...
> 
> Create a named formula (Insert | Name > Define...).  Say you name it 
> PlotRng.  It should be =INDIRECT("'D'!$A$" & A!$H$1 & ":$BZ$" & A!$H$1)
> 
> Now, create a chart using this named formula.
> 
> For more help on creating a named formula or using one in a chart, see 
> the links in the first paragraph in
> Dynamic Charts
> http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
> 
> -- 
> Regards,
> 
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
> 
> In article <B009AF0A-D4CC-4168-8103-449165A2B934@microsoft.com>, 
> NickKrill@discussions.microsoft.com says...
> > I have a database on sheet "D" with 1000 rows of data 79 columns wide.
> > 
> > How do I create a single line chart on sheet "A" that graphs a single row on 
> > sheet "D" where the row number is specified in cell H1 on sheet "A"
> > 
> > This doesn't work: 
> > =D!&ADDRESS(OFFSET(A1,H1,1,1,1),1):ADDRESS(OFFSET(A1,H1,1,1,1),78)
> > 
> > The chart would graph out whatever D! row number is pointed to by cell A1 on 
> > sheet "A"
> > 
> 
0
NickKrill (11)
1/21/2006 7:22:02 PM
You are welcome.  Glad to be of help.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <44164841-9930-4521-90EE-80459B2618AA@microsoft.com>, 
NickKrill@discussions.microsoft.com says...
> Tushar - Thank you for your help, your solution worked marvelously!
> Nick Krill
> 
{snip}
0
1/21/2006 8:25:22 PM
Reply:

Similar Artilces:

Displaying cell data in a chart
Previous posts indicate a successfuly method for displaying data/text from a cell into a chart. Following this procedure, I select the chart, press the equal sign, and select the cell, and then enter. The address of the sheet and the cell address are displayed after the equal sign. When I press enter nothing seems to happen. The data are supposed to display in the middle of the chart, but I do not get any display of the data. I am in a Pivot Table worksheet but did not directly insert a Pivot Chart. I used getpivotdata and created new cells for plotting into the inserted column ch...

Chart Label wraps on the same line. "Wavelenght [nm]" appears as.
Chart Label wraps on the same line. example "Wavelenght [nm]" appears as "}Wavelenght [nm" in general the chart labels are not what i typed. Send me a copy of the file (personal mail not to newsgroup) What version of Excel are you using? -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "rog" <rog@discussions.microsoft.com> wrote in message news:E9369578-AA4B-480B-93A2-14D91B8AC1F8@microsoft.com... > Chart Label wraps on the same line. example "Wavelenght [nm]" appears as > "}Wavelenght [nm" >...

labeling data points on a scatter chart
Hey guys. I need some help with my scatter diagram im using for a marketing assignment, about cars. So i have thee collums, the 1st is the car type e.g. "Mustang" and the 2nd is the torque e.g. "300" and lastly then the Price e.g. "20 000" What i want, is for each data point on the scatter chart to have the cars name next to it. Using the the e.g. data/figures above, i would have at the point (300, 20 000) the word "mustang" next to it. Help: How do i do this? Thanx guys!!! After plotting the X and Y values, you can use a third party label...

Problem with Powerpoint Chart with RDP /span
I used RDP Session with /span with 2 monitors. When open PowerPoint 2003, insert a chart, click off (i.e. deselect) the chart, font compresses horizontally and the font are not visible correctly. ...

organization charts
Resize the organization chart boxes Feel free to do so. It is your workbook. Gord Dibben MS Excel MVP On Thu, 27 May 2010 14:53:41 -0700, KKJ <KKJ@discussions.microsoft.com> wrote: >Resize the organization chart boxes ...

Conditional Charting
Hi, i hope someone will be able to help me on that one. I want on the line graph to show the data points that are above and below the average control line to be of a diffenrent colour. For the moment i am only trying to the points that are equal to between 0 and 7 to be of a different colour. Below is the code to do so but i get an error msg saying that the object doesnt support method or property. Can anyone tell me where am i going wrong here. Sub ComponentValue() Dim i As Integer, CompVal As Integer Dim dpoint As Point i = 1 For Each dpoint In ActiveChart.SeriesCollection(4).Point(i) C...

Lockup when Moving Chart to a new worksheet
Excel locks up when I try to move a new chart to its own worksheet. How do you do this without excel locking up? ...

As Delegate create Rule in Inbox
I want to create a "generic" mailbox for several users to send messages on behalf of. I know how to configure this and set it so the users can view the mailbox. My problem is sometimes they will want a rule set up to reply to specific messages (for example a secretary sends out mass emails but wants the recipient to reply to their particular representative not the generic email address). How can I have the delegate set up a rule in the inbox so those messages that match the subject line receive a reply telling them to contact their representative. Since multiple people ...

Duplicate Draft Email Created
Hi y'all My client sends an e-mail through the SFO client. The activity is successfully created in CRM, but the email appears in the Draft folder twice. I believe that the email has been sent although it doesn't appear in the sent items. Anyone had this problem before? I checked for a duplicate email address in CRM but couldn't find one. Thanx Morag hi, We've got the same problem and on of our sales sent it twice thinking he forgot to send it. The first message is copied when your writing the message, as usual with Outlook. The second one is created when you send t...

Using an existing chart, how do I update the data
...

Permanent solution to event 9667 (Failed to create a new named property for database)??
Hi, I've been fighting the following problem for a few months now: Event Type: Error Event Source: MSExchangeIS Event Category: General Event ID: 9667 Date: 12/22/2009 Time: 03:14:58 User: N/A Computer: .... Description: Failed to create a new named property for database "First Storage Group\...." because the number of named properties reached the quota limit (8192). User attempting to create the named property: "SYSTEM" Named property GUID: 00020086-0000-0000-c000-00000000001b Named property name/id: "X-Vs" This article mentions...

Chart w/ multiple stacks
I am trying to stack a chart with 6 series. I want to have series 1 & 2 stacked, a new stack column for series 3&4, and another stack for series 5&6. I have 12 months of data for 6 series Jan Feb Mar Apr ...... Dec S1 Region 1 '99 xxxx xxxx xxxx xxx ...... xxx S2 Region 2 '99 xxxx xxxx xxxx xxx ...... xxx S3 Region 1 '00 xxxx xxxx xxxx xxx ...... xxx S4 Region 2 '01 xxxx xxxx xxxx xxx ...... xxx S5 Region 1 '02 xxxx xxxx xxxx xxx ...... xxx S6 Region 2 '02 xxxx xxx...

Excel Table/Chart to JPEG for Web
I'm looking for a simple (read cheap) program that I can copy/paste or print into and adjust the pixel count (keeping aspect ratio) and resolution (dpi) for upload to web in jpeg format. Ideas? Why not do a screen grab for free and edit in paintbrush? Alt+Print Screen will snap the active window, open paintbrush select Edit>paste and edit away -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk "sinofNOskSPAM at comcast dot net" <sinofsky@comcast.net> wrote in message news:CJ6dnUJO0cjfdnbenZ2dnUVZ_v-dnZ2d@comcast.com... > I'...

how do i create a chart with 4 sets of data?
I have to create a graph holding 4 sets of data, two bar charts and two line graphs. The other problem is that they are measured in different units - is it even possible to plot this on a single chart? Create a column chart of all four data series Right click on one in the chart; select Chart Type and convert to line Repeat with second data series Right click one of the data series; Format Data Series and specify Secondary y-axis Repeat with second data series best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "PK" <PK@di...

Create Playlist to play from External Hard drive
I have purchased a WD HD Media Player which plays songs loaded on my external hard drive but it does not play the playlists that were created on the main computer. I have read where the lists have to be created separately to play on the external hard drive, but I am unsure as to how to complete this. Has anyone else experienced the same problem? I am using WMP 10 on an XP machine and have exported all the songs to a Western Digital Hard Drive. I have connected this hard drive to the WD HD media player to play through my HD receiver but I can only play one song at a time. How do ...

[MDI] have a few doc type created while user press file new option?
Hello I'm new to MFC. I'm writing a MDI program which will plot a few graphs. So I use AddDocTemplate() to make a doc type for each graph that i need to plot. But the problem is when a user press file new, a dialog box come up and ask which type of doc they want instead of creating new child windows for all the type. How can i make it create all doc type windows instaed of asking which one the user want Thanks : Like, if i have 2 doc type for 2 graph. Cgraph1Doc and Cgraph1View for graph 1. And Cgraph2Doc and Cgraph2View for graph2. When the user press file new, the program ask wh...

Delete old chartobject and create a new named one?
Hi, I need a subroutine that delete the old chartobject in a worksheet, create a new chartobject , name it as I want. Reason is that if the user delete the chart, I want to recreate it. I know how to protect it but feel that the user had to be able to change labels, colors etc. More than grateful to every suggestion! Kind regards tskogstrom Hi, This will delete a chart object, but not sure how you are identifying which chart object. Activesheet.chartobjects(1).delete ' create and give it a name with Activesheet.chartobjects.add(1,1,400,300) .name = "MyName" end with...

can't create schema for qualified attributes in element with default namespace
Hi, I have a xml file like the one below <?xml version="1.0" encoding="utf-8"?><e1 xmlns:e1="http://tempuri.org/Source1.xsd" e1:att1="1" e1:att2="2" e1:rest="345"/> If I try to create a schema for it with Visual Studio, I get the error "Failed to create a schema for this data file because: Although this XML file is well formed, it contains structure that Data View cannot display. The 'http://tempuri.org/Source1.xsd:att1' atrribute is not declared. An error occurred at , (7,8)" However if I qualif...

charting help
I am looking for help making a chart. My chart is a time line for employees so going down in column A is employee names going across the rows of the top i have the number of employees needed for that particular hour. is this possible to chart? -- darkbearpooh1 ------------------------------------------------------------------------ darkbearpooh1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30640 View this thread: http://www.excelforum.com/showthread.php?threadid=503150 Hi, Sounds like a gantt chart. take a look at these, Jon Peltier has an article on ...

Creating a right-click command to backup file
Hi All I have Creating a right-click command to backup file try txt and then xls file. Try txt file ok. Then try xls file, not ok, when right-click no option prompted ? Do you know why ? For txt file REGEDIT4 [HKEY_CLASSES_ROOT\txtfile\shell\backupfile] @="Backup Text File" [HKEY_CLASSES_ROOT\txtfile\shell\backupfile\command] @="cscript //b H:\Software\shell\xbcp.vbs "%1" "txt"" For excel file REGEDIT4 [HKEY_CLASSES_ROOT\xlsfile\shell\backupfile] @="Backup Excel File" [HKEY_CLASSES_ROOT\xlsfile\shell\backupfile\...

How to start a macro with pivot-chart-update-button (!)
Hey Guys The pivot-chart-update-button (!) dominates my custom pivot-chart- layout. This annoys me (and I get no credit from my colleges as Excel- expert). The chart is placed in a chart-sheet. I have recorded a macro that "re-build" the chart layout. But this macro is to be activated only from the macro-list options, since Excel not alows me to place an button in this chartsheet, and connect this button to a macro which 1) updates the chart and 2) then re-build the layout. I hope for some qualified help here - to improve my repurtation in this office - and make the stars shine on ...

Creating HTML help file from within Visual C++ Build
When I created an MFC AppWizard project, Visual C++ also created a bare-bones help file for WinHelp. Whenever I build the project, VC++ compiles this help file automatically. However, I do not want to use this WinHelp help file. I have separately created a chm (HTML help) file for my application, which I would like VC++ to compile during the application build process and place in a specified directory. How do I go about: 1. Removing the default WinHelp compiling from the project? 2. Adding HTML help compiling to the project? Thanks for help Abhijit 1. I think you can remove the default help...

Chart formula style depends on number of sheets....
I was reading 7 formulas from a seriescollection in VBA (xlbubble chart) . First four gave me A1-style formula, last three gave me R1C1 style formula. This messed up subsequent processing. I used: ActiveChart.SeriesCollection(SeriesIndex).Formula Nothing changed while running this from index 1 to 7. Cause of this wonderful behaviour? The last three chart series were added while the workbook had only one sheet. The first four were added while the workbook had more than 1 sheet. Solution: add a second sheet to the workbook. All fomulas now come in A1 style... Very weird. Can't imagin ...

totals in a pivot table chart
Hi I want to show the totals in a pivot table chart How can I show that By the way, I have the office 2000 Thanks so muc Sylvio I don't know of any way to include the totals in a PivotChart. You could create a normal chart, based on the pivot table, and include the totals in that. On Jon Peltier's site, there are instructions for creating a normal chart from pivot data: http://www.peltiertech.com/Excel/Pivots/pivotcharts.htm Sylvio wrote: > Hi, > > I want to show the totals in a pivot table chart. > > How can I show that? > > By the way, I have the...

Scatter chart Indicator size
I am trying to change the size of data indicators larger than the range allows(2-72). I get as far as the format legend key window, but would like to go bigger than 72 units. Has anyone dealt eith this before? Hi, The only way around this would be to use VBA and custom markers. You would need to set the size of the shape based on your data and then copy/paste the shape to the appropriate data marker. Here is a page explaining custom markers. http://peltiertech.com/Excel/ChartsHowTo/CustomMarkers.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Joshu...