GETPIVOTDATA and (blank)

I have created a spreadsheet which has two pivot tables in it on separate 
sheets.  In addition there is a summary sheet which I use the GETPIVOTDATA 
function to extract data from the pivot tables referencing adjacent cells for 
the Manager and Rep ID's.  The original data includes some rows without ID's 
in either the Rep or the Manager fields resulting in some "(blank)" entries 
on both pivot tables.  Where this has happened on the first table I have 
entered "(blank)" in my reference cell on the summary sheet and the data is 
extracted as nomal.

When I apply the same logic to extracting the data from the second pivot 
table I'm getting a #REF! error.  I can see no differences between the two 
pivot tables (apart from the data obviously).

Any suggestions would be greatly appreciated.
0
Utf
4/23/2010 12:46:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

0 Replies
2411 Views

Similar Articles

[PageSpeed] 36

Reply:

Similar Artilces:

getpivotdata
Hi, I have a pivot table with multiple columns and need to create a summary report by different views using getpivotdata. Below is just a small summary. The actual data runs into hundreds of rows. The Pivot looks as follows Col 1 Col 2 Col 3 Col4 Country Segment Pricing Various months....................... A AA XA XB XC Total AA AB XA XB XC Tot...

GETPIVOTDATA Link to External Pivot Table
In a workbook, I made a GETPIVOTDATA formula to link to a Pivot table in another workbook, but... If I open the workbook with the formula while the pivot table workbook is closed, and then update links, I receive the dreaded "#REF!" error in the cell where the formula is. (By the way, the fomula points to the correct full path of the starting cell of the pivot table, and the formula works correctly when the pivot table file is open.) Is there any way to make GETPIVOTDATA to read pivot tables in other workbooks that are closed??? I would prefer to use the GETPIVOT DATA formula rat...

How to delete blank rows
I am trying to delete blank rows using Chip Pearson's macro; also tried acouple of addins that delete blank rows. My problem that the cells are not really blank, apparently they have zero length strings. Is there a way to delete zero length string rows, or to convert zero length string cells to blank cells, and then run the delete blank rows macro? The cells' data was achieved via copy/paste from another sheet, which was achieved from formulas such as =IF(D29="","",ha). I tried such as =IF(D29="",G32,ha), where G32 is a really blank cell. But some of the f...

GETPIVOTDATA and (blank)
I have created a spreadsheet which has two pivot tables in it on separate sheets. In addition there is a summary sheet which I use the GETPIVOTDATA function to extract data from the pivot tables referencing adjacent cells for the Manager and Rep ID's. The original data includes some rows without ID's in either the Rep or the Manager fields resulting in some "(blank)" entries on both pivot tables. Where this has happened on the first table I have entered "(blank)" in my reference cell on the summary sheet and the data is extracted as nomal. When I...

Hiding/Blanking Mailbox property
Can anyone please tell me if there is anyway to not display individual mailbox properties in Exchange 5.5 for regular users(NOT ADMINS)? When a user wants to send email on the TO ... line chooses the person's name if you double click that name it should not display any properties of that mailbox. PLEASE HELP!!! I don't think this is possible. You could just leave the attributes blank and use some other metabase to store that information securely I suppose. -- regards, Michael Abbaticchio MVP for Exchange Server http://mvps.org/exchange "YT" <anonymous@discussion...

Blank e-mail problem
Using OL 2003 w/ Winxp. Recently, a few of my associates have told me that they have received my e-mails when I reply back to them, but then they open them up, and there is nothing in the body? What settings did I change, or did I pickup a virus? Thanks, RED anglirich <anglirich@lexcominc.net> wrote: > Using OL 2003 w/ Winxp. > > Recently, a few of my associates have told me that they have received > my e-mails when I reply back to them, but then they open them up, and > there is nothing in the body? > > What settings did I change, or did I pickup a viru...

Section Break Creating Blank Page
I have a 2 page template set up to make the cover page margines and header/footer different then the rest of the document. When I insert a Cover Page Building Block that takes up the full page, a new page appears with the margines from the CoverPage section with nothing but the Section Break (Next Page) displayed. How can I keep the Section Break on the First Page and keeping the document to only 2 pages? On Fri, 13 Nov 2009 19:13:01 -0800, ZMAN11 <ZMAN11@discussions.microsoft.com> wrote: >I have a 2 page template set up to make the cover page margines and >head...

