Thread: 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"

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"

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"
>
>
>

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/ne...rts/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"
>

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/ne...rts/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"
> >
>

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}