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
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
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
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.
Please have a look at the attached. The graph regenerates automatically without any manual intervention.
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
hey its probably me but I can only see a new 'Temperature chart' sheet, but theirs nothing on it...
Try this - saved in the older format.
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
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...
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.
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
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
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.
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
It just keeps saying 'That Function is not valid'
I bloody hate excel sometimes! :D
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.
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
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..
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.
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks