I have a dividend table of 8 cells (vertical) with alternate Date and Dividend amount. Is there an elegant function to add the dividend up to a given date?
i.e. if 23 Nov 09 is the 2nd dividend and I need dividends to this date,
I wold like to summarize values of a certain numbers of enties (eg. 5), on a specific criteria, but discarging earlier entries of the same criteria. An example being:
Entry
Value
Entry A
1
[Code]....
If I would like to summarize the values from Entry A, but firstly do so from number three entry buttom-up, and only the prior five values, how could this be done?
I need have a table that displays only the last so-many (250) elements of a large data collection (table), updated continuously.
View 6 Replies View RelatedI have a table showing the quantity of products that were sold to stores within a particular week. The following data are shown in each column: Distribution centre (DC) ID, Item ID, Item description, Store ID, Day of delivery, Invoice Number, Quantity delivered - see example attached.
I want to sum up the quantities of identical items that were delivered to the same store from the same DC. In other words, the following conditions have to be met: (a) Distribution centre ID has to be identical, (b) Item ID has to be identical, (c) Store ID has to be identical. To give a specific example as per the attached file, instead of having 2 rows for 2 deliveries of 5 + 3 boxes of chocolate to store# 1, I want to have just one row showing that store# 1 had 8 boxes of chocolate delivered, a second row for that store would show that store #1 had 10 boxes of tea delivered to it. I would like the summed up quantity data point along with the Distribution centre ID, Item ID, Description, Store ID, Day of delivery, Invoice Number to be in a new tab, i.e. I want to reduce the table size and do not want the original rows to be in the new table.
Since my table goes all the way down to the last row 65536, I would need the formula/macro to be able to recognise identical DCs/items/stores and then sum up the quantities (there are about 1500 different item IDs). Can you think of a solution??
I have a table with Companies in a column and Countries in the heading row. Now in this table I have an entry "1" against certain countries for each company.
I need to get a concatenated (Company&Country) list, but I need this is all in a single column.
Companies list.xlsx
How do I automatically generate an Entry Number for each entry in a table?
I have a sheet to build up a database of information and a Userform for the data to be input through. Each time the user selects to create a new entry with the userform I'd like to be able to automatically provide a number (i.e. Entry 1 of 12) in the Entry Number field which will then be transfered back to the sheet.
In addition, if an entry is deleted from the sheet the entry numbers beneath the deleted entry will need to update.
Range B9:B58 and I9:I58 data set 1 (worksheet Deal Selection)
Range J5:J18 and L5:L18 data set 2 (worksheet Tables)
Identical tables. Taking worksheet Deal Selection as our starting point column(s) B and I
If the user adds a new supply customer in column B then I would like this new customer to be
Reflected in worksheet Tables column J5 at the end.
And visa versa for column I add to column L5 at the end same work sheet.
I am guess that this could be done by amending the current macro button
Add Supplier and Add Demand on both side(s). but not removing it current functions
I have also added the question to another forum experts exchange as i am have difficult completing this task.
I have recently noticed a problem with a pivot table and assoicated pivot chart. The table has numerous columns from date, location and one entitled route. The route column has a value which is either a number or 'other'. The formatting of the column has been set to general (although the same problem was present under text). The problem is as follows:
When a new row of data is entered where the route is the same value as previous rows it should be grouped together. However when I go into my pivot table and look at the options for route I find the same value repeated twice. ie a route value of 155 will be listed once at the top and then lower down the list. A temporary fix is to rename all values of 155 to x155 and then back to 155. This then has everything grouped again under one value in the pivot table. As soon as new entries are added though the problem repeats itself. Does anyone know why this might be?
I was wondering if there is a simple way of deleting past entries from a drop down list for a pivot table. I have tested my spreadsheet and now want to create a 'blank canvas' for users.
View 3 Replies View RelatedPlease find the attached sheet.
I just want to know whether is it possible to condional format multiple cells based on multiple cell values.
Eg: In the attached sheet,when a driver is standby and the vehicle no column should be empty,if accidently a data entry is done in vehicle no column it should highlight the whole area (ie,from A3 to E3).
How do I stop a pivot table adding data together?
e.g.
1st Nov -100
1st Nov - 100
2nd Nov - 200
2nd Nov -200
I want this but instead get this:
1st Nov - 200
2nd Nov - 400
I need the pivot to split it out,
I'm an Excel newbie using 2010, but have been given a rather daunting task. I have a lot of data organised in a table, something such as:
Column A: Name, Column B: City, Column C: Date Entered
I can obviously filter this table to show the values I need, but I need it to automatically tell me the total amount of people entered per week, per city on a separate worksheet.
I understand I could use a pivot table for some of this, but I want to further automatically manipulate the data once I have the totals (for example, adding a financial value for each, etc) which I'm not certain how to do with a pivot.
So, ideally what I would like is a formula that says total entries in Table 1 that are marked Sheffield in Column B and have a value including or between 01/03/2014 and 07/03/2014 in Column C.
I have attached an example of what i am trying to do. I can work out how to calculate unique entries by putting in a formula under a pivot table, but is it possible to select an option in the pivot table settting which will give this result?
View 2 Replies View RelatedI have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
View 3 Replies View RelatedI would like to be able to bring these results into an output, but without other members of the other team members results been seen by other memebrs of the team
View 7 Replies View Related2013
Current Year
Q1-13
Q2-13
Q3-13
Q4-13
Totals
Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00
[code].....
I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.
I think this might be a programming issue (not sure but I hope it can be solved).
I have three sheets, each containing a table. I'd like users to be able toggle or select rows from these tables for inclusion in a fourth table in a new worksheet.
Unfortunately I have zero knowledge of vba and 0.01 knowledge of macros. I'm attaching a sample of what I'm working with.
In the attached, the "price list," "likely to reference," and "optional" worksheets contain the source tables; I've added a column to the right of each called "include" (where I'd like a user to be able to select a desired row).
The "custom" worksheet is where I'd like an assembled worksheet to live.
I have some data formatted as a table. There are some blank rows at the end that still contain formula.
Every time I sort the data these rows move to the beginning. I have tried only selecting the active rows of data, but the whole lot defaults in each time
How do I sort data with a selected range only (within a table)?
Don't really want a macro as this will need to be used by others
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]
I'd like to check whether all the filters in a pivot table is selected or not.
I am currently using the following code:-
Code:
Count = 0
For i = 1 To Worksheets("WW").PivotTables("PivotTable6").PivotFields("Country").PivotItems.Count
[Code]....
where flag is to flag whether all fields are selected in the pivot field or not.
The problem is the above code is very time consuming.
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]
I have created a Pivot Table to sumarize the monthly sales data.
Is it possible to select just one month and show the YTD sales?
e.g. We are in September, I select August the Pivot Table should show the August sales column and YTD (Jan to August) sales column.
I have to prepare sheet 2-sales for my job and I cannot find the way to fill the price and sales results depending from the three other inputs required of the table.I belive we will need the functions addif, match, & others but still can't make it. Another tag when there is no result it needs to show "No existe producto".
View 3 Replies View RelatedIs it possible to get the current Page Item Selected for a pivot table
I've tried the following functions in VBA, but have had no success
CurrentPageName
CurrentPage
CurrentPageItem
ParentItems
STO-Productivity-Feb0314.xlsxI work in cancer research and I'm trying to find a way to show the amount of studies that staff have in total and their role on the study. I have a data table that contains many fields but the crux of what I'm having trouble with starts with the following:
When I begin with a pivot table I start by just selecting the all of the studies listed in the table and then the two roles that staff can have data or regulatory. Staff can do both roles or just one on a study. I put the roles of data and regulatory into the row labels and the studies that we work on in the values. The problem is that I now only see studies that the staff member works on that meet working on both roles and not the studies that they serve only one role.
UPDATE: see the attached spreadsheet to illustrate further. Ablorh is the employee that I have it focused on and it only shows the one study she has both the regulator and data roles. The 7 other studies that the employee works on in the data role do not show.
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:
1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case
2. the ToC has to change depending on the sheets selected.
I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.
What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
I would like to be able to select several non-sequential rows in a worksheet called "Data" (using a check box or just entering a value in Column A) and then be able to press a Command button to copy the selected rows to another worksheet called "Estimate" at the bottom of a table, and delete the designators in Column A (i.e. deletes the value, or unchecks the boxes) so I can repeat the process again if needed.
View 7 Replies View RelatedI am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.
Sub test()
Dim SelectDate As Range
Set SelectDate = Range("SelectedDate")
If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then
ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Jan"
ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Feb"........................
