Macro-generated Values Not Updating Charts And Sums
Jul 5, 2006
The question is pretty much described in the post title. Here is the situation:
I have a workbook that has data plugged into it manually throughout many sheets, and at the end there are a few sheets with different graphs and charts that are generated by a macro. This macros works perfectly - all the data is accurate and the figures are perfect when compared with a manual count. The problem I have is that the cells where the macro puts the summed values are in a chart that is supposed to create a bar graph. Normally, if I change a value that a bar graph uses I see the bar graph change - but that does not happen with the cells that have values placed in them by the macro. I also have a section that sums the values in the chart to make sure it's running right, and these values do not update automatically as well. The cells have the formula =SUM(B3:B12) and so on, in them. Right now, every time that I run the macro I need to go into the cell with the formula, hit 'enter', and then it does the summation. Is there a way to correct this problem, or is this something that is part of excel?
View 4 Replies
ADVERTISEMENT
Nov 2, 2013
I'm trying to dynamically populate ranges to facilitate dynamic charts being generated.
I use excel 2010 at work, and 2011 for mac at home.
Dynamic chart ranges populated from named ranges as selected in nested indirectly sourced validation lists
I want any selection made in a dependent validation list which contains a list of named ranges to trigger a worksheet_change event which copies the range the selection points to and pastes it into a dynamic range in another column, beginning as a specified cell.I've tried using this, put together from some code examples from similar, but different issues.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
Range(Range("B2").Value).Copy
Range("P2").Paste
End If
End Sub
Trouble is, I don't really understand this code. It doesn't appear to do anything when I make a worksheet change in "B2", but I don't know exactly what it is. I suspect that perhaps the fact that "B2" is validated from an indirect source might be difficult?
View 8 Replies
View Related
May 23, 2008
I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....
What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)
As a lot of the time I use the same number range but on different rows and sheets.
View 12 Replies
View Related
Sep 8, 2006
I have a worksheet containing 16 chart objects. The source data for each of these charts is located on various other worksheets (all within the one workbook however). Whenever I change data on my Data Input worksheet, the source data is correctly being recalculated, however, the associated charts aren't changing.
The only two ways I can get the charts to update are: (1) save and re-open the file (saving doesn't fix it but re-opeing the file appears to do a full recalculation which then correctly updates the charts); or (2) click on each of the charts individually, go to Source Data and re-accept the data range that is already in there.
I've used the following VBA on my Data Input worksheet to try to do a full spreadsheet recalculation every time I deactivate it but this doesn't solve the problem either.
Private Sub Worksheet_Deactivate()
Application.CalculateFullRebuild
End Sub
I've also tried CalculateFull and just Calculate in the VBA but to no avail.
My calculation is set to automatic.
I don't know if its relevant or not but this spreadsheet uses a custom worksheet function to derive some of the source data. I've never come across this problem before. Does anyone have any ideas on why its doing this and more importantly how to fix it?
View 9 Replies
View Related
Jan 19, 2013
The charts I received have many Series formulas that reference and external spreadsheet
Is there a way that I can modify them and reference the same sheet name but locally
For your information
When selecting menu Formulas and then "Show Formulas" chart data series do not display
When selecting a Chart the "Show Formula" selection is not available (at least I did not find it)
In addition, when selecting the Chart, Then Design, then "Select Data", it replies in the pop-up with the comment "The data range is too complex to be displayed...."
There are about 40-60 formulas as shown below!!
The sheet name with data is D_Brand_Q
HTML Code:
' here is a sample of the current formula
=SERIES('C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$1,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$E$197:$E$218,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$197:$CC$218,2)
' This is the desired formula
=SERIES(D_Brand_Q!$CC$1,D_Brand_Q!$E$197:$E$218,D_Brand_Q!$CC$197:$CC$218,2)
View 1 Replies
View Related
Aug 28, 2012
I have just been asked to update a file on a quarterly basis which contains a large number of charts based on the performance of various funds against their respective benchmarks. In column A I have dates going back to 2008 and then the fund and benchmark figures in the next columns. Each quarter there will be a need to update the charts to the new rolling 3 year period, i.e. in October when I update the file, the charts will need to show the results from 30.09.09 to 31.09.12
As there are 20 charts this can be quite time consuming? The charts sit on the same tab as the data, under the columns at the bottom.
View 2 Replies
View Related
Apr 30, 2008
For some reason, the charts in my excel workbook do not update automatically when the data are changed. I have set Calculation to Automatic, but it still doesn't work. They update only if I close and reopen the workbook. Is it due to a problem with setting? How can I get the charts to automatically update?
View 4 Replies
View Related
Dec 19, 2008
Need to write a formula which sums the values in a range only to the date we mentioned. For ease sample date & requirements are highlighted in attached spreadsheet.
View 3 Replies
View Related
May 10, 2009
Ok what I am trying to do is lookup a value in a table (kinda like one below but alot bigger). What I plan on doing is creating a drop down list for the rows that show "A, B, C etc) and then another drop down for (AA, BB, CC etc).
So lets say the 2 drop-down list are set to C and BB
I want to be able to fill another cell with the value -134
I would use If statements but like I said the table would be alot bigger than example. If possible I would rather not use VBA due to work security settings.
a b c
AA-110-110-129
BB-115-115-134
CC-117-133-136
View 3 Replies
View Related
Oct 10, 2009
I have a formula utilizing a random number generator that produces a new number every time I hit the F9 button. I want to accumulate 1,000 values for these numbers, but I don't want to take the time to write down each number (copy and paste). I would like to simply hit F9 and have the value stored in a cell that then steps down so when I hit F9 again it records the new number, so at the end of the sampling, I end up with a column of 1,000 numbers
http://www.triplescreenmethod.com
http://www.twitter.com/triplescreen
View 9 Replies
View Related
Jan 14, 2009
I am trying to write a function that sums the values in a range in excess of 2. For example, if the cell is 3 add 1, if it is 5 add 3 etc.
Here is what i have so far, it compiles with no bugs, but it does not work in my worksheet. I wrote it in Sheet 1 (VBA) of the worksheet i am using it in.
View 2 Replies
View Related
Aug 6, 2007
I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View 9 Replies
View Related
Dec 31, 2013
I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:
Counter
Column 2
Column 3
[Code]....
Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.
What I would like it to do is if there is a blank value in any column remove column one from the denominator.
Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.
View 3 Replies
View Related
Jul 7, 2008
Create a smart macro which will remove all references to the #REF! which is left when rows are deleted. It would have to remove all trace of it from any equation it may be in (i.e. if it was in an averaging equation, it would need to remove the preceeding comma as well:
=average(a1,a2,!#REF!,a4 .........)
View 9 Replies
View Related
Mar 16, 2009
I have a macro that looks at a list of students and the school they attend, and then creates an individual worksheet for each school containing only their students. I want to hide Column B on each of those worksheets. This seems like it should be easy, but it is not working.
When I leave it as is pasted below, Column B does not get hidden.
When I change it to Sheets(school.Value).Columns("B").Select I get an error message stating "Select method of Range class failed."
Sub ExtractSchools()
Dim wsTransfer As Worksheet 'worksheet with transferred data from registrations wrkbk
Dim wsList As Worksheet 'worksheet with list of students
Dim wsNew As Worksheet 'worksheet being added for a school
Dim wSheet As Worksheet 'name to loop through all worksheets
Dim rng As Range
Dim school As Range
Dim rowNum As Integer
Set wsTransfer = Sheets("Transfer")
Set wsList = Sheets("Student List")
wsTransfer.Select
Range("Database_Transfer").AdvancedFilter xlFilterCopy, Range("Criteria"), _
wsList.Range("Database_Unique").........................................
View 9 Replies
View Related
Nov 18, 2008
when I run a macro, it takes me to a different sheet, clicks on a cell, and then goes to data--> form to automatically generate a form so I can make a new entry. I want the macro to wait until I have completed the form, and on completion to do something else. I'd prefer it if I could indicate completion of the firm by just a keystroke, but a mouse click will do as well.
View 2 Replies
View Related
Apr 29, 2014
I have a macro that copies rows from my DAILY OCCURENCE sheet that have a YES in them to my MANAGER SUMMARY SHEET and G1 is selected which has a hyperlink that opens a new email with the manager's email address and Subject pre-assigned.
I then have to manually go back to the newly generated summary sheet and select the cells and paste them into the email that was just opened.
Is there a way that the generated rows are already copied in memory and all i have to do is CTRL+V into the new email, or even better, the cells are pasted in the email automatically? So basically either the information being copied over stays in memory, or after it is copied into the summary sheet it is copied again. I just dont know how to copy unpredictable ranges generated by macros.
The code that generates my summary sheet for my manager is below.
[Code] ...........
View 4 Replies
View Related
Feb 6, 2007
I have created a dynamic chart using defined names, and the cells in the range are updated on a monthly basis. However these cells are copied from another sheet using a formula i.e =sheetname!$cell_no. and until that data is entered they have 0 values - my question is, is there a way to suppress the 0 values which are being 'plotted' on the dynamic chart until they have appropriate values?
View 4 Replies
View Related
Jul 1, 2013
I have three sheets of data which require different values. What I'm looking for is for the other two sheets to be updated when I change the values in the field 'Option_Group_IDs' in the productdata-army-to-merge.csv file.
The two fields in the other two sheets to update are the optGrpID filed in the optiongroup-data-army.csv and the optGroup field in the optiondata-army.csv
productdata-army-to-merge
*
A
B
C
1
Remove_Product
Additional_Section_IDs
Option_Group_IDs
[Code] ..........
View 1 Replies
View Related
May 10, 2014
I need to have two area charts combined, but they have different x and y values. Is there another way to do this.When I try to combine them in one graph, I can only use one x-axis value.
In the excel sheet the PV needs to be the x-axis.
View 1 Replies
View Related
Apr 1, 2009
Is there a way to limit the range of values on the Y-Axis on the lower end? Like I don't wish the Y-axis to begin from 0 but say 500. Is that possible?
I have a set of data values ranging from 650 - 850, but the Excel graph however looks too fine because the Y-axis begins from 0.
View 6 Replies
View Related
Sep 28, 2011
I would like to know how can I plot my x axis of a chart with the maximum and minimum value that I have in this variable. Note that my variable is no going from the lower value to the highes one, it is random. So what I did is to use the autoscale of the chart, but it is not working as I would like. Any solution?
The code that I use is:
Code:
' TO GET THE INITIAL and FINAL VALUE and the STEP
Worksheets(wsheet(27)).Activate
ActiveSheet.ChartObjects(chartName(11)).Activate
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnitIsAuto = True
xScaleMin = ActiveChart.Axes(xlCategory).MinimumScale
xScaleMax = ActiveChart.Axes(xlCategory).MaximumScale
xScaleStep = ActiveChart.Axes(xlCategory).MajorUnit
'After I use them (xScaleMin,xScaleMax,xScaleStep) for several charts, in this way:
ActiveSheet.ChartObjects(chartName(11)).Activate
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text =VarName
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Select
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 11
ActiveChart.Axes(xlCategory).MinimumScale = xScaleMin
ActiveChart.Axes(xlCategory).MaximumScale = xScaleMax
ActiveChart.Axes(xlCategory).MajorUnit =xScaleStep
' but if my data was between 50 and 250 it is plotting between 0 and 300
View 2 Replies
View Related
Aug 20, 2003
Negative or zero values cannot be plotted on log charts!!
Some of my log charts will have negative or zero values at certain input variable combinations, however, I'm obviously not interested in those charts when this is the case.
View 6 Replies
View Related
Aug 4, 2014
I am trying to copy a set of data (lets say workbook 1) into a new workbook(workbook 2). Both files are saved in the same directory.
Workbook 1 will be continually updated with new row additions and some changes in text in the rows. I want to copy these new changes (from workbook 1) without having to copy and paste the new additions into workbook 2, as it is wasting a lot of my time.
Is there a trick in excel to automatically do this?
View 2 Replies
View Related
Sep 3, 2008
I am having trouble writing a code for something I need to do. There is a workbook with a worksheet for each month. At the start of every month a new worksheet is added. The opening data in this new worksheet is coming from the closing data of previous month, and some of this data is modified as the month passes.
So, in a way, some of the data for each month depends on the data of previous months. If there was an error in one of the cells, for say, January, and it was corrected manually, it will affect the cell values for feb, march, and so on. Currently, its all a manual process - from copying previous month's data to a new worksheet, and manually correcting errors in each worksheet, which is error -prone and a time consuming process. Is there any way to link each month's data and create an "update values" button clicking on which will update the values for cells in every month's worksheet following the one which had errors in it and was manually updated. It has to one directional.
View 9 Replies
View Related
Jun 19, 2014
I'm using this to update a range of cells after an automatic copy and paste procedure;
Code:
Sub UpdateBtoW()
On Error GoTo HandleError
Application.Calculation = xlCalculationManual
Sheet74.Activate
Dim cell As Range
For Each cell In Range("B1:B50000")
If Not IsEmpty(cell.Value) Then
If cell.Offset(0, 14).Value = "" Then
[code].....
What it is supposed to do is look in column B and find any non-blank cells. If it finds one, it should check the following and update column W as necessary;
1) Column B shows 1, column P is not empty, column W is empty - UPDATE COLUMN W WITH 'Letter 1'
2) Column B shows 2, column P is not empty, column W is empty - UPDATE COLUMN W WITH 'Letter 2'
3) Column B shows any value, (not blank), column P is empty, column W is empty - UPDATE COLUMN W WITH 'N/A'
The issue is that it is updating the cells as required, but it then goes on to fill the entire sheet with 44819 in every single cell.
View 4 Replies
View Related
Oct 17, 2005
I have a number of charts (mostly pie) I don't have a legend, but I do have "Show label and percent" selected.
When I add in new data the actual chart updates to show the right sized pie slices... but the values on the labels don't update as well. I can't figure out how to make that happen?
View 9 Replies
View Related
Nov 13, 2007
I am working with a worksheet that has hundreds of named ranges already in-place. When the creator defined these ranges, they defined them all statically. I would like to update each named range to be a variable named range.
I was thinking about doing a massive find / replace in the VB Editor, but I can't seem to find the named ranges. I assume they were defined from within Excel by the Insert > Name > Define method, and not through VBA and that is why I can't find them. Is there any way to simplify this process instead of manually trying to update each and every range?
View 9 Replies
View Related
Apr 23, 2008
I've been working on this chart for a while and can't seem to get my x and y axis as well as my series corrected. I have time and distance but since my distances are small I cant seem to make sense on how best to depict my data. Right now it is shown as data points but would love to show value.
View 2 Replies
View Related
Jul 23, 2012
I want to write a interface program using VB Macro, for updating the Excel Table values into SAP Tables. Is there any macro that can do this work?
View 4 Replies
View Related