two pivot q's
Hi, I've been so lucky as to get new excel and am stuggling with two things I can't do anymore. 1. Remove getpivotdata - if I click in cell B5 I want the formula to use B5 and not lock to a particular cell. 2. Base second, third, etc pivot tables on a first. There's a box for this now, but I've no idea how to use it anymore. I'd really appreciate it if anyone knows how to do these things. Thanks, Light. Debra Dalgleish shows how to remove that annoying GetPivotData feature here: http://www.contextures.com/xlPivot06.html - Jon ------- Jon Pelti...

Count text with specific criteria ignoring N/A and blanks
Hello I have a list of survey names in A3:A726 and peoples names in Y3:Y726. Column Y also contains N/A and blanks. I am doing a summary table on the next sheet so I need a formula to show if it is 'SurveyName="Manchester" then count 'Autumn 09'!Y3:Y726 to show it has 42 names listed against it (ignoring N/As) Any help would be greatly appreciated. Many thaks Paula Hi, Try this =sumproduct((sheet1!A3:A726="Manchester")*(sheet1!Y3:Y726<>"N/A")*(sheet1!Y3:Y726<>"")) I have not tried this but it should wo...

GETPIVOTDATA
When I setup a GETPIVOTDATA function across multiple (roughly 200)worksheets in a workbook a #REF! appears and the workbook is locked into an endless loop. After I write the formula I get the correct answer but, when I do a manual calc (Ctrl F9) I get a #REF! The pivot table is in a different workbook but it is open when I write the formula and do the manual calc. How can I get the GETPIVOTDATA function to return the correct answer? ...

Blank Series in Charts
I want to create a chart with 25 different seies'. The problem is that some of the series will be blank sometime. But I d not want to include all of the series' all of the time. The data will be present at all times. I have worked out how to make excel ignore the data for the series. Th problem is that I end up with 25 series in my legend when only 4-5 ma be on the graph. Can anyone help me with this? Many thanks in advanc -- Message posted from http://www.ExcelForum.com Create a chart from all the data Select the data on which the chart is based. Choose Data>Filter>AutoF...

GETPIVOTDATA Turn Off
How do you stop the GETPIVOTDATA function. When I create pivot tables if I want to work with data within the table this function makes it impossible Example: Table Result Formula Sum of Count Animal Total Cats 7 14 =GETPIVOTDATA("Count",$I$10,"Animal","Cats")*2 Dogs 8 14 =GETPIVOTDATA("Count",$I$10,"Animal","Cats")*2 Elk 3 14 =GETPIVOTDATA("Count",$I$10,"Animal","Cats")*2 Grand Total 18 It dosen't matter if I change the $ reference locks, the r...

avg formula and blank cells
What is the proper way when using the AVG function with cells formatted as numbers, to fill a blank cell so it won't make the avg lower, but rather be ignored in the AVG function? Thanks! --Randy Starkey __________ Information from ESET NOD32 Antivirus, version of virus signature database 4080 (20090515) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com Excel ignores blanks cells when averaging. If you mean ignore cells uncertain a certain value. In Excel 2007, use AverageIf. In earlier versions you need to construct the formula using SumIf ...

getpivotdata wildcard?
I'm using Excel 2003 on Windows XP. I'd like to replace a reference in the GETPIVOTDATA formula with a wildcard which represents a different cell reference. Is there an add-in tool that will let me do this? -- -Kim ...

How to get rid of GETPIVOTDATA function?
Hello, while addressing cells in a spredsheet from PivotTable Excel 2007 always puts GetPivotData functionautomatically instead, like old versions, simply addressing cells (like =B3 or simillar). It makes my formulas very long and not possible to understand. I wonder if I can switch off this automatic functuion and tell Excel to address cells just by they simple address? Thanks Rafal Office button/Excel Options/Formulas/2nd section: deselect "Use GetPivotData functions..." "Rafal Hemmerling" <Rafal Hemmerling@discussions.microsoft.com> wrote in message news:...

blank GP instance
Hello: Strange question....is there anything "bad" about having a GP instance without a name? Seriously, the instance has () in Add/Remove Programs and Start...All Programs...Microsoft Dynamics shows the name of the GP folder as "GP 10.0- ". I got the Dexterity Shared Components error and used the DOS method of getting around it as outlined in a TK. The second DOS command produced an error saying that a named instance can only be created when you have an instance. Yep, it sounds like the computer was drunk or something. Nevertheless, I went along with it. I re...

