+ Reply to Thread
Results 1 to 6 of 6

How do I create a dynamic chart

  1. #1
    Nick Krill
    Guest

    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"

  2. #2
    Bernard Liengme
    Guest

    Re: How do I create a dynamic chart

    read other posts today

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

    "Nick Krill" <[email protected]> wrote in message
    news:[email protected]...
    >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"




  3. #3
    Nick Krill
    Guest

    Re: How do I create a dynamic chart

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > >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"

    >
    >
    >


  4. #4
    Tushar Mehta
    Guest

    Re: How do I create a dynamic chart

    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 <[email protected]>,
    [email protected] 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"
    >


  5. #5
    Nick Krill
    Guest

    Re: How do I create a dynamic chart

    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 <[email protected]>,
    > [email protected] 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"
    > >

    >


  6. #6
    Tushar Mehta
    Guest

    Re: How do I create a dynamic chart

    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 <[email protected]>,
    [email protected] says...
    > Tushar - Thank you for your help, your solution worked marvelously!
    > Nick Krill
    >

    {snip}

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1