Combined VLOOKUP With A Date Range SUMIF From Pivot Table Data?
Feb 13, 2014
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
=IF(ISERROR(VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE)),0,VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE))
View 5 Replies
ADVERTISEMENT
Oct 26, 2009
I have the following vlookup formula in my spreadsheet.
IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))
It works perfectly for my current requirement, but it looks at data in a pivot table which is created from a dataset. If I name the columns in the pivot table so instead of looking at column 67 it looks at a name range will this name range change dynamically when the pivot table is refreshed? If not how can I get it to.
View 2 Replies
View Related
Feb 5, 2014
I have a Takeoff sheet where an estimator enters a part# and the rest of the data is entered on the Takeoff sheet by using Vlookup to retrieve the information from the Products Sheet (there are about 12,000 products with 12 fields for each product). I need to then take all of the parts on the Takeoff sheet, and summarize the data, which is what Pivot Tables are for. The thing I cannot find out, is if this can be done. The machine keeps running out of memory (it has 32g, so I question that, and I tested with a 500 row sheet and got the same result), so I can never get the Pivot Table to actually create, or get a useful error message. Searching the web and this forum tells lots about Vlookup IN a Pivot Table, but not creating a Pivot Table from Vlookup data. I copied the data values to a new sheet to test the data and the Pivot created fine, so I believe the data itself is properly formatted.
View 1 Replies
View Related
Oct 23, 2013
Have a simple Pivot table that i have grouped in 14 day buckets.
Currently, the date range looks like this:
1/21/2013 - 2/3/2013
However, I want the range to show the 'last' date only.
Is there a simple way to do this? I just can not see it in the format cells menu...
View 2 Replies
View Related
May 9, 2014
I would like to compare % difference between date range through pivot table
Date RangeProductAmt
1st weekABC1000
2nd WeekABC2000
3rd WeekABC1259
1st weekXYZ3000
2nd WeekXYZ4000
3rd WeekXYZ2500
How to plot a Pivot table to compare the % difference between 3rd Week Vs 2nd Week Vs 1 Week for each product through pivot table?
View 2 Replies
View Related
Jun 25, 2008
When using a Pivot Table I regularly filter on known data specific to me i.e. WBS codes.
Column ‘A’ contains the WBS Codes which can be as many a 1000, however, I only need to filter out the 10 or so I require, and currently I uncheck all the records and manually scroll through the list to check the ones I require.
In order to speed things up I recorded a macro which works fine, however, If I need to check any new codes I would have to record the macro again, as I tried to edit the Macro and all it does is to un check all the other codes I don't need.
Is it possible to create a Macro that picks up a range of data that is then used to filter on. (i.e. only the records I require)
View 9 Replies
View Related
Sep 10, 2009
I was wondering if any one could guide me how to use SumIf or any other functions while creating a pivot table from excel data in a very presentable manner.
The data shows - sales in qty to various customers for the month of may. Important columns need to be reported are Item, Qty.
On the second tab (in the attached sheet) i am showing the format how i want. I need to sumif by item code and put the total in the respective company column. I could manually do this by SumIf but if I want to do this for month by month for 4 months then how would i do it quickly.
Is there an easy way report for 4 month's comparative figures to make an analysis to see whihc company orders whihc product and the so on.
View 9 Replies
View Related
Jun 19, 2014
I am having a list consisting of two columns. Column A describes a "product" and the currency directly below. In column B i have the quantity of the product and below the value. Now i have products in USD and EUR and would like to only sum up the quantity of all products which are in USD. A simple example is attached and i calculated the target valua manually. The values i would like to sum are market in red.
View 1 Replies
View Related
Oct 29, 2008
i would to combined these together
can this be done
View 2 Replies
View Related
May 23, 2012
I know how to use the large function when looking for a specific criteria as the example below illustrates:
Code:
=IFERROR(LARGE(IF('2012'!$D$1:$D$7="Green",'2012'!$C$1:$C$7),1),"-")
What I'm trying to do is take this one step further and not only find the nth largest numbers in a set of data based on a particular criteria, but also sum those numbers because they repeat in a table: for example a sample table is below:
Account Accout # Store # channel $ sales
A 1000 10001 green $100
B 1001 10011 green $230
A 1000 10002 green $120
C 1002 10021 brown $145
A 1000 10003 green $100
D 1003 10031 red $20
B 1001 10012 green $50
So what I'm basically attempting to do is bring in the nth largest accounts within the "green" channel. Now if these were the top 5 stores I was looking for, the formula from above would suffice. However since this deals with accounts and the account # repeat I need to bring in the total sum of those repeating accounts instead of just one of the unique stores. So if I was seeking the largest account (NOT store) within the "green" channel the correct values this formula would be:
Account A $320
I would imagine we would need to combine a sumif with a large function or maybe involve sumproduct somehow.
View 6 Replies
View Related
Jan 30, 2012
I have created Pivot Table in "Sheet2" and the source data is in "Report" tab. The source data is in the range of A1:K200 -means until the last cell of the excel. Now if the data is more then this we need to change the source data every time before we refresh the pivot table - Is there any macro or any way so the range can be change automatically ?
View 9 Replies
View Related
Apr 29, 2006
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
View 2 Replies
View Related
Jul 16, 2014
On a sheet called, "Details", I have a pivot table that has three fields in the column area, Calendar type, Description, and Dates. I want to filter the pivot table based on a label filter in the Dates column. The filter should be between two dates (in D4 and D5) that are entered on another sheet and passed to the Details sheet through formulas in cells D4 and D5.
I have attached the following code to a button on a different sheet.
The code successfully filters for the employee name (which is a report filter in the pivot table) which is in a named cell.
I am having trouble with passing the start and end dates to my pivot table filter. I do not get any errors, the filter is simply blank.
Private Sub CommandButton3_Click()
Sheets("Details").Select
Dim pt As PivotTable
Dim Employee As String
Dim SDate As String
Dim EDate As String
[Code]...
View 6 Replies
View Related
Feb 22, 2008
I have looked at the below Hide/Show Pivot Table Field Items help web pages:
Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria
I am trying to use the above, but with dates in the following format in each cell:
YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc
The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.
Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
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
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
Jun 4, 2012
I have a user that uses pivot tables and charts every month to do a report. He wants to copy his charts every month and then just change the month in the data for the pivot table, but not matter what he tries its tied to the previous month and then chart data range in the select data source properties is grayed out. how to get this to work?
View 3 Replies
View Related
Feb 4, 2013
how to find the source data range of a pivot table (that already exists) in Excel 2007?
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
Mar 5, 2013
I have the following code to update a pivot table:
Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
View 2 Replies
View Related
Jan 29, 2010
In creating a new sheet to allow for multiple piece of information to auto fill; I am struggling on a small VLOOKUP issue. On my parts information page (where the info is being pulled from) I want to be able to separate out data based on the manufacturer while still leaving enough open/blank space for future additions. My issue is that with VLOOKUP every time I add to the current list(s) of data, I have to re-sort to alphabetic in order for it to work right. This means that the separation I am looking for disappears for the most part. Maybe I am doing something wrong or I am missing something, which if so, do Pivot Tables work better for this situation?
How my sheet is working is the user can pick from a list of items, click a button and the item is then transferred to another page, when it transfers the corresponding cells on the transferee page will auto fill with the rest of the info in those cells, thereby displaying not only the item picked but all of the realative data with it.
View 2 Replies
View Related
Jun 28, 2006
I have a formula that uses the max function/formula (I am not sure of the terminology) to summarise the maximum value contained within a cumulative list of monthly expenditure in cells F12 to F60 (i.e MAX(Cashflow!F12:F60)) of a very old sheet I am working on, I was wondering if it was possible to have a cell that displays the profiled expenditure which is displayed in column E adjacent to the cell containing the maximum expenditure.
I have tried using the vlookup formula however combing formulas!
View 4 Replies
View Related
Feb 22, 2009
Have following table:
No. Be Country
1 D GB
1 C DE
2 B DE
2 A GB
I want to present it as follow:
GB DE
1 D C
2 A B
Did not solve it with a pivot table. Not either with VLOOKUP combined with IF. I am thinking in the direction: If 1 in the first column and GB in the third, return the D. But I am not getting it right.
View 2 Replies
View Related
Sep 19, 2007
one part requires a pivot table to split two seperate pieces of data. the macro is running fine until this point when i get the AddFields method error (run time 1004). the Visual Basic error is as follows:
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Employee", _
ColumnFields:="Int.Ext"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Lab Hrs")
.Orientation = xlDataField
.Caption = "Sum of Lab Hrs"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=-6
Sheets("FINISHED WORKSHEET").Select
ActiveWindow.SmallScroll ToRight:=4
Range("AA1").Select
ActiveCell.FormulaR1C1 = "Int Lab Hrs"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-25],Sheet1!C[-26]:C[-24],3,FALSE)"
Range("AA2").Select
is this basically a write off or can anything be salvaged to make it run correctly. the pivot table will always use the same columns etc. every month it is run.
View 2 Replies
View Related
Feb 19, 2014
Figuring out a SUMIF or SUMIFS formula which will clean up some weekly data. I am envisioning a SUMIF formula which looks at the client name in column A in a table and then it will search through the long list of data for all entries for that specific client on another sheet in column "A", for instance. It needs to take into consideration only the encounters which happened between the dates in the table for that client listed in column B & C. The sum will be the column next to the column with each client's name which has a procedure date in between the date criteria's from the table. I have attached an example to better illustrate.
SUMIF Example.xlsx
View 5 Replies
View Related
Feb 25, 2012
This is how my pivot table would look like to start. Over time I would refresh and more things will be added. I want to make a Pie chart, and everytime I refresh the Pivot I want the Pie to get updated.
[IMG][/IMG]
BUT I want to do this on a separate tab. So basically I want to do a Vlookup of sorts. Where all the Fruit items are pulled and automatically updated when I refresh the Pivot. Then I would do the same for Veggies.
View 4 Replies
View Related
Feb 15, 2008
I am trying to to a vlookup on a pivot table and keep getting an error message (The error states that The formula you have entered contains a error) and excel will highlight the 0 in my formula. The formula is
=IF(ISERR(VLOOKUP(G17,'Curr Month'!$G:$H,2,FALSE)=TRUE,0,VLOOKUP(G17,'Curr Month'!$G:$H,2,FALSE)
View 9 Replies
View Related
Jun 3, 2009
I've attached a sample/equivalent workbook of what I'm working on which will hopefully make it clear(er).
>There are two worksheets/month. Both worksheets (represent 2 different categories) are structured the same, two columns: model code & $ amount. >The model codes change (in # and actual model), between categories and month.
>The data for each month rolls up into a year-to-date summary worksheet, with 4 columns: Model (includes all models YTD, each only listed once), category1 YTD, category 2 YTD, & Total YTD).
Previously this had been done by manually entering any new models for the month into the rows in the YTD summary sheet. And the totals for each model (highlighted in yellow in the YTD tab in my sample) were just done by an adding formula, with the new month's data manually entered into each individual cell at the end of the formula (...+X). I know there's a much better way to do/automate this! (there are a lot more models than I've put in my sample aka it's way too time consuming manually).
My problem is twofold:
1. (main issue) I have been trying to do this using various IF statements nested in VLOOKUPS, and vice versa, but the issue that arises is for models in the summary sheet that don't exist in a given (month's) table. I want the value for those models (for that specific month) to be zero, but I cannot figure out how to get that to work in my formula. The only piece that works for me thus far is =VLOOKUP(A3, 'Jan Cat1'!A2:B18, 2, FALSE), but I've tried nesting it in IF statements, nesting IF statements in it, using ANDs & ORs, no avail.
I'm not even sure any of these options are the best ways to reach what I'm ultimately trying to do. A pivot table may be better? But I will need to keep/preserve the summary sheet for each month (so there cannot just be one big updated master pivot table).
2. If I could find a way to automate/refresh & update the row of models each month, it would be the sprinkles on the icing of this cupcake.
View 10 Replies
View Related
Jul 12, 2014
I am attempting to create a vlookup formula that will count or sum a series of data so that I can pull it over into a summary sheet on a report. The vlookup formula that I am using is
=IF(ISNA(VLOOKUP($A34,'Jan 14'!$N$2:$AF$36,12,FALSE)),0,VLOOKUP($A34,'Jan 14'!$N$2:$AF$36,12,FALSE))
It seems to be working I just cant figure out how to incorporate the count or sum function into it where I need it.
Test Sample File.xlsx
I have attached a sample of the file I am working with.
View 4 Replies
View Related
Jul 28, 2014
I sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.
The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.
VB:
Private Sub ArttotKnop_Click()
Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant
iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....
Since my table grows larger every day I want to use variables.
Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable
Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))
They don't work.
View 2 Replies
View Related
