+ Reply to Thread
Results 1 to 11 of 11

How can I auto populate line graphs with varied ranges?

  1. #1
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,401

    How can I auto populate line graphs with varied ranges?

    Excel Forum Sample of Screw and Barrel Log.xlsx
    Hello all,

    I have a workbook with multiple sheets that I would like to have a line graph for each sheet create automatically. The number of columns and rows are not the same on all sheets. The number of columns used will change for each sheet. So one sheet can use 1 column, another sheet can have 3 columns, and another can have 15 columns. The rows on each sheet will be different, but shouldn't change.

    The only constants are that the date will always be in row 2, and the data used will always start in row 7. The horizontal axis will always start in A7 but may only go to row 33, 34, 35 or a lower or higher row.

    In the workbook I have manually created the graphs for the 14A Screw, 14B Screw and 14B Barrel sheets. If someone could show me the way to get the 14A Barrel sheet to automatically create the line graph, I should be able to get it to work for all the other sheets as well (I hope).

    Thanks to all that have helped me in the past, and thanks in advance to those that help me with this.
    Last edited by gmr4evr1; 12-27-2017 at 01:25 PM. Reason: Added attachment
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    Dubai
    MS-Off Ver
    2013
    Posts
    5,737

    Re: How can I auto populate line graphs with varied ranges?

    You could use dynamic named ranges to define the range of the chart. But before that, I'd encourage some minor tweaks to the layout, if that's permissible.

    - Move the date to Row 6 rather than Row 2. Move the measurement to Row 2. This will give you a contiguous range of data
    - Do not type in anything in Column A after your chart data. Your notes etc should be from Column B onwards if required at all.
    - Row 6 should not have any data beyond the dates

    Define the dynamic named range. The formula is as below. I named it 'Range'
    =OFFSET('14A Screw'!$A$6,,,COUNTA('14A Screw'!$A:$A)-5,COUNTA('14A Screw'!$6:$6))

    You can see this in the Name manager as 'Data'

    Now, simply use in the chart data range '14A Screw'!Data. Do this for all sheets.

    See attached.

    553541d1514395534-how-can-i-auto-populate-line-graphs-with-varied-ranges-excel-forum-sample-of-.xlsx
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,401

    Re: How can I auto populate line graphs with varied ranges?

    Ace,
    Thank you for your help. I will take an in depth look at this as soon as I can. As for the layout, I kind of figured that would have to be changed, which will not be a problem.

  4. #4
    Registered User
    Join Date
    05-30-2017
    Location
    Bursa,Turkey
    MS-Off Ver
    Office2013
    Posts
    5

    Re: How can I auto populate line graphs with varied ranges?

    Hi gmr4evr1,
    see attached file, for another approch.
    Last edited by sakman26; 12-30-2017 at 02:53 PM. Reason: message deleted. See Mess.#6

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    9,556

    Re: How can I auto populate line graphs with varied ranges?

    sakman26, Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!

    * you should have explained several of your dynamic range formulas in the post also.
    Last edited by protonLeah; 12-30-2017 at 02:13 AM.
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    05-30-2017
    Location
    Bursa,Turkey
    MS-Off Ver
    Office2013
    Posts
    5

    Re: How can I auto populate line graphs with varied ranges?

    protonLeah,
    In this work, it is impossible to write the details of all the operations
    (unfortunately my english will not be enough for the detailed explanation).
    For this reason, I think that I have to delete my message(reponse) and the attached file.
    Thanks..

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    9,556

    Re: How can I auto populate line graphs with varied ranges?

    sakman26's solution:

    Named/Dynamic Ranges:

    a =VALUE(GET.WORKBOOK(4)&INDIRECT("IV50000"))
    ch =INDIRECT(ADDRESS(3,2,,,INDIRECT(ADDRESS(1,10))))
    chk =IFERROR(MATCH(sa,listName,0),"–")
    chkG =IFERROR(MATCH(saG,listName,0),"–")
    f =GET.WORKBOOK(16)&INDIRECT("IV50000")
    listName =OFFSET(Data!$C$6,,,COUNT(Data!$B$6:$B$152),1)
    RngC =INDIRECT("'"&sa&"'!A3:U152")
    RngCG =INDIRECT("'"&saG&"'!A3:U152")
    RngG =Graph!$A$8:$U$157
    RngM =Main!$A$3:$U$152
    sa =Main!$D$1
    saG =Graph!$D$1
    sG =COUNT(Graph!$A$14:$A$163)
    sGC =COUNT(Graph!$B$9:$U$9)
    sh =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&INDIRECT("IV50000")
    xval =OFFSET(Graph!$A$14,,,IF(sG=0,1,sG),1)
    xvalMax =OFFSET(Graph!$B$6,,,1,IF(sGC=0,1,sGC))
    xvalMin =OFFSET(Graph!$B$4,,,1,IF(sGC=0,1,sGC))
    yvalMax =OFFSET(Graph!$B$5,,,1,IF(sGC=0,1,sGC))
    yvalMin =OFFSET(Graph!$B$3,,,1,IF(sGC=0,1,sGC))



    The syntax is: Get.Workbook(type_num, name_text)
    Type_num Returns
    1 The names of all sheets in the workbook, as a horizontal array of text values. Names are returned as [book]sheet.
    4 The number of sheets in the workbook.
    16 Name of the workbook as text. The workbook name does not include the drive, directory or folder, or window number.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-30-2017
    Location
    Bursa,Turkey
    MS-Off Ver
    Office2013
    Posts
    5

    Re: How can I auto populate line graphs with varied ranges?

    protonLeah,

    Merci for your comprehension.....

  9. #9
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,401

    Re: How can I auto populate line graphs with varied ranges?

    @ sakman26 - Thank you for assisting me with this, I like your version better than my original. I will talk to the department that is going to be using it to see if it will work for them. I do have one question though...
    The attachment I posted only had 4 sheets in it, with names changed, HOW the heck did you get a workbook with all the sheets in it????

    @protonLeah - Thank you very much for reposting sakman26's work, and explaining what he had done, I really appreciate it!!

  10. #10
    Registered User
    Join Date
    05-30-2017
    Location
    Bursa,Turkey
    MS-Off Ver
    Office2013
    Posts
    5

    Re: How can I auto populate line graphs with varied ranges?

    gmr4evr1,
    for your question
    "The attachment I posted only had 4 sheets in it, with names changed, HOW the heck did you get a workbook with all the sheets in it????"

    I downloaded directly the attached file you sent.
    I attache the downloaded file. You can compare it with what you sent. I did not do anything except this one.
    For your information..

  11. #11
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,401

    Re: How can I auto populate line graphs with varied ranges?

    Wow, that is odd. When I open the attachment in my original post (#1), It opens as I attached it, with only 14A Screw, 14A Barrel, 14B Screw and 14B barrel sheets as the only ones in the workbook with the name changes. With graphs on both of the 14B sheets.
    When I open the one you attached (post # 10), it has the 4 sheets mentioned above, plus all the ones I deleted before I attached it in post #1.

    Original when I open it from 1st post - no hidden sheets
    gmr4evr1 original attachment.PNG
    sakman26's when they open it
    sakman26 attachment.PNG

+ 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