Graph Needing Range Adjustments Dynamic
May 9, 2008
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.
View 9 Replies
ADVERTISEMENT
Apr 28, 2008
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.
View 9 Replies
View Related
Jul 28, 2008
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.
View 9 Replies
View Related
Aug 6, 2006
i have a problem that i have been trying to get over for about a week now.
i need to calculate a lease commission, with an extensive amount of variables.
first i need to find the length of the total term which should be anywhere between 1 to 10 years.
then on a annual basis i need to define how many months are billable in that year.
which gives me to variables to account for there, which are
A= initial free months, non paying
B = the last month of last year may only be a half year
i think i have worked that out pretty successfully, so next i need to calculate the rent for each year period. with several variables
a= the rent can be caculated :
-by per month basis
- by annual basis
- by a per square foot basis
b= next in relation to annual rent operating expenses may also be calculated in the annual rent number also by the same variables, however it may or not be calcuated into the number depending on the lease.
c. this is where i am at now, and its killing me. i need to account for rent adjustments for each year.
rent adjustments can start from either the lease start date or the date that rent starts which would be after the lease start if free rent is granted.
then the adjustments will continue through the end of the term and be implimented every x number of months.
the value of the adjustments will either be a percentage of the first years rent usually 3-5 %
or per sf, per month, or just flat rate per year. but it will escalate each year.
for example year 5 is x amount of ajustment from year 4.
i am finding difficulty in finding an annual value of the original lease term in relation to this date series. expecially if the adjustment periods leave a remainder carring over to the next year, or if their are several adjustments in one given year.
any help would be appriciated on this.... i know its pretty complicated, and i have rewritten this code about 30 different ways , i am at a loss right now.
if you think you may want to see my file let me know and i can post it
View 9 Replies
View Related
Jun 30, 2014
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.
Graphseries.xlsx
View 4 Replies
View Related
Jan 13, 2014
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.
Example.xlsx
View 3 Replies
View Related
Oct 3, 2011
Is there a way to create a dynamic chart to automatically run from the most recent Monday to Sunday. So on Monday the entire chart will reset?
View 9 Replies
View Related
Apr 25, 2008
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.
View 3 Replies
View Related
Aug 6, 2008
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.
View 2 Replies
View Related
Dec 4, 2008
i need to create an IF and an And formula for the debit columns and credit columns of an income statement and a balance sheet on a 10 column worksheet. The formula for each column will be slightly different but will be the same for every row in the respective column. the formulas must check to see if the account number is associated with an asset, liability, or owner's equity. this will determine if it is a credit or debit balance. my basic goal is to enter all values in unadjusted trial balance and adjustments, while all the other columns fill in automatically.
View 9 Replies
View Related
Jul 24, 2014
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 using Office 2007.
View 1 Replies
View Related
Jan 7, 2014
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)
Graph.jpg
View 2 Replies
View Related
Sep 21, 2007
I am trying to use an IF Condition for multiple options and have forgotten how to make it work. Here is my basic formula:
=IF((B20="CHASE"),SUM(C20:I20))
I need to have several other options in B20 to "CHASE", like AMEX, DISC, BOA and CASH, so I need the B20 result to reflect which of those conditions exist in order to find the sum of C20:I20. I cannot seem to get any configuration to work.
View 9 Replies
View Related
Apr 7, 2014
I want to create a dynamic line graph using week and year numbers stated in another sheet.
e.g.
Start Year - 2012
End Year - 2014
Start Week - 3
End Week - 12
The top 2 rows above my graph data are as below:
Year - 2012 2012 2012
Week - 5 6 7 etc.
This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.
View 7 Replies
View Related
Jan 30, 2008
I've been playing around with the API functions (specifically, InternetOpenURL) to retrieve the contents of an HTTP webpage and store the information within a text file, using Excel VBA. It works great for any "normal" webpage not requiring a "Username" and "Password" login. I can't find any examples on how this is done for a webpage requiring such a login? I've heard you can do this via sending the appropriate cookie to the server, but I can't find any documentation on the web? Does anyone have any idea on how this functionality is achieved?
View 9 Replies
View Related
Dec 22, 2009
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
View 9 Replies
View Related
May 14, 2009
I have a table with subtotals that I need to find the largest value for the subtotal results and then return the cell contents for the corresponding row.
I have attempted to use the hlookup function, but keep getting a #ref error (probably because I am just not that familiar with the entire formula requirements).
I attempted to nest in the 'largest' function to the lookup function, but have so far been stymied....
View 14 Replies
View Related
Sep 8, 2006
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.
View 4 Replies
View Related
Feb 1, 2010
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.
View 9 Replies
View Related
Oct 16, 2009
Is it possible to plot an xy scatter graph of "Result A" versus "Result B", and have the data points labelled with the values under "Mix" ?
Mix Result AResult B
1340.543 0.520
1360.562 0.525
1380.570 0.526
1400.561 0.528
1410.559 0.526
1420.568 0.526
1440.570 0.526
1460.578 0.526
1470.579 0.530
1480.575 0.529
View 2 Replies
View Related
May 13, 2014
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.
View 4 Replies
View Related
Sep 4, 2006
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).
View 5 Replies
View Related
Apr 3, 2008
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
View 9 Replies
View Related
Jul 15, 2014
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.
View 1 Replies
View Related
Oct 28, 2009
I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:
=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))
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.
View 3 Replies
View Related
Jul 23, 2008
I need to select data from columns A, B and I for the graph. I have the below code but get an error message. Can anybody help ?
Set cellGR1 = Cells(2, 1)
Set cellGR2 = Cells(K, 2)
Set cellGR3 = Cells(2, 10)
Set cellGR4 = Cells(K, 10)
Charts.Add ....
View 9 Replies
View Related
Aug 21, 2008
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 ......
View 9 Replies
View Related
May 28, 2008
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?
View 7 Replies
View Related
Sep 13, 2007
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.
View 9 Replies
View Related
Aug 5, 2013
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
View 5 Replies
View Related
