Automatically Run Macro On Formula Result Obtained From Pivot Table
Jun 26, 2006
I currently have an excel file set up which is refreshing imported data every minute from a server. This data is located on " sheet 1".
This data is the result of another system constantly monitoring (pulling data from) the process at my factory. It consists of temperatures, speeds and other settings.
When a temperature, speed or setting changes then it will be captured by the monitoring system and therefore my excel sheet will load it into the imported data within the next minute.
I would like to be able to capture the change in temperature, for example, if it goes into alarm and automatically send an email reporting this alarm. This alarm is captured in my imported data in a specific column with a 1. If it is out of alarm it is 0. There will not be a report of an item, at any one time, with both a 0 or 1... so there is only one case of any one monitored process.
I have a pivot table set up to filter all the uneeded data out and I currently refresh it manually to show the current status of all alarms. If an alarm occurs on the process, I can refresh my pivot table and it will be indicated with a 1 until it is refreshed again (and the alarm is gone).
So the question:
1. How can I refresh the pivot table automatically every x seconds?
2. How can I automatically send an email on the value change from 0 to 1 in case of an alarm?
View 3 Replies
ADVERTISEMENT
Sep 29, 2007
I have a Pivot Table feeding a chart that needs specific formatting
However, after a Pivot Table refresh, the chart loses any custom formatting and returns to the default formatting.
(I know I can create custom user-defined charts but I don't want to do that here)
What I would like to happen is for a (chart formatting) macro to automatically run after a pivot table refesh occurs. It should only occur on the one worksheet that contains the Pivot Table and associated chart and should run the macro after a refresh REGARDLESS of whether the data within the table was updated or not.
View 4 Replies
View Related
Jan 3, 2014
I have a very large pivot table. 1 output is the sum of seconds it takes to complete a task. I would like to convert this field from seconds to minutes. the data is all in seconds, so i need to divid the sum by 3600. is there a way to do this calculation inside the pivot table?
View 1 Replies
View Related
Dec 18, 2008
I use a macro that automatically generates a pivot table, re-formats the data, and then makes sure there are no blanks and that the DATA area of the pivot table is set to SUM OF and not COUNT OF.
It was working just fine yesterday but today it is giving me the following error and is highlighting this section of my
View 6 Replies
View Related
Jan 23, 2008
I have a sheet that comes from a pivot table that I have to format each month. It's a pain I was hoping a macro might be able to do. I was told this is the place to come for this. I have attached the the sheet unformatted and formatted. Note, there are 2 more columns on the finished sheet I add in to show totals. Also note col A has to be re-ordered each time (Safety, Environmental, Security, Vehicles, Operations).
Not sure about the complexity of this. Let me know. And let me know if I need to supply more info.
View 3 Replies
View Related
Dec 3, 2013
I have a workbook with several worksheets and several pivot tables. Based on the selection from a drop down box on the first tab, I want to automatically filter pivot tables on subsequent tabs. Is that possible?
View 2 Replies
View Related
Nov 22, 2006
I have existing Pivot Tables that have a data source on another sheet that varies in size.
How can I have the pivot tables' data ranges reset to match the number of columns and rows on the data sheet?
View 3 Replies
View Related
Jun 10, 2008
I have a workbook that automatically creates a new sheet based on the current day. There is a pivot table in the workbook that gets values from multiple consolidation ranges.
Right now I have to manually add the ranges from the new sheet every time. I need that pivot table to update with the same ranges from the new sheet every time a new sheet is created.
There is also another sheet (named "Data Results") that needs to update too, but that's not as urgent.
I've searched everywhere on the forums and can't find anything like that. When the file opens, I've written script to have a box pop up asking whether you want to "View Data" or "Begin New Audit." If you click "View Data" nothing happens and you can just view the file. If you click "Begin New Audit" it creates a new sheet based on the current date.
I wanted to attach the file, but it's an Excel 2007 macro-enabled file (*.xlsm).
View 4 Replies
View Related
Jul 3, 2012
I have 2 pivot tables in a worksheet.
The names are "PivotTable1" and "Pivot Table2"
Cell H2 is the worksheet has a currency type, ex) EUR, CAD, GBP
Column 1 in each pivot table is "Currency Pair Sell/Buy"
Based on Cell H2, I want the pivot table filters in column 1 to show 'USD.currency' and 'currency.USD'
For example, if H2 = EUR, the pivot tables filters in column 1 should only show USD.EUR and EUR.USD.
Any template for this type of code, I can fill in the blanks with the relevant sheet names and other information that might be needed.
View 9 Replies
View Related
Jun 15, 2009
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies
View Related
May 16, 2009
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................
View 4 Replies
View Related
Feb 13, 2014
I have a vast amount of data entries in a pivot table (over 100). I want to create a pivot table but when I click on it it says 'count' and not 'sum'. It will take me a long time to change them all, how do I do it quickly? And possibly, without the 'sum of ....' in the label of it?
View 3 Replies
View Related
Oct 12, 2002
Possible to get a pivot table to automatically expand the range selection of data as it grows. I have a worksheet that grows daily and I am running a pivot table from this. If I refresh the pivot table it doesn't pick up new data unless i change the range or i have a huge range selected in the first place. Problem is if i select a huge range then the grouping options i am using won't work correctly.
View 5 Replies
View Related
Dec 6, 2009
This formula tells me the highest number: =LARGE((G36,O36,W36,AE36),1)
The result is AE36. The answer is correct. I get the highest number. I want the formula now shows Y3 instead of AE36.
In my situation Y3 is the name of 1 of my tables. In my case I get from this table, the highest number.
These are 6 cells to the left side. These are 33 cells to the upper part. In the future I will work with more tables. So the answer must always move
I've been busy this few days to investigate. That is why I know it works with offset. Anyway, I still do not know how it goes.
View 5 Replies
View Related
Apr 25, 2014
I have excel result sheet which contains students information. i.e. name, subjects and their corresponding marks, grade, percentage etc. So from that sheet I want to print result cards for each students separately from the data (result sheet).
View 4 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jan 11, 2013
I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
View 1 Replies
View Related
Mar 19, 2007
I have a pivot table having 5 columns, where the 1st 2 columns are the side label (rows) and the last 3 columns are the facts (data). The side labels are named as Sales Executive and Customer Name respectively. The facts labels are Last Year Qty, Current Year Qty, and Business Plan Qty. The data for the pivot table are extracted from the legacy database.
Sales Executive | Customer Name | Last Year Qty | Current Year Qty | Buss. Plan Qty
I want to add computed columns to check the performance of Current Year Qty as against Last Year Qty (vs LY) and Business Plan Qty (vs BP).
Sales Executive | Customer Name | Last Year Qty | Current Year Qty | Buss. Plan Qty | vs BP | vs LY
If I will create these additional columns outside the pivot area chances are it will not be updated in reference to the rows in the side labels especially when there’s a new entry in the Sales Executive or Customer Name columns. Is there a way in which the “vs LY” and “vs BP” columns would automatically be updated when I refresh the data in the pivot table? Can it be done inside the pivot table? How will I do that?
View 4 Replies
View Related
Feb 9, 2008
I wanted to know if there is a way you can add a formula to pivot table. In the attached file, i'd like a row in the pivot table which should be (sum of number 2)/(sum of number 1) for each color under the fruit. ie. the row that is in column E should actually be a part of the pivot table itself. is this possible ?
View 3 Replies
View Related
Jan 23, 2012
I have a spreadsheet containing 2 sheets. In cell A3 of sheet 1 I have a formula that generates either a blank or "1". In sheet 2 I have a macro called Index which I would like to run when the formula in A3 displays "1".
Having done some research I know that the worksheet change event does not work with formula changes so have tried the worksheet calculate function but to no avail. I have done a lot of searching but cannot find what the problem is.
View 6 Replies
View Related
Jan 5, 2013
Can we apply any formula in the pivot table?
View 1 Replies
View Related
Oct 23, 2013
I have a range of cells that totals different data that we use for reporting. I set up a couple of pivot tables and charts so people could break down work to what they need,
The problem I'm having is that the cells are updating but the pivot table still shows the data from last week when I set it up. I need it to update so how can I do it?
View 4 Replies
View Related
Jul 25, 2006
Another question about pivot tables. In Excel it is possible to show different kinds of information in a pivot table like sum, total, min, max, average and so on. I want to create my own formulas in my pivots. Is this possible and if so how?
View 7 Replies
View Related
Jul 28, 2006
I have two questions about dealing with formulas in pivot tables and how to make them dynamic. First let me give you some further background.
My database countains the following variables: Country, Company, Period, Product, Category and Sales. Out of this database i created a pivottable which sums the total sales amount for each company in every period. Further selection on country and category is possible in this pivot table. See my attached file!
I want to express the sales of each company as a percentage of the total sales in that period. For example for company A in period 1 their total sales was (929/3172) 29,3% of the total market sales. Which formula do i have to use in my pivot table to express company's market shares?
Further my pivot table can be specified more detailed by country and category. I want to be able to select on country and category in such a way that the company sales are still expressed as a percentage of the market sales. In other words how can i make my pivot table dynamic?
View 6 Replies
View Related
Jan 4, 2010
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies
View Related
Mar 14, 2013
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
View 1 Replies
View Related
Jan 22, 2007
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies
View Related
Jul 25, 2009
I have a pivot table macro below. It works great:
View 3 Replies
View Related
May 9, 2008
I am testing the code below...
Sub applyCombination()
Dim shtP As Worksheet
Dim j, k As Integer 'j = row; k = col
On Error GoTo errorhandler
Set shtP = Sheets("Sys-Year")........
k = k + 1
Next k
j = j + 2
Next j
Exit Sub
errorhandler:
Exit Sub
End Sub
and, as it is a bit slow to paste a formula in each cell, I was wandering:
Is there any way I can place the resulting value ONLY?
(I don't want to do copy and paste special just values as it takes even more time)
View 9 Replies
View Related
