+ Reply to Thread
Results 1 to 48 of 48

Creating a line graph for certain data..

  1. #1
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Creating a line graph for certain data..

    I'm trying to create a button that when clicked will generate a line graph for a column of values that has a heading. However the column will often get new values inputted into it and so the graph will need to generate for newly input data too...can anyone help with this please? Ive attached an example of what I mean. Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    I suggest using a dynamic named range.

    http://www.ozgrid.com/Excel/DynamicRanges.htm

    Use the range name as the source of the data.
    Martin

  3. #3
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    I've had a look into that but I can't seem to work out how my formula should be worded, as the sheet name is '1 Database' and my info starts at cell D5. Also, im running excel 2010 and can only seem to find Define Name if I select all the cells and right click. However once I've named the cells and I try to produce a line graph with the name it says reference is not valid.

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    Please have a look at the attached. The graph regenerates automatically without any manual intervention.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    hey its probably me but I can only see a new 'Temperature chart' sheet, but theirs nothing on it...

  6. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    Try this - saved in the older format.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    thats great, but how exactly did u make it update itself? I've tried copying your forumla but it doesnt seem to want to update itself...

  8. #8
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    It's the range that is dynamic and the graph just reflects this.

    Have a look at how the range named 'Temperature' is defined by looking in the name manager on the formula bar.

  9. #9
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Ok, I'm having real trouble getting this to work...It works with a few of the sheets but with others it just doesnt show any data, then everytime I go to check the formula for a certain chart it seems to of changed the cell numbers in the formula by itself :S

  10. #10
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    Not to sure what you are doing but it might be related to how you ae setting the source data for the chart.

    If you right click on the chart, you get an option to 'Select Data'. When you get the dialog box, make the amendment to series 1 in the bottom left as opposed to the 'Chart Data Range' at the top. Select the series and press edit.

    Hope this helps.

  11. #11
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    It just keeps saying 'That Function is not valid'
    I bloody hate excel sometimes! :D

  12. #12
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    Can you post a copy of the workbook and a description of which ranges are of interest? It might help diagnose what is going wrong.

  13. #13
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    I certainly can, I do appreciate the help a great deal by the way!!
    Ok, basically, information is input on sheets named 'Tank 1' to 'Tank 16', the information is sent to the database sheets, which are '1 Database' to '16 Database', and then each database has a corresponding graph sheet. So 'Graph 1' should show the Temperature values found in '1 Database'. The problem I'm having is working out what formula needs to be put into the Graphs to link them and update with the database sheets. Ideally I'd also like to make a graph for the PH values and the ammonia values on the same sheet as the temperature graph, but I'm still stuck on the temperature graph formulas, so havent even looked into doing them..
    Attached Files Attached Files

  14. #14
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    Thanks for this - it helps a lot.

    I've made some changes for the first of your tanks.
    • I've changed the chart type to scatter plot as I suspect that you are interested in seeing the variation in temperature and pH over time and you will get days when no measurement is taken.
      I've created new dynamic named ranges for date and pH.
      I've added a second series to the graph so that you can see how the two parameters are varying with time.

    There's a lot of opportunity here to simplify your workbook considerably with a single data entry sheet and single chart sheet, both of which could be set to auto-refresh to a tank number picked from a drop-down list.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Ah, I never thought of doing it that way. How would I go about creating that though? Would I firstly create a drop down list for a cell that contains Tanks 1-16, then have it save the data to the database sheets I already have, but also have a button that when clicked produces a chart for the tank selected on the input sheet? Is their a name for this method so I can read up on it a bit as I have no idea how I would make a formula that would do that..
    And thanks for the help with the dynamic ranges and the chart!

  16. #16
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    I've created a new sheet that has the potential to merge all the input sheets into 1 sheet, but I'm not sure how to progress with the formulas for saving inputs to their respective databases based upon the drop down cell input selected, and how to generate a graph based upon which tank is selected.
    Do I just need to know the function for identifying the active tank based upon the drop down list, and then use the original formula of saving it to their respective databases? Would 'Vlookup' work for this?
    I've attached an updated workbook, the new sheet is called 'Tank All', would this be sufficient for the merge?
    As always your help is greatly appreciated.
    Attached Files Attached Files

  17. #17
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    I'd independently created a proof of concept from scratch - please see the attachment. It avoids the need to have dynamic ranges as it resets the data source on the chart by code.

    I'll have a look at your latest version shortly
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Could I write the formula as this:
    Please Login or Register  to view this content.
    But keep on adding an 'IF' for each tank?
    Or is their a simpler way?
    I've had a look at the one you just made but can't for the life of me understand half of the formulas! :D
    Also, your chart won't show up on my excel again (I'm running Excel 2010) sorry.
    Last edited by teylyn; 12-01-2011 at 08:36 PM. Reason: changed quote tags to code tags

  19. #19
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    I've saved in the older format and added comments to the code to help explain what is going on.

    I suggest having a play with the sample data on the Input sheet. Just hit the submit button.

    There is additional validation that really should go in to check that all the mandatory data has been added for a line before it is submitted but that's step 2 .
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Thanks, I've had a mess around with the worksheet and seem to understand it a bit more now, I've added a clear contents to the submit formula, and have put in a bit of validation, but havent locked the worksheet yet. Unfortunately I still can't see the chart not really sure why to be honest, I have excel 2010, and it should be backwards compatible, so not sure what's going on there. Also, if you look on the 'Data' sheet it keeps producing a long list of a certain tank number for some reason (if you clear all the data and enter new inputs it seems to be the first tank that you enter data with) is their any purpose for this?

  21. #21
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Attached worksheet
    Attached Files Attached Files

  22. #22
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    I've taken your latest version and made a few tweaks to improve data completeness as well as fixing some bugs.

    If you still have problems seeing the chart, I would suggest getting rid of the current one and creating your own scatter plot with a title and naming the sheet Chart It doesn't matter which data it uses as its source as this is reset by the code. I don't understand why you are not seeing it as I am using Excel 2010 as well.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Ok, I've finally worked out the issue with why I couldn't see the graph, appears as though my hp printer was causing issues. Anyway, I've had a mess around with the worksheet you made and it is excellent, however I've added some validation to cells, some instructions and changed a few of the cells to input type and heading etc and it seems to of somehow stopped the 'Submit' button from working :s I believe it has something to do with the x value of the chart which it can't find, but I've tried changing the cell formatting back and it still isn't working. Any suggestions? I really do apologise for nagging on at you, I know I've probably took up way too much of your time! It is all appreciated so much though!

  24. #24
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Would help If i remembered to attach again!!
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    I think I worked it out, i't wont let me run the macros when I protect the sheets. I've found this code, which I could use..
    Sub MyMacro()
    Sheet1.Unprotect Password:="Secret"
    'YOUR CODE
    Sheet1.Protect Password:="Secret"
    End Sub
    And I've been reading that you can somehow use a 'userinterfaceonly' method, but not exactly sure what this means. Any advice on what the best method is?
    Thanks
    Last edited by Stryda; 12-01-2011 at 11:07 AM.

  26. #26
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    This one is a new one for me.

    I suggest trying the following pasteed into the ThisWorkbook tab in the VBA editor.

    Please Login or Register  to view this content.

  27. #27
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Hmm, I tried that, but it's bringing up a run-time error 4004. I've already added a formula to thisworkbook in order to save the workbook automatically when it closes, so not sure if I have integrated the protected sheet formula correctly.
    Attached Files Attached Files

  28. #28
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a line graph for certain data..

    stryda,

    please edit your post #18 above and add code tags.

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

  29. #29
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    done, sorry.

  30. #30
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Creating a line graph for certain data..

    Thanks for the effort, but you used quote tags. I've changed them to code tags. Code tags look like this:
    [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

    When you are in Advanced view, you can easily add them by clicking the # icon.

  31. #31
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    I think that the problem is due to the fact that the third sheet is a chart.

    Making this simple change seems to fix it.

    Please Login or Register  to view this content.

  32. #32
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Sorry Teylyn, will make sure I use the correct tags next time.

    I'm afraid I'm still having problems with this workbook though mrice
    If I open the workbook with all of the sheets already protected it straight away brings up a 'runtime error 13', and when I click 'Debug' it highlights the 'Next Sheet' in the code you just provided.

    If I go to input data and click 'submit' it does nothing, and if I click 'view' it brings up a 'run-time error 2147467259'

  33. #33
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    Reading around on this, it looks like others have experienced similar problems.

    I have found an alternative which dynamically protects the data sheet when it is activated and unprotects when it is deactivated. As the macro only runs starting on the input sheet, this should be OK.

    Please Login or Register  to view this content.
    Paste the above onto the Data tab in the VBA editor.

  34. #34
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Hmm, ive put in the code and the 'view' button is now working but the 'submit' button is bringing up a 1004 error code regarding the 'chart' worksheet being protected. Also, if you open the workbook with the worksheets already locked it straight away brings up a '1004' error code. Also, I've been trying to allow autofilter of the 'Data' sheet but as It's protected it wont let me...I've tried adding a code to the data sheet to enable the autofilter as true but its still not working
    Attached Files Attached Files
    Last edited by Stryda; 12-04-2011 at 08:17 PM.

  35. #35
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    From what I can gather...protecting the input sheet wont allow it to clear the contents of that sheet, and if you protect the chart sheet it wont allow you to input any data and change the chart sheet...I think...

  36. #36
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    I've attempted to unprotect the workbook when the 'Submit' macro is activated but it brings up a 1004 error and highlights the clearcontents line. Can you see a problem with the following code?

    Please Login or Register  to view this content.

  37. #37
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    Hmmmm....

    A long shot - have you introduced any merged cells?

  38. #38
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Nope I havent...
    I've attached an updated copy of the workbook, the only way to get it to work is to unprotect the chart sheet and the input sheet. Strangely though...if you protect the chart sheet and data sheet but unprotect the input sheet it sends data to the data sheet but brings up an error regarding the x series values of the chart.
    Do you know of any other method to allow protection but get the code to work? Or is the only thing left now to just leave the chart sheet unprotected for end users?
    Attached Files Attached Files

  39. #39
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    Try

    Please Login or Register  to view this content.

  40. #40
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    That worked a treat! I think I may be about ready to put this workbook into action !!
    Honestly, Thank You so much for your help you've been truly amazing! I definitely couldn't of done this without you, so thank you.
    I'll update and let you know how things are going.

  41. #41
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    No problem - it's been an interesting exercise.

    I hope that the fish (assuming that is what is in the tanks) are happy.

  42. #42
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Haha yeh it's fish mostly and a couple of terrapins ...I hope they are too!
    Thanks Again.

  43. #43
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Umm...could I possibly ask for your expertise once again?
    I'm wanting to add a couple more inputs onto the 'Input' sheet and subsequently onto the 'Data' sheet, but I can't seem to understand the code too well to be able to do that. I would of been fine doing it if the code stated which cells to copy (ie A5:G5) but it says
    Please Login or Register  to view this content.
    and things like that which I don't fully understand (I'm not asking you to rewrite the code or anything).
    The new data values I'd like to add are 'GH' and 'KH'.

    Also, would it be difficult to add ammonia readings to the chart as well? Or would it look too hectic?

    Many Thanks.
    Attached Files Attached Files

  44. #44
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Still can't seem to get this to work Any help greatly appreciated

  45. #45
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating a line graph for certain data..

    The important number is the 7 which refers to the last column of data that you are working with.

    Please Login or Register  to view this content.
    Sounds like you will need to change this to a 9 in order to accomodate your two additional columns.

  46. #46
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Ah right ok ..that works a treat, thank you for that!

  47. #47
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    Hey again mrice, I've put this spreadsheet into action and it's done fantastic, however I haven't been to use it for a while and have just gone back to use it and for some reason there is no chart present on the chart sheet :S I haven't got a clue why it's happened. Would you mind taking a quick look at it Pleaseeeeeee . Thank you. P.s hope you've been well.

  48. #48
    Registered User
    Join Date
    11-09-2011
    Location
    leeds, england
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: Creating a line graph for certain data..

    I'm struggling to upload the spreadsheet :S It's bring up a red exclamation mark with a 500 cancelable text error

+ 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