GETPIVOTDATA with Date
I could not reconcile GETPIVOTDATA with a date format criterion, so changed the dates (I only need the month) to text, i.e., Nov 03. However, this does not work, even when I only try to retrieve the No 03 data: =GETPIVOTDATA('Roll-up Pivot'!$A$2,"'Nov 03'") It works like this: =GETPIVOTDATA('Roll-up Pivot'!$A$2,"Hours Enrg E") but when I try to add the 'Nov 03' criterion, it gives a REF# error. A second method that would solve this same problem would be if I coul us a range criterion in GETPIVOTDATA. The original date format i actully ...

SmartList Builder Restriction Values Blank
When creating a restriction on a new SmartList through SL Builder, the list of values are blank. Why would that be? The SmartList I am creating includes the tables RM Open File joined to the RM Distribution Work File which is joined to the Account Index Master. I would liked to restrict the SmartList to include non-voided transactions and only distribution transaction types of REC (I believe that would be a '3'). The client is on 10.0 SP1. Any help would be appreciated. Tree309, I was able to create the SLB, but I did things a bit differently. 1) First, I added the RM Di...

Blank Rows
I am trying to enter a blank row inbetween every row that is in a worksheet and I can't figure out a way to do it. I am going to be merging this information and when I merge it without blank rows, it does not pull all of the rows. Please help with any information you may have. Hi Acesmith You can use a macro Sub test() Application.ScreenUpdating = False Dim numRows As Integer Dim R As Long Dim rng As Range numRows = 1 Set rng = ActiveSheet.UsedRange For R = rng.Rows.Count To 1 Step -1 rng.Rows(R + 1).Resize(numRows).EntireRow.inse...

GetPivotData Function
=GETPIVOTDATA("PrehuntTotal",'pvt prehunt ASMtotal'!$A$3,"COUNTY",$AF $2) Those familiar with the GetPivotData function know that "pvt prehunt ASMtotal" is the name of the pivot table and "PrehuntTotal" is the variable name - the data I'm interested in. The value for "County" is set dynamically. The user selects county from a drop down list. The choice is placed in Cell $AF$2. My question for the group is this - would there be a way to set the variable name the same way that the value of county is set so that the user can selec...

Replace null string with blank cell
I have a large table which was created via lookups from other tables. Many of the entries are (or should be) blank. After the table was created, I converted everything to values (Paste Special/Values) in order to save memory. However, the table now contains a null string or other unprintable character in each of the "blank" cells. Thus, if I use END-DN or END-UP to find the next value in the table, the cursor goes to the end of the table, as it sees something in each cell. If I edit a "Blank" cell, I see no characters, and if I select the formula bar & hit ENTER, ...

#Value! Errors from a GETPIVOTDATA function
Hi I have a report which picks up data from a pivot table using the GETPIVOTDATA function. e.g. =GETPIVOTDATA("Total",Electricity!$A$3,"Planning Group",$A5,"Sub-Transaction",D$2,"Summary","Energy Price") It works well in Excel 2003. However when I send the file to a colleague who uses Excel 2000 all the results are replaced by "#Value!" He has the analysis toolpack installed, Any ideas what the problem might be? I think I've worked out what the problem is. To save on file size I unchecked the “Save data with table layout”...

Blank row
I'm entering numbers under a colum, I need the total sum of the colum; But after I enter the last number and press enter it needs to skip with a blank row or cell ? but allways adding the total sum. After the 221 press ente, the cursor will go to the next cell below to enter my next number but allways have that blank row or cell before the total sum. Thank you very much. JA ex: 123 222 111 221 ---> Here is where I need the blank row allways 677 This is the total sum ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post us...

GETPIVOTDATA query
The formula below works. It gets the VAT value from the pivot table for TP = 1. GETPIVOTDATA($B$5,"TP['1'] VAT") Is there a way that I can reference the TP outside the formula - something like GETPIVOTDATA($B$5,"TP[' & A1 & '] VAT") so that I can choose which TP to see by changing the value in A1. Thanks Laurence Lombard Add some quotation marks, and it should work: =GETPIVOTDATA($B$8,"TP " & A1 & " VAT") It should work without the square brackets and single quotes around the number. Laurence Lombard wro...

How to I test for a blank time entry?
Hi Guys How do I test for a time-of-day entry being blank? The cell (say A1) is obviously time formatted (hh:mm) but my usual method for the result cell: =IF(A1="","",do whatever) doesn't work because the value in A1 is the wrong type. Have also tried =IF(ISBLANK(A1),"",etc) but that doesn't work either. When A1 is blank, the result cell displays #VALUE. For information, the result cell (formatted as General) contains the following formula: =24*60*IF(A1="","",A1-$C$14). C14 contains another time, so this is basically a time subtractio...