Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Buckinghamshire, UK
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Query and Named Range Charts (excel 2000)

    Hi, I have a an excel query that pulls in data from MS Access 2000. Data comes into worksheet "builders". The incoming data has a name that it got from the name of the MS Query "Builders Query". How can I refer to this named range "Builders Query" for the source data control of an Excel Chart? Can it be done and if so what is the syntax? I need this facility so the chart will grow and shrink with the incoming data provided by MS Query.
    Many Thanks In Advance

  2. #2
    Lounger
    Join Date
    Nov 2001
    Location
    Buckinghamshire, UK
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Query and Named Range Charts (excel 2000)

    Hey, Thank u very much Jan, that worked perfectly!

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database Query and Named Range Charts (excel 2000)

    Jan, that is a really clever <img src=/S/clever.gif border=0 alt=clever width=15 height=15> idea. When I followed your suggestion, I can use the named range successfully in formulas like =Sum(YourName); however, the name does not appear in the GoTo Dialog box. Why not?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Query and Named Range Charts (excel 2000)

    I don't know why it does not appear. Seems like all defined formula's do not appear, since XL does not recognize them as ranges (which -in fact- they normally aren't, except formula's that return a range, like OFFSET). Makes sense you don't see things you cannot go to.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    Buckinghamshire, UK
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Query and Named Range Charts (excel 2000)

    Last week this worked fine....now Excel keeps giving me error message "invalid reference" it won't even refer to any named range? Any ideas

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Query and Named Range Charts (excel 2000)

    Have you tried redefining the name?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Query and Named Range Charts (excel 2000)

    You might also create a dynamic rangename yourself.
    lets say your data is in A1:A.....
    Insert, name, define.

    Name: YourName
    RefersTo: = OFFSET($A$1,0,0,COUNTA(A:A),1)

    Now enter some rows into column A and press F5 (Goto) and enter the name in the goto box.

    I used that technique in a sample spreadsheet (called Autochrt.zip) you may find at the bottom of
    [url]www.bmsltd.ie/mvp[url]

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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