Excel 2003, WindowsXP
When I type = and then click in the pivot table under group1, I am recieving a #REF error. It strange because when I do the same thing in another group (ie. group 2 or group 3, etc.) in the same pivot table I don't recieve the error?
Is there any more efficient way to get a formula that tries to get pivot data to return data to show zero, rather than ERROR, than this?
=if(iserror("the get pivot data formula"),0,"the get pivot data formula")
I am trying to squeeze some more effenciency into a ridiculous spreadsheet, and I hate that this formula has to do 2 lookups and an if statement. If this really is 2x the work, I was wondering if there is a more efficient way?
I'm struggling to get the "getpivotdata" function working correctly.Sample book attached.
I'm trying to return the data that is held within the pivot table to a separate part of the sheet.In column D I have week numbers and in column E the qty(which is currently empty) How do I return the qty in column E from the data in the pivot table (column B) relative to the respective week numbers.
We have a very long macro that at the end sends an email to each training coordinator. Within the body of the email, we want to autopopulate the completion status from a pivot table using getpivotdata.
We first try to set the variable but get an error: [compile error:invalid qualifier].
Sheets(Summary).Select
Dim BDCompletion As String
BDCompletion = Application.PivotTableSelection.GetPivotData(A3, "Business Dev Plan Found")
And this is how we plan to incorporate the variable into the body of the email:
With OutMail
.To = "name@company.com"
.CC = ""
.BCC = ""
.Subject = CurrentSheet.Name & " Training Plan Status as of " & Format(Now, "dd-mmm-yy")
.Body = "BD is " & BDCompletion & " complete for 2007 Training Plans as of the date of this email."
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C: est.txt")
.Send 'or use .Display
End With
Is it possible to replace the Pivit table name/reference in a GETPIVOT function with a cell/range reference? I can on other components of the function call, but not that. For example ... the "hardwired" function call might be:
=GETPIVOTDATA(" Sum of 1991",'[EE_financial data.xls]Sheet1'!$A$3,"Company","ERG","Item"," Depreciation & Amortisation")
I can achieve the same result by externalising the 1991 and ERG, so i replace this with other dates or names, thus:
ERG
1991=GETPIVOTDATA("Sum of "&C13,'[EE_financial data.xls]Sheet1'!$A$3,"Company",D12,"Item"," Depreciation & Amortisation")
.. and this works fine.
However, if I try and replace the '[EE_financial data.xls]Sheet1'!$A$3 with a reference to a cell containing that string, it returns #REF!
I am trying to do a "getpivotdata" formula on a cell in certain file that gets info from a pivot table on another file. However the name of the second file (the one where the pivot table is found on) might be different each time so I created a variable for the second file name but I can’t figure out a way to use this variable in the get pivot data formula as I am not very experience with VBA.
Template = Application.InputBox("What is the name of the file ending with (.xls)")
If Template = False Then
cont1 = MsgBox("Please write the name correctly using (.xls)!", vbOKOnly)
If cont1 = vbOK Then
Exit Sub
End If
End If
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
my lookup isnt returning the correct data- already tried sorting it doesnt work im pretty sure ive put in the correct formula
View 6 Replies View RelatedI keep getting the "You've entered too many arguments for this function" error.
Here is the formula:
=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))
i am working on a project where i need to get data from a pivot table that resides on a separate worksheet named "Pivot_by_Group".
I have a worksheet "Budget vs. Actual" and I need to pull data from the pivot table to populate the Actual values.
How can i do that and one more thing, hopw can i suppres errors (#NAME, #REF, etc), i've used the ISERROR function with no luck.
Trying to use a the getpivotdata fomula with date as an argument, refer to with a cell. e.g. =GETPIVOTDATA( 'May Table'!A1, "SICK " & S20). where S20 is a date format
View 2 Replies View RelatedMy created pivot table chart is a "sales funnel" that has 8 field list in the title section used to slice the date (i.e. per quarter, per sales person, per product, etc.). When a combination of selections results in no data, the pivot table dynamically removes this column or row. The GETPIVOTDATA function returns a #REF! because of the invalid reference. How can I lock the pivot table to always have all columns and rows or can I return a zero instead of the #REF!? When the #REF! occurs in a @sum range, the #REF! is returned instead of the sum of the numbers.
View 3 Replies View RelatedI want to create a charts on a pivot table, but I don't like the way Pivot charts function and the limitations they have.
So I want to create a normal charts that is dynamic and based on a pivot table.
In order to do so, I want to create dynamic named ranged for the data in the pivot table to use for the data series for the chart.
I can reference the week numbers (headers in the pivot table) for one data series and get the data by this formula:
=OFFSET(Weekly!$C$15,0,0,1,COUNTA(Weekly!$14:$14)-1)
$C$15 is the first cell that holds the data for the series.
Row 14 holds the week numbers (headers in the pviot)
However, I want to make that formula even more dynamic by having it reflect the changes done to the pivot table who's cells it references to.
So how do I do this using the GETPIVOTDATA function? (or some other function that makes it dynamic and reflect changes done to the pivot table and accounts for the row numbers with the data can change)
If I generate the GETPIVOTDATA formula and try to substistute the fixed cell reference for $c$15 with it, the OFFSET formula for the dynamic range gives an error.
And how can I grab the column headers (week number) dynamically?
I've attached a spreadsheet which is a basic version of what I'm trying to achieve. I've got a pivottable based on 3 columns of data: Order No, Delivery Date, Value
I want the sum of a given order in a certain month. E.g. the value of Order No. 1001 for Dec '05. I tried doing a pivottable and then using GETPIVOTDATA (which I've not really used before) and failed. I imagine there's a better way I've not attempted yet. Can anyone shed any light? The spreadsheet is attached
I have this problem with pivot tables:when I write a formula relating to a cell in the pivot table I don't get the usual basic cell refs (e.g. B4/b5) but I get something like GETPIVOTDATA("Vehicle No",$A$3,"Model Desc",...... etc etc
and I cannot copy and paste this formula!
I would like to know if there is a shortcut to use basic Cell refs because it is very boring always type the cell details in manually when using data from a Pvt table
Some weeks ago I had to move from excel 2000 to excel 2003 and It seems incredible to me that moving to a more recent release of excel become a step behind....
also using vba code?
In Excel 2010, I can manually enter the GetPivotData function (and it works), but with Generate GetPivotData selected / enabled (in PivotTable Tools --> Options --> PivotTable --> Options), the GetPivotData function isn't automatically generated.
View 1 Replies View Relatedso, for a column, if i reference a getpivotdata to an item's total, it will sum the total being displayed- which can be less than the "real" total if some items have been hidden in the pivot table. what i want is to have getpivotdata (or another function, i don't care what it's called) to always sum an item's total for the entire pivot table range- irrespective of whether certain of it's details have been hidden. ideally, what i'm trying to do is:
sum the total of an item for the pivot table range
sum the total of the column from the original data
compare if they are not equal, the pivot table has not been refreshed
i need to do this without macros. (it's my "solution" to see if macros are enabled or disabled- because my pivot table automatically refreshes data based on macro)
I want to sum up data for two periods (4&5) from a pivot table using GETPIVOTDATA formula. I've only been successful when I manually type the period values 4 and 5 as is shown below:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{4,5},"Project",$A7,"Category","T&M"))),0)
However, I cannot get the formula to work when I substitute cell references for the period values 4 and 5. Therefore, the following formula does not work:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{E5,E6},"Project",$A7,"Category","T&M"))),0) where E5 has 4 in the cell and E6 has 5.
I want a UDF; = OK (f1,f2) each argument being an alternative function. If f1 returns an error message then use f2. Should be easy but I cannot get it to work.
View 5 Replies View Relatedi created a function called ColorFunction
but when i try to access the function it says
Book1.xls!Module1.ColorFunction
when i select this it says
Function Arguments
No help available
The function takes no arguments
Formula result =
but when i run it for the first time
it worked, but when i try to use again after saving the book .
my formula called ' conversion' used to convert foreign money in EUR does not
seem to work. My formula gives a value error. I wonder why...
Function Conversion(Position As Integer, Currency As Integer) As Integer
Dim USD As Integer
Dim GBP As Integer
Dim CHF As Integer
Dim JPY As Integer
Dim NOK As Integer
Dim NZD As Integer
Dim SEK As Integer
Dim ZAR As Integer
Dim ISK As Integer
Dim TRY As Integer
Dim AUD As Integer
' here i give a destination to my forex quotes who are in a excel sheet
USD = Range("N9").Value
GBP = Range("N10").Value
CHF = Range("N11").Value
JPY = Range("N12").Value
NOK = Range("N13").Value
NZD = Range("N14").Value
SEK = Range("N15").Value
ZAR = Range("N16").Value
ISK = Range("N17").Value
TRY = Range("N18").Value
AUD = Range("N19").Value
it returns an error when I try to enter it. Is there a brave soul which a good eye for errors that might be able to shine some light on the error. Currently it appears the error centers around the MONTH( ) portions of the function. But each embedded function is able to stand alone and I can't imagine why they won't work together.
View 4 Replies View RelatedI am struck in using IF /AND in nested function as follows,
A1B1C1D1E1F1G1
1 3 5 7 9 32
=IF(AND(F2>G2,AND(IF(A2>1,A2,AND(IF(C2>5,C2,AND(IF(E2>9,E2),""))
(Above, A1 to >G1 are cell address , in exact rows are actual values)
I have following snippet of code. it is very simple.
Code:
Sub GetDatafromXmlToTemp()
Dim str As String
Sheets("sheet1").Select
desc = WorksheetFunction.Match("FAMILY_ID", Rows("1:1"), 0) ' gives column number for ex 4 for "D"
[Code] .....
I am getting error "Argument not optional" in line.
Code:
str = ColLetter
I am getting "Sub or Function not defined" error in the below place of my coding. I have highiglided the code which system did and this was given by one of our commite member only
[Code start here]
Windows(Fname).Activate
If Not WorksheetExists(Date1) Then GoTo ABC
Else
[Code]....
In Excel 2007, the following cell Q14 CSE formula accurately returns the row number of the first negative value in the column P array P14:P102.
{=MAX(ROW(INDIRECT(ROW()&":"&MIN(IF(P14:P102="",ROW(P14:P102)))))*(INDIRECT("P14:P"&MIN(IF(P14:P102="",ROW(P14:P102))))
I have been searching through your forum but I can't seem to find the solution to my problem. I have two sheets: On one sheet in cell b2 I have a validation list whose source is =Indirect(Subgroup) which gives a #ref error. However when I evaluate Subgroup, I get a legitimate range. I have attached an example of what I am trying to do.
View 9 Replies View RelatedI am having problems getting the code right for the WorksheetFunction. I have put a snip of the procedure below.
I have data that is sequentially entered into columns, always in rows 4 to 18 with the column ref increasing by 1 each time new data is entered.
After each instance of data entry into a new column I need to loop through that data and, if the a cell value matches a value in range A1:A200, to place x in column B in the same row as the match in column A. All in Sheet2.
Set cStartcell = Sheet2.Range("IV4").End(xlToLeft) ' goes to last entered column
For Each cell In Sheet2.Range("B2:B200")
Application.WorksheetFunction.If(Match(Offset.(0, -1), Sheet2.Range.("cStartCell:cStartCell.Offset(15, 0)"),""x"")
Next
I have put the problem in bold. whatever I try seems to come up as 'end of statement expected' or 'identifier or bracketed expression expected' errors. When I have managed to get rid of errors the fromula does nothing.
I am having trouble with the following code. When I put in a value in C8 that is on the list being searched (A2:A27), the Answer is still coming up as false (ie, the find function isn't finding the variable in the list, though it is there). I'm guessing I'm using incorrect syntax somewhere.
Dim Answer As Boolean
Private Sub CalcBi_Click()
Dim Pledge, Edate, PR, PPA, EEA As Double
Dim Due, Chdate As Date
If Range("C6").Value = "" Or Range("C8").Value = "" Then
Exit Sub
Else
Pledge = Range("C6").Value
Edate = DateValue(Range("C8").Value)
Set rngschedule = Worksheets("Bi Weekly Schedule").Range("A2").Offset(Application.WorksheetFunction.Match(Range("C8"), Worksheets("Bi Weekly Schedule").Range("A2:A27"), 1), 0).....................
Is there a way in which you can create an if function or similar to give a reason for why the cell of data has an error.
Basically i have some data in a table, and when a #num! error pops up id like to in the cell next to it give a sentence stating why this has happened.
E.G.
Cell A1 Cell B1
#Num! Number error due to 'x' being too small
Not sure how i can get this to work, i have tried a basic if function but that hasnt worked.