I have a table that has formulas that read off a pivot table. When the pivot table is updated, the other non-pivot table is updated. I created a graph off of the non-pivot table data. I want to do a bar chart, but if data is added to the pivot table (and then through formulas added to my non-pivot table) I want the graph to automatically update with the new fields. I can't figure out how to do this
For example, if my table had Grapes, apples and peaches and their quantities in cell A2:b4 if I choose range a2:b5 I don't get any data in my graph at all, if I choose the correct range a2:b4, I get a bar chart with Grapes, Apples and Peaches and thier quantites. If I added Strawberries to the Pivot table, the non-pivot table would update, but the graph is not grabbing row 5, I have to manually change the range to a2:b5.
i have a graph that i've linked to let's say cells A1:C150
however, my range could sometimes needs to be shrunken to A1:A100 or A1:A40 etc. (the number of rows that are "active" is determined by cell K27)
i'd like for my graph to automatically adjust depending on the number of rows that are filled within the range above. can this be done with formulas and graph manipulations?
i've put in a formula currently that makes the value of the cells that would return 0 into "" but that doesn't seem to do the trick.
I have a sheet that has names in column A and a list of subjects in Row 1. Cells B2:BA25 have the scores acheived for each person in each subject. I was thinking of creating a customisable graph where by there would be a dropdown box that could select a persons name and this would populate a bar graph of all that persons scores.
I have a graph that auto updates but sometimes there is only 1 series and sometimes there is 2,3,4, or 5 series. Is there a way to have the graph update accordingly? and only have have the series that are valid be in the key on the graph. Currently the graph keeps all the series in the key even when there isn't any data for it.
Please see attachment. Use sheet 2 for the graph. and use the drop down menu to select catagory. this ia very quick mock up of the concept.
I am making a dynamic graph with use of listboxes. Please see attached example.
In the example doc is a graph which is now static. Based on the rows I selected in the table, for store 1 and for february. What I'd like is that a user can select a store and a month in 2 listboxes and that the graph is dynamically created based on dynamic selection in the table. I think I need to use the OFFSET function and some defined names but I'm not sure how.
I have a database in excel with a lot of tables in which lets a uses a combo box so the user select can view just one table.
I want to be able to create a macro so when a button is pressed, it copies the data into a new workbook and creates a chart so they can do whatever they want without disrupting anything in the original workbook.
I have been able to find code that will export the data and create a chart (see below) but the problem is the range changes from table to table so I need to find a way of changing how the chart picks up the data range.
Sub Chart_New_Book()
Sheets("Temp").Select Cells.Select Selection.Copy Workbooks.Add ActiveSheet.Paste Range("A1").Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10") ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" End Sub
So at the moment when charting ony cells A1:C10 will be plotted no matter what the size of the table is and making the target range bigger only sees the blank cells being plotted.
This may be a tough one. Through a button, I am trying to create a popup window displaying an excel chart, viewed always on top, dynamically linked to my spreadsheet data. The idea would be to see it evolve as I input data, and be able to open as many as I want. I guess I could create a macro to paste/clear the graph, but a pop up window would appear more professional and more user friendly.
What I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then _ Target.Offset(0, 14).Value = Now
End If End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
I am in the process of setting up some graphs. The graphs will show the last 6 months of data so they move as each month is goes. I saw a slick way of doing this using the count function but this did not quite fulfill my needs. What I was looking for is to be able to input the start month in a cell. The cell would be part of the function within the formula. I created a simplified version of the spreadsheet below.
Spreadsheet.jpg
Then I created my names using the ctrl-F3
Names.jpg
I then created the graph I wanted and wanted to use the formula =SERIES(Sheet1!$B$3,!chtCat,!chtIssuesReported,1). This kept getting an error indicated nothing really. It basically says there is something wrong with my Series function. I have a working version of a similiar worksheet I have been basing my entries on. They look almost identical but mine is failing. I also created a new file and tried this and it still fails. I can run the formula evaluator within Excel 2010 against the !chtCat and !chtIssuesReported names and they both return the correct value (which equals areas on the spreadsheet). I have also tried to enter the spreadsheet name and a tab in front of the names to get them to work and still get an error. Basicall I am trying to create the graph below (this grpah is using the hard coded locations)
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e. First dynamic range: called "Milestones" at A11 Second dynamic range: called "Activities" at A25 & make a Validation list that will list content of both
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
i have a range (A2:A30) that has values imported into it (sheet1). in B2:B30, i'm doing a simple calculation based on the perspective values in A. The number of values imported into A is different each time my model is run.
i'd like to graph A and B columns against each other but i don't want to have to adjust the range on the graph each time the model is run.
ps. i tried graphing A2:A30 vs. B2:B30 but the graph also shows the empty cells in A so that doesn't work.
I have a report that has rolling week by week data (as in YTD). The graphs I have are based on a 13 week period. As I add a week (done by VBA import) I want to update graphs to lastest 13 weeks as in last 13 columns of data on data sheet.
I am trying to automate a graph for data that is not always the same amount but always in the same format. I have attached a spreadsheet that has an example of the data.
Columns B, D, & E are the ones that I am interested in charting in a line with markers type graph, but the rows may be 100 for one set of data and 1000 rows for another set of data. I am trying to get the macro to open a new worksheet and as part of the macro create a userform that will ask for the text information on the top of the worksheet (as this is not always correct when imported).
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.
For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel ......
I have a vast database where I have linked charts. I send the data from a macro to the database and sometimes there are 1000 inputs and sometimes there are only 20. How would i set up my charts to only graph the inputs that are present?
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range. B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
I'm trying to create a line graph to show the trends of usage. I have a list of dates that I pulled from a website. I am trying to create a function that will count the number of dates between, for example, 8/5/2013 and 8/11/2013.
I had tried using this function to create a set of weekly ranges. =TEXT(DATE(2013, 1, 7)+(ROW(2:2)-1)*7, "m/d/yyyy h:mm AM/PM")&"-"&TEXT(DATE(2013, 1, 7)+(ROW(2:2)-1)*7+6,"m/d/yyyy h:mm AM/PM")
It correctly displays the date ranges, however when using this function: =COUNTIF($E$2:$E$305, A2) Nothing gets pulled.
Here is one of the dates that has been pulled for example: 8/5/2013 11:10:00 AM
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed The code is then highlighted in yellow, the code is: Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
I would like to make a scatter graph that will graph the attached. The score would be on the Y axis and the birth date would be on the X axis. This is simple to do by itself but what I would like to do in addition to this is to have the top 25% of the scores a single color, the middle 50% of the scores a second color and then the bottom 25% of the scores to be a third color. And if it is possible to have the ID visible when you move your cursor over a given dot in the graph. Currently when I make a scatter graph the X,Y coordinates show when I hoover the cursor over a dot.
How can i make a graph that will graph against time? lets say i have a bank balance like this:
1/2/2000 $500 1/3/2000 $600 3/12/2000 $400
there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.