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



0
lombardm (46)
8/29/2004 9:15:12 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
885 Views

Similar Articles

[PageSpeed] 51

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 wrote:
> 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
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
8/29/2004 12:12:08 PM
Thanks Debra for your prompt reply. What works in the end is

=GETPIVOTDATA($B$8,"TP['" & A1 & "'] VAT") so that the result reads

=GETPIVOTDATA($B$8,"TP['1'] VAT")

The '1' must end up in single quotation marks and in square brackets. It
took a while to works that one out. In this case the '1' is the name of an
actual item in the pivot table, but according to the help file one can use
it to reference the 1st, 2nd etc  items. The syntax remains the same.

Laurence

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:4131C818.3020705@contexturesXSPAM.com...
> 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 wrote:
> > 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
> >
> >
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
lombardm (46)
8/29/2004 1:08:08 PM
Reply:

Similar Artilces:

MS Query, command View-Query Properties
Hi there, In View-Query Porperties you have options for: - Unique Values - Group Record The first option reduces the result set to one row for field values a-b-c. That's clear. But what about Group Records? To me, it looks similar. And how do these two options cooperate / interfere with the [Totals] button which sets Sum, Avg etc. to the selected column. Thanks for a reply. Frans ...

fail to create IXSSO.Query object
I'm moving classic asp sites from Windows 2000 / IIS 5 to Windows 2008 R2 / IIS 7. We have the "Indexing Service" started. Catalogs were created OK. However, I am unable to create an ixsso.Query object. Get the following error message Microsoft VBScript runtime error '800a01ad' ActiveX component can't create object /HC/ECO/Announcements/Headlines/SearchResults.asp, line 39 ASP code : <% Dim sSearchString Dim oQuery Dim results() ...

ledger form and binding vs queries
I've designed a "ledger" form that generally acts like a datasheet form, but uses text boxes, comboboxes, and a lot of code to act in a much prettier way. I first attempted to link the form to appropriate records (from 1 table) by setting the form's recordsource at runtime to a query string and then using event driven procedures to create new records, update records, and delete records (represented by line items in the ledger) in the form's recordset (the controls in my ledger remaining unbound, only the form is bound). This was leading to problems I wasn't sure how...

creating query
I am getting a message about having an ambiguious outer joinin one attempt and duplicate records in another. I have a database containing information from all of the football plays in a game. I have many tables but 4 of them are "main", "formation", "play", "playdiagram" The main table has the following fields [mainid] score time formationid runid the formation table has many fields including [formationid] formationfamily backfield variation strength the play table has the following fields (it consists of both a formation and a play) playid fo...

two sets of parameters..one query...but how?
I have a query to track when I must renew State Police Clearances AND Child Abuse Clearances. I want to use the "Between [date] and [date]" function on BOTH of the fields in order to bring up BOTH sets of dates for BOTH clearances.The problem I am running into is that when the query results come up, if one date doesn't meet the criteria NONE of the information comes up (even if the other date DOES).Any suggestions!??!?! Thanks in Advance....

