Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    chart range based on cell? (Excel 2003)

    Hi

    Please see attached workbook

    I would like to automate the graph, I would like to sum rows in col T(Net Net Price) where column I(Cust Class)= A or B or C or D.
    There are other names I assume the principle would be the same.

    At the moment I have to do it manually with this small sample this is easy, but normally I will have hundreds of rows.

    e.g.

    Names: ="A Customers"

    Y Values =Sheet1!$T$35:$T$42
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: chart range based on cell? (Excel 2003)

    Isn't this more or less the same as <post#=527,125>post 527,125</post: > ?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: chart range based on cell? (Excel 2003)

    Hi Hans

    It is, but I still haven't been to resolve it.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: chart range based on cell? (Excel 2003)

    You never reacted to the replies there by Jezza and Rory...

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: chart range based on cell? (Excel 2003)

    I am not sure exactly what you want. Based on your "labels" and description:
    Are you after plotting the A, B, C, etc customers (from I) each with separate markers for the values in col T? (you current chart has col T for A, and col Q for B and C)

    If so you could in a new col (eg Cols AC, AD, AE, add more as needed):
    AC33, AD33, AE33
    <pre>A, B, C</pre>


    AC34:
    <pre>=AC33&" Customers"</pre>

    AC35:
    <pre>=IF($I35=AC$33,$T35,NA())</pre>

    Copy/Autofill AC35 to AC36 and down the column.
    Copy AC34:AC whatever to cols AD and AE.

    Select the range of data (AC34:AExx), copy it and paste it into the chart and you will have plotted, the A, B, C data with different markers [Note, I47 contains "C " and not just "C" and that should be corrected or you must use a trim in the formulas to get it to work...]

    If this is not what you are after, could you elaborate?

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: chart range based on cell? (Excel 2003)

    Hi Hans

    I have apologised to Rory for not replying , I had a private e-mal conversation with Jezza.

    I did not explain to myself correctly in my origional post, whilst Jezzas post did what I asked, I did not really need to sum these rows, I need to select the range of rows that were = to A B C or D.

    I think the workbook in my latest post explains more explicitly.

    I apologise for any confusion.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: chart range based on cell? (Excel 2003)

    Hi Steve

    I am sorry, I don't know what happened I obviously selected the wrong column for B C all Cust Class should be related to column T

    I have corrected it in the attachment

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: chart range based on cell? (Excel 2003)

    I got, I think, half-way there. You also want the X axis...

    I assume you have followed my intructions above and replaced the ABC range, with my new ones in AC, AD, AE.

    You now need the X-ranges:
    In AF35:
    <pre>=COUNT(AC$34:AC35)</pre>

    Copy AF35 to AF35:AH47 [Expand rows as needed]

    Right click the chart, Source data, Series (tab)
    Select "A customers" enter in X-values:
    <pre>=Sheet1!$AF$35:$AF$47</pre>

    Select "B customers" enter in X-values:
    <pre>=Sheet1!$AG$35:$AG$47</pre>

    Select "A customers" enter in X-values:
    <pre>=Sheet1!$AH$35:$AH$47</pre>

    <OK>

    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: chart range based on cell? (Excel 2003)

    Hi Steve

    Thanks for reply, I am trying to apply it now.

    I will keep you informed of my progress.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: chart range based on cell? (Excel 2003)

    Here is working copy with the changes. If you change A, B, C values in col I the data will change. The data does not even need to be sorted/grouped by customers to work

    Steve

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: chart range based on cell? (Excel 2003)

    Hi Steve

    That worked terrific, I am so grateful to everyone who assisted with this request.

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: chart range based on cell? (Excel 2003)

    IHi

    Steve kindly provided me with formulas to set the range for ABCD Customers.

    I would like to go a step further and set the List Price, Cost Price, and min Price ranges.

    The principle is to find the greatest number of Classes in this demo sheet there are 7 A, 4 B, 1 C, 1 D

    so the List Price range would be =Sheet1!$V$35:$V$41 but if therer were more B's say from I42 to I50 there would be more B's than A's so
    this would the have to the range =Sheet1!$V$42:$V$50

    Hope this is clear

    Many Thanks
    If you are a fool at forty, you will always be a fool

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: chart range based on cell? (Excel 2003)

    Reposted as a new thread in <post#=531,252>post 531,252</post#>, so I am locking this one to avoid duplication and confusion. Please post replies in the new thread.

Posting Permissions

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