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 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 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 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
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.
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.
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 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.
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?
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 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....
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