Delete Queries using Unmatched Query
Reading many helpful responses on this site, but unfortunately none of them seem to remedy my current issue. Just learning Access, took a quick college prep course, got the basics. Now I am expanding freelance. SO the scenarios is my test idea that I have intent to apply on larger scale. I have a database with 15 records... lets say reservations (tbl_reservations) for a hotel. Used a maketable to copy that table because it gets updated often to cancelled or complete status. So I filter out the 9 of those 15 reservations which are still in pending status and make the new table (tbl...

Using only forms, Not queries
HI, How can we restrict users to enter/view data only through forms and Never queries? When a form uses a query to access the table, we need to give the rights to read, insert and update anycase. Then what stops the user from using the Query directly? Request some clarification. Thanks a lot Ramesh You most certainly can, and should hide all of the ms-access interface. The options to complete hide and keep people out of the ms-access interface can easily be done using the tools->start-up options. Using those options allows you to complete hide the ms-access interface (tool ba...

related products table
I would lilke to automate the update of tblRelatedItemsTable for our B2B web (Books-Wholesale) there are only two fields ProdCode & RelatedItem All the needed info can be found by querying tblInventory the most straightforward example is an author with 20 titles ProdCode for title1 is related to ProdCode for title2 --> title20 ProdCode for title2 is related to ProdCode for title1 AND title3 --> title20 ProdCode for title3 is related to ProdCode for title1 AND title2 AND title4 --> title20 etc etc Books in a series would be the same. If someone can point me in the right directi...

can I query active directory with excel?
Looking for a way to query Active Directory with excel ...

SQL Query in VBScript
I am using the following code to perform a SQL query and return a recordset I am getting the error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" This error occurs when I open the record set. What am I doing wrong? Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "DSN=ChartMES;" objRecordset.CursorLocation = adUseClient strQuery = "Use...

Return Random Sampling from Query
If I have a first query that returns 3000 records, is there a way to build a second query based upon the first to pull a random sampling of say 225 records from it? QB SELECT TOP 225 * FROM QueryName ORDER BY Rnd(IsNull(NumberFieldInQuery)*0+1); It would work best if NumberFieldInQuery is the primary key field in the original table. NumberFieldInQuery needs to be a number field. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "QB" wrote: > If I have a first query that returns 3000 records, is th...

Deleting data from a table through a query
I have a database that is designed to update a list of credit union members from a master list so that vehicle insurance coverage can be tracked. I have been able to run an unmatched query to achieve a list of old members who have either paid off their vehicles or moved their loans to other locations. What I need to do now is delete these people from the main table. I have the cascade update and delete funtion in place to delete the vehicle information once the member is deleted, but I don't know how to take the information found in the unmatched query and delete those members fr...

help designing query to get results
In my database of employees I have three date/time fields - Agency Start date, Rehire date (a lot of our employees only work part of the year) and Separation Date. If I wanted to show only the employees who have either started, were rehired or separated in a certain time frame, how would I do that? Example: Which employees started or were rehired or separted between july 1 and july 30? -- JoAnn In query design view, enter the date range in the Criteria row under the first date field. Below the Criteria row, you'll see another one marked Or. Enter the same date range...

How to implement a query that return data with no match
Hi, i have read some posts of inner join and outer join, but no one could help me to solve my problem :( I have 2 tables 1) tblRecords (having, article; User;date in; date out; service) 2) tblUsers (user) what i need is to select from tblRecords those records that does not match the users in the tblUsers, so if for example i have 20 records in the table 1 (all from diferent users) and in table 2 i have 3 users, the result will be like if i delete from tblRecords the records that match tue users in tblUsers. But i don't want to delete those records... so how can i make a select query to ...

dynamic query?
I have database in which 1 field called sector has multiple values (e.g. A, B, C, D) and a field call industry which is a subfield to sector. So sector A can have mulitiple industries as can sector B and sector C etc. I have a table defined where the data looks like: Sector Industry A industry_1 A industry_2 A industry_3 B industry_1 B industry_2 B industry_3 C industry_1 C industry_2 C industry_3 On a data entry form, when sector A is chosen in th...

Importing data to Excel using MS Query
I am importing data into Excel 97 using the data query functions and MS Query. I can set simple criteria, but I am also sure I could do more with this if only I knew how to. Could anyone recommend some (simple!) reference material that explains more about setting up SQL queries. TIA, John. -- John Reynolds This is a pretty good site for a basic MS Query tutorial. http://www.rdg.ac.uk/ITS/info/training/notes/excel/query/ For learning SQL...I always recommend: The Practical SQL Handbook: Using Structured Query Language It's not Microsoft specific, but it will teach you what you ...

Still stuck: rerunning a query to change criteria
My form lets one set criteria for a query, from which some textboxes display several Dcounts of the selected clients (grouped and counted by age and gender). Now I need this query to be rerun once different criteria are set. But I believe that as long as the form is open, the query wil be run ones and then never again! I could of course have people close and open the form again and again, but that is cumbersome. Me next try would be to have the criteria be set i the form and then as next step run and open the query, including the data to be displayed in a subform. But is ...

Need some query help.....
I'm very new to Access. I was wondering how I could display a field in a query that is conditional. I will do my best to explain... I want to add a field to my query that grabs the info from Project.Badge or Project.BadgeET depending on if the field TestType is displaying "Eng" or "Pkg" so it would be something like this If TestType = "Eng" then Display Project.BadgeET ElseIf TestType = "Pkg" then Display Project.Badge End if im assuming I would type some formula in the Criteria section?? but under field, i can on...

Hide duplicates in a query ...
Hi folks I would like to hide any duplicate records in a query. I have already used the "Unique Values"parameter, but I would like to hide any records where the first field is duplicated. Did that make any sense?? ... eg. Field 1 Field 2 1 a 1 a 2 b 2 c I would only like to show one record for "1" & one for "2" etc ..... Can anyone help? Thanks If it makes no difference which record of the group you want to see, you could use an aggregate query where you group by field 1 and specify firs...

Including Null values in query design
I am using a form to enter the parameters for a report. The parameters are drawn from a number of combo boxes on the form. The report is based on a query. Several of the query fields have expressions such as: Like IIf(IsNull([forms]![frmReports]![cboIssuedBy]),"*",([forms]! [frmReports]![cboIssuedBy])). This works fine as long as the fields that are being queried have no null values - if they do, then in the situation where I want the "*" to apply, the query only returns the records which have a value. Really, I want to be able to replace the "*" pa...

Problems with small numbers in Query and in XL pivot table
Hi, I have some CSV data that I want to use in a Pivot Table: I'm accessing the data from XL through MS Query using the MS text driver. I have a field named 'Ratio' with significant digits up to the ninth decimal place. Ratio 0.000118019 0.0000012 0.0000014 0.0000028 0.000001488 But, ... in Query and in Excel, everything is showing up as zero. How can I get the correct number to display and calculate in the Pivot Table ? TIA Leon Works by me. No data loss. HTH -- AP "Leon Noel" <LeonNoel@LeonNoel.kz> a �crit dans le message de news:443a2551$0$21291$8fcfb9...

Query SQL server db with cell value parameter
I want to supply criteria value using a cell on the spreadsheet to contain a date. Can't seem to figure out how to do this. On the Connections - Properties - Definition dialog, "Parameters" is grayed out. ...

Import text file query error in 2007
I have an excel sheet with an import text file query. If I change the file path from the workbook connection properties it results to an error, "not responding" then excel closes. This happens both in XP & Vista. Also this only seems to happen if the data source is a text file. Changed file location for an access file query & had no problems. Any suggestions? Thanks in advance...Diba ...

Parse textbox words for query values
I've created a very simple asp.net web form (via visual studio 2008) that has a text box and a submit button. I want to be able to paste a bunch of 'words' into the textbox and have the words used in a select statement. Something like SELECT name, address, status FROM StatsInfo WHERE status IN (textbox-word1, textbox-word2, textbox-word3,...) I've got something simple working where a single word can be queried, but I can't find a way to parse the contents of the textbox so the query searches for each word. Do you have or know of any examples you co...

Self linking tabel in editabel query
Hi I have a StaffBookings table with Date, Time, StaffID and ClientID columns which I use to enter information via a bound form. Now I also need to have a column that shows the count of jobs that the staff has done. My solution would be to do a count query on StaffBookings table and link it to the StaffBookings table itself. Problem is that such a query makes the form un-editable as far as I understand. How can I get round that; that form bound to StaffBookings table is editable but also has the count? Thanks Regards John You've described a "how"...