Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    chart (2000 9.0.3821 SR 1)

    I have a query in Access that I have sent to Excel to create a chart. All the necessary information is in the spreadsheet. I have attached a small sample file to this for reference.
    The type of chart I am looking for is I think called a Stacked chart. The X axis is the Sale amount, the Y axis is the salesperson. The name of the chart is to be the "month" data, which is always the same because of the query.
    I need column to be "grow" by each sales person's sale. For example, in Dec, Sam had three sales. The chart should show a stack 4950 high (first sale amount) with the customer's name and item with in the stack. On top of that should be his next sale, 3550 (for a cumulative height of $8500) and so on.
    The Y axis will show three names across with the sales stacked on top of the appropriate salesperson.
    How can this be done. The chart wizard is very unclear to me.

    Thanks

    Rick
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    Your data needed to be rearranged (see attached). Once it's in the proper arrangement, you need to use the chart wizard & in step 1 choose 2nd from the left in the top row, which is the stacked column chart. IN the next step you have to override to get the series to go in columns.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    Bob,
    Thanks for the help.

    two follow up questions:
    Can the sales data of Customer name and Item be labeled inside the boxes. this way the 4950 sale would show the customer name of Warrens and the item of 16b Rocker.
    Since this chart will be printed, is there a way to have the boxes not shaded? It only needs to be outlined in black, with the customer name and item information inside. Attached is a sample of what I am trying to do.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    OK, look at this -- not saey to replicate, but doable...
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    Bob,
    I attached the wrong file to illustrate my example.

    Please review this one.

    Thanks
    Rick
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    yes
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    bob,

    You did it!!!

    thank you.

    I saw how you created the series strings for the data, and then also added a formula in F2, but I couldn't see how you made the chart do what you did. Does this show up on the Wizard.
    I'm amazed that this can be done. please help me learn what you did.

    Thanks again,
    Rick

  8. #8
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    1 - Click Carpenter 55P in the chart once.
    2 - Click it a 2nd time. Not as in a double click, but 2 single clicks. Notice the formula bar.
    To create it in the first place:
    1 - click the area of the column chart you want to put in the text. Name box (left of formula bar) should read "Series 3"
    2 - Click it again in the same place. Name box should read "Series 3 Point xxx" or something like that.
    3 - right click in same place & choose "Format Data Point"
    4 - select Data Labels tab and check Value. Click OK.
    5 - Now select the value entered by this process, say the 6925.
    6 - select it again.
    7 - go to the formula bar and type =, then click on the cell containing the text to show.

    HTH

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    Bob,
    I kind of understand how you did it, but my concern is that I was hoping to set up this worksheet and have it import this Access query and automatically create these charts, w/o the need to manually add the names/ items. Just push a button and the data is sorted, and the chart done.

    Is this possible?

    If not, can it be done a different way...within Access?

    Thanks,
    Rick

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    bob,
    Is there a way to have Excel create this chart, in this format, automatically?
    the data will change monthly, and I'm looking for a way to see if the chart could be generated on the data from the query.

    Thanks for any and all help!!!

    Rick

  11. #11
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    Does the data change as in just the values or are there more values? If just the values change, then by copying the data onto the same fields we've been toying with, it should be automatic. If the data consists of entirely new values in that there are more rows as well as more collumns, then a macro needs to be written to do the work, and it's not trivial (not difficult, but not trivial). I suggest either you do the steps I recommended a few posts ago and record them then edit it (if you know VBA at all), or, given the size of the task, you can "hire" me to write the code for you. My going fee is $125/hour.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: chart (2000 9.0.3821 SR 1)

    Hi,
    Just as alternative possibility, attached is an Access database that's similar to what you're after. I didn't have time to figure out the datalabels as I suspect you'd have to code that bit of it, but since your data is coming from Access anyway, I thought this might be worth considering.
    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: chart (2000 9.0.3821 SR 1)

    I think what you have is close to what I am looking for. My concern is the labeling of the data within the stack. Is this going to take a code to write this? I have no experience in code writing. Is there any sampling of any code that is already written to do this sort of thing that I can "cheat" from?

    Thanks,
    Rick

Posting Permissions

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