I am wanting this work book to do is have a table of categories update automatically when a category is selected in the "category column". The 3rd cell to the left of the category column "Gross column" is where the data is which needs to be sent to the corresponding category in the table and utomatically update. I have attached a sample workbook.
View 4 RepliesBasically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
Category
Genre
Title
Author
Year
[Code]....
I'm trying to code so that any changes made to Excel document will automatically change in PowerPoint. I have Copied and Pasted Special link so that its connected but when ever a change is made in Excel in powerpoint I get a pop up box saying I need to press update. But I want to bypass the pop up update box and have it so that powerpoint automatically updates when I change something in Excel.
This is the code I have so far. I don't know if it's right or if I am on the right track as I get a compile error saying I need a End with.
[Code] .....
I have two text boxes on a userform that are used to populate a label caption based on the textbox values. How do I set the label caption to update once a a textbox value is changed?
View 2 Replies View RelatedI have 3 pivot tables on the same page - they all have the same PAGE and ROW fields, just different column and data fields. (may seem weird, but I'm producing reports for non-Excel people and this seems the only way to display info in user-friendly way).
Problem: If a user changes the selection in a Page field, I need the other 2 pivot tables to automatically select the same Page field, so the pivot tables are still all like-for-like.
I've started writing a macro which I can run to do this - below - but
1) I need it to run automatically when any of the page or row fields are updated
2) my macro is probably far too basic for my needs - one of the page fields has 245 values, so the below isn't really going to cut it!...
I have an Excel sheet which has Category Column, I have set an auto filter for it, When I click a particular Category data pertaining to that Category is visible.
But my problem is I need help (VBA), that loops category autofilter, through each Category item and capture( sum number of items in that particular category) and put it in an other excel sheet. Kindly Help.
I have a macro to create a report and it selects to place in the pivot table. The problem is that sometimes in my basic data for creating the pivot table, not all the same fields are there. Here is what it looks like for this section now.
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Category Id")
.PivotItems("ARMS/AMMO/EXPLOSIVES").Visible = False
.PivotItems("COMPUTER").Visible = False
.PivotItems("COUNTERFEIT GOODS").Visible = False
.PivotItems("DRUGS").Visible = False
.PivotItems("GENERAL MDS/OTHER").Visible = False
.PivotItems("PROHIBITED ITEMS").Visible = False
End With
In the instance for today, the field "Drugs" is not in my main data. So I get an error box and it stops because the category is not there. How can I get this to continue if one of the fields is not found?
I am using a pivot table to summarise information with a list of tonnes and sources from civic amenity sites. All was fine until I got to this month to do monthly reports. The pivot table appears to be treating what appears to be exactly the same categories of "CIVIC" and "TRANSFER" differently. By this I mean that although the values in the column appears to be "CIVIC" or "TRANSFER", some get treated differently in the pivot table making it pretty useless. I have made sure that all spellings of the words are the same. there are no spaces before or after the word e.g " CIVIC" but to no avail!
View 4 Replies View RelatedI have this VBA code running in cell A1 which gives you a yes/no message box when you try to change the selected cell. I am trying to have the Target.Address = 'Specific Header Cell in Table' but I am not able to develop the code to do so. Here is the code:
[Code] .....
The main reason I have this message box is to prevent the user from changing the cell in the table because the titles in the tables are linked to macros. If they are changed, the macros will not run. Also, I am aware there are codenames for the excel sheets in a workbook. If there are codenames for the header titles in an excel table, I could link my macros to the codenames. As a result, I would not need this macro for the message box.
My problem this time is in regards to auto sorting a table as data is changed in another worksheet and is auto copied within the said table.
I.E. I need the Fax page (second last), the table has to auto sort based on column " C ". This is easily done using custom sort but can it be done automatically ? I want to lock the page so nobody modifies it.
I attached a copy of my work so far, using Excel 2007.
All the sheets are locked except " Player DATA " and " Fax ".
The password is " Moose ".
I have two data tables (12 columns each, 60 rows each) in the same worksheet. Ideally, I'd like to conditionally format the 1st table to show if any values changed from the 2nd table.
I see that I can conditionally format each cell to compare to its respective cell in the 2nd table, but would rather not do this 720 times.
Is it possible to always print out a specific pivot table even if changes/selection and range have changed? What I have right now is a button that prints out the pivot table (assuming the range is not changing). But if a user changes selection the button prints only a part of the pivot table.
View 3 Replies View RelatedI have a code that i want to run after an item in a page field on a Pivot table is changed. I have put the code in the PivotTableUpdate event. However I would like the code to run only if the user changes an item on a specific page field i have set up on the pivot table layout.
So, if I have 2 page fields on my pivot table pf1 and pf2 :If the user changes an item in pf1 the pivot table data changes but my code within the PivotTableUpdate event doesn't runIf the user changes an item in pf2 the pivot table data changes, my code detects this page field has changed, and my specific code runs.
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
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.
I'm new to Excel Macros and have hit a brick wall on what I would consider
to be my primary workhorse macro! I'm embarrassed to admit that I haven't
mastered coding for movement of data around the spreadsheet much beyond
the macro recorder. I need something tight, efficient, and (instructively commented)!
I've got a spreadsheet consisting of a Master table with 42 rows, (2 rows
per record), and 14 columns of information. To the left of that is another
table consisting of live data from the process. It has anywhere from 1 to
24 rows with 10 columns representing Current/Live/Pre-processed information,
pulled in from the company web location.
Both tables start with an indexing column of 2 character alphanumeric IDs.
They are as follows: C2, D3, D4, E3, E4, F3, F4, G3, G4, H3, H4, I2, I3,
I4, J2, J3, J4, K1, K2, K3, K4.
MASTER TABLE Index Column is B4 to B44 (2 rows per record, in A->Z order)
UPDATE TABLE Index Column is Y4 to Y24 (Could be from 1 to 21 rows (Max),
1 per record, normally in A-Z order.
What I need to do is Key on the Master index column for a particular ID,
against the UPDATE index column. When there's a match I need to take the
updated values from the UPDATE table and assign them to the appropriate
cells locations in the MASTER table.
Example:
Starting with ID "C2" in the MASTER table, check to see if "C2" exists in
the UPDATE Table Index. If there's a match the following cell value
transfers must occur. Z4 -> E4, AB4 -> J4, AC4 -> J5, AD4 -> H4,
AE4 -> K4 and AG4 -> I4. If "C2 is not found in the UPDATE Table then skip
to next ID, (D3).
If ID D3 is found, another cell value transfer must occur. Z5 -> E6,
AB5 -> J6, AC5 -> J7, AD5 -> H6, AE5 -> K6, and AG5 -> I6. If "D3 has no
updates then skip to next ID and so on. Continue checking IDs and updating
until all IDs have been checked.
I'm having difficulty crafting a macro to search 2 columns against one nother,
specify the correct ActiveCell, than offset appropriately to place the desired
value in the correct location.
I'm trying to update a query table in excel, but it keeps giving me the error 1004
Worksheets("Data-All").Range("A4").QueryTable.Refresh BackgroundQuery:=False
Cell a4 is in my query table.
I have a table recording details of attendees of courses. There are common and variable entries columns) for each attendee (row)
Common: Venue, Date, Teacher, Course.
Variable ( and specific to attendee): Role, Place of work,
So if there are 40 attendees then currently I have to enter the common fields for all attendees 40 times !!
Is there a way of having input boxes/ user form which asks for the common fields and then automatically updates the table?
When I tried to Refresh the Pivot Table, the message I got was: The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. if you are changing the name of a PivotTable field, you must type a new name for the field. I did go in a check that all the columns are named though.
View 1 Replies View RelatedI've created a pivot table that summarizes sales and gross profit on a quarterly basis and YTD basis by Market and store. I have a drop down in my source data file which changes the month. I go to update the pivot table and it no longer recognizes the data fields because the headings change to the subsequent month. Is there a way to not have to recreate the pivot table every time a run a new period? See below. Once I run August and then refresh the data it will not recognize these fields any longer.
View 9 Replies View RelatedCell A10 is set up to autocomplete based on the cells above it. Cells B10 and C10 are set up to use VLOOKUP to gather information from the table above based on the name in A10. What I would like is for C10 to autopopulate with the information in the table which corresponds to the name entered in A10, which it already does. What I would like to do after that is be able to change the data displayed in C10, and have it update in the cell in column C which corresponds to the name entered in A10. What I'm trying to do is create a customer manager. The customer's names will be hidden, as well as all their information, but I want my sales guys to be able to pull up an individual customer's information, then enter notes on that customer (column C). Vlookup won't allow you to enter information, as changing information erases the vlookup formula in the cell. This is very difficult to explain without direct visuals. Is there any way to do this?
View 9 Replies View RelatedI have a spreadsheet with 3 tables (all side-by-side). Table one has 8 rows of data; table two has 2 rows of data; table three has 3 rows of data. I am looking for assistance on how to sort each table in desceding order (by sales). Basically I want to do a sort everytime there is a change of values in any of the tables. I have attached a sample spreadsheet to clarify the table layout and the manner in which the values change within each table.
View 2 Replies View RelatedI have a data table that looks somewhat like this a number of rows of "Date", "ID#" and other extraneous fields.
I have a pivot table that simply lists the total count by date, then i grouped it by month and then by quarter:
Date4 Date2 Date Count of ID 2008-Q4 76 October 2008 29
10/1/08 8
10/15/08 19
10/21/08 1
10/31/08 1
November 2008 25
11/1/08 2
11/15/08 19
11/21/08 1
11/29/08 2
11/30/08 1
December 2008 22
12/1/08 1
12/15/08 19
12/21/08 1
12/31/08 1
My problem is, when i add a new row to the main table, say with a date of "December 20, 2008", and refresh the pivot table, the information ends up clear at the bottom (in this case, after the year 2014).
I figured the pivot refresh would place the data among the other December 2008 rows in the pivot table.
I have a pivot table on a hidden sheet and a combo box the user picks from. I would like a macro or formula that could change my pivot table Page Value to that which is chosen from the combo box. Then update the pivot table. I can then use my report to look up values from this pivot as needed.
View 3 Replies View RelatedI have one "master" spreadsheet I utilize per month. Each month my "master" spreadsheet is updated twice a week (so, for a four week month I update my "master" spreadsheet a total of eight times).
To update my "master" spreadsheet I compare it to an "update" spreadsheet that contains the same column headings as my "master" spreadsheet.
What I need updated:
I need to compare account numbers (column A) between the two spreadsheets, once I find a match I need to update each of the costs for the 5 products (columns B thru F) from the "update" spreadsheet to the "master" spreadsheet. I currently have a simple SUM(IF) statement that completes this for the first update.
I am lost at how I can have my "master" continually update for the next seven updates without losing each of the previous updates.Any help on this would be greatly appreciated.
I am attaching two sample spreadsheets - "master" and "update".
I created a Pivot Table with employees summarized or total hours, which also totals up their billable charges. I would like to "read" the pivot table & break it down to the employees weekly or monthly hours & billable.
I was going to use the following function, but can't use because there is not a column that finds the employee's name several times:
=SUMPRODUCT(--($B$7:$B$4995>=AN3),--($B$7:$B$4995<=AN4),--($B$7:$B$4995=$G$7),--($D$7:$D$4995))
From Pivot Table:
Employee A 56 $7,280.00
XX 56 $7,280.00
07/17/2013 3 $390.00
07/18/2013 9 $1,170.00
07/19/2013 4 $520.00
07/22/2013 9 $1,170.00
07/23/2013 4 $520.00
07/29/2013 7 $910.00
07/30/2013 4 $520.00
07/31/2013 3 $390.00
08/07/2013 9 $1,170.00
08/08/2013 4 $520.00
Also, I was going to upload an example & removed some confidential content from the original log file in which the pivot table(s) came from but don't see the pivot tables updating or giving error messages from lost data.
How does that pivot table update then? I hope I don't have to re-create it again.
When I update the pivot table with new data, the column where I have some percentage numbers the formatting changes. One number goes back to standard. The new numbers that enter the column also enters as standard numbers. How do I get the column to stay in percentage numbers?
View 9 Replies View RelatedI'm using a total of 20 Rows and 2 Columns. Each row has Column A for Description and Column B for Score. There are total 10 subjects with 10 noneditable rows, and 10 editable rows.
I'd like to protect column A and B for rows 1 to 10 and unprotect only Column B for rows 11 to 20. When any cells from Column B, rows 11 to 20 updates, I'd like to automatically sort columns A+B for ONLY rows 1 to 10.
Therefore, rows 1 to 10 need to be sorted based on the scores on Column B, and since rows 11 to 20 are just input fields, they are never to be sorted.
Column B for Rows 1-10 will be formulas and are based on Column B values from rows 11-20.
When a value in any of the rows 11-20 of Column B is updated, Range A1:B10 will automatically sort from highest score to lowest score (Column B).
Since Column B for rows 1 to 10 contains formulas and not actual values, would it throw off the order of things when sorting formulas?
the macro coding to perform this task.
Here is just a visual of what the spreadsheet will look like
....A B
=======
1||A (B11)
2||B (B12)
3||C (B13)
4||D (B14)
5||E (B15)
6||F (B16)
7||G (B17)
8||H (B18)
9||I (B19)
10||J (B20)
11||A 10
12||B 9
13||C 8
14||D 7
15||E 6
16||F 5
17||G 4
18||H 3
19||I 2
20||J 1
The Bold are editable fields. and I want to use auto sort A1:B10 based on updated values of B11 to B20.
I have also attached the basic excel file that situates my concern.
I have a spreadsheet that has a top row of weeks ranging from 1/1/2012 to 1/1/2015
I am making a pivot table to show a sum of each column by user (in column A of the source). My problem is this, I need to show the dates and there related values in the top row of the pivot table, and the users as the rows. My problem is two fold - how to display dates across the top row of the pivot (only when a value exists) and then how to make sure the value is a sum rather than a count.
I've written a Fantasy Football programme, which is 100% working, however I would like to have the highscoe table update automatically, rather than using the data/sort command. E-mail me and I'll sent it to you with what I'm trying to do.
View 2 Replies View Related