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
776 Views

Similar Articles

[PageSpeed] 10

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:

filter query based on single column list box
Thanks for taking the time to read my question. I want to filter this query by the values in my single column list box. I made this function to create the string, but the query doesn't like it. If I paste the string that the function returns into the criteria line the query works. What am I doing wrong? Thanks, Brad SQL: SELECT tblPeripheral.Type, tblPeripheral.Location, tblPeripheral.[Serial Number], tblPeripheral.Model, tblPeripheral.Brand, tblPeripheral.[Model 2], ExportPeripheralInfo() AS Expr1 FROM tblPeripheral WHERE (((tblPeripheral.Type)=ExportPeripheralInfo())); Func...

oledb query problem
Hello. I have a connection with a Access 2007 db back end (tables only) using OLEDB. The db is on a shared drive on the network. So a UNC is used. A simple query works but a query in which other tables are included just never works. Here is the query I'm trying to use; SELECT tblChangeRequest.ChangeRequestID, tblChangeRequest.ChangeRequest, tblChangeRequest.DevCompletionDate, tblProjectLead.ProjectLead, tblSPGName.SPGName, tblChangeRequest.QuickHit, tblChangeRequest.BugFix FROM (((tblChangeRequest LEFT JOIN tblProjectLead ON tblChangeRequest.Project...

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...

Using Lookup Query with Calculated Value in Table ComboBox
Hello all, This question takes a complicated setup, but I'll try to keep it brief. I have a database with a main table (tblMain) and a lookup table (tblLookup). tblMain has an integer field (LookupMe) that is related to the ID field in tblLookup. I set up LookupMe to use a ComboBox input display control that uses a query as its source. The query contains two columns: tblLookup.ID and a calculated value, say "[First Name] & " " & [Last Name]". Back in tblMain, the LookupMe field has the following properties: Bound Column: 1 Column Count: 2...

querying a view
I've got 2 taables containing a couple of years worth of data and have created a view which joins them and does a couple of calculations. If I run a query against that view to select only the last weeks data, does SQL Server haave to joint the whole contents of each table before the where clause on the query is used? Thanks No. The query optimizer will attempt to optimize the query as a whole. That is, for example, if your view is Select <column list> From Table1 Inner Join Table2 On <condition>; and you do Select <column list> From <Your...

web query autoformats numbers
Hi I'm fetching data from a server (the URL of which I've excised for security reasons) using a web query as below. The problem is, sometimes the data is something like so: 8264E01 This is supposed to be interpreted as text. I can't seem to find a combination of settings that causes it to leave that as text. It always seems to convert that to a number and display it as 8.264E4. Is there any way to force this to leave it as text? I've tried: - locking the formatting but leaving the page editable (the query refuses to run) - formatting the destination as text - various s...

Queries calculation
I have a table called “openbills”. I have a query called “billsopenvalue” In the table I have a field where I insert the amount a customer paid – this can be as well a part payment of the bill. The query calculates how much is still pending to pay. Now the problem: Ie: the total to be paid amount is 1000. The customer paid 100. = Result of query 900 Now the customer paid another 100 – normally the result of the query should be 800; but it is again 900 because the resting value is calculated: total to be paid – paid amount = resting amount. How can I fix after each calculation the restin...

Convert Access SQL Query to Excel MS Query
I have a query that works in Access but when I convert it to Excel, I get errors. Can someone help me with converting this to MS Query SQL Excel 2003 language? Here is the original Access coding: SELECT HS_Incident.[Plant Name], HS_Incident.[Incident Date], HS_Incident.[Incident Desc], HS_Incident.[Lost Time Case], HS_Incident.[Report Date] FROM HS_Incident WHERE (Datediff('d',[HS_Incident].[Incident Date],date())<=7) and [HS_Incident].[Lost Time Case] like 'Y%' and [HS_Incident].[Plant Name] <>'MARKHAM ' or (Datevalue([HS_Incident].[Report Date])>...

Trying to format the spreadsheet to fit on one page from a query
We have an Access Database with Employee Info. Example: First Name, Last Name, Cell, Extention, Email, Title We have some old timers that want their report a certain way. We have 6 different sheets in a workbook that are sorted by the above (by First, by Last, etc..). They do not want Access reports. They want one workbook, with 6 different sheets, posted on the Intanet. So I created a Workbook that way and used "Import from an access database". Each sheet has it's own saved Query. The data comes across perfect. However, there is 90 people on the list. So it goes from row A1...

select status in a query
I have a table with account information. One of the fields is Account_Status, there are 6 possible choices for account status going from 1 - 6. Wha I'd like to do is run a qurey where the user can choose to return accounts with status 4 or 6. Any help appreciated. I can use "between and" ut this returns to much information if I need just 3 or 6. It also has to be user friendly as I do not want someone in the query. Any help appreciated. Thanks Jo See sample database at this site: http://www.cadellsoftware.org/SampleDBs.htm#FilterForm Look at how the listbox is set up (...

Outlook 2007 Rules Query
I have 2 email addresses that I download into Outlook 2007. Let's say, name@domain.com and alias@anotherdomain.com I have a rule to move emails to alias@anotherdomain.com into a particular folder. However, if people send an email to both of my addresses, both emails get moved into this folder. How can I have the mail to name@domain.com stay in the inbox? Thanks, Fred. Add an exception to the rule - except if the other address is in the header - except if received by the other acct may also be an option. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips....

Queries 05-14-07
Dear Sirs, I have two select queries. I would like to combine the data from this two, into one query (something like merging the data) - query 1 + query 2 = query 3 Can someone tell me how to do do that? Thanks Klaus It depends... We're not there. We can't see what you're doing. Post the SQL of the two queries as well as information about what the queries do and what you would like this 3rd query to output and we'll get back to you. Cheers, Jason Lepack On May 14, 8:41 am, Amateur <Amat...@discussions.microsoft.com> wrote: > Dear Sirs, > I have two select que...

Query runs slow.
Hi, I have a query joining few tables. One table is very large (500T rows), but the other tables are having less than 100T records. Query seem to run slow, 1000ms. I have proper indexing implemented. What should i do? I am thinking of few solutions. Fix 1. Make a temp table and apply condition to largest table. Doing so, the temp table will have only 100 records or less and then join Fix 2. Instead of temp table create View for the larger table and apply filter and then join the view with tables Fix 3. implement caching of query (pls suggest if there is any other way to cache the ...

Web Query #6
I have this web site which shows the approval status of a number: http://www.mca.gov.in/DCAFODINPortal/dca/MyMCALogin.do?method=setDefaultProperty&mode=22 The sample numbers being checked include the following:- 00004549 00007241 00359036 I have the above numbers in an excel file.I would like to check the approval status of these numbers from excel itself. Can anybody help? : Swami Hi Turn on the macro recorder and do it all once manually, with the first number. Then inspect the code and see where the number figures in the code. After that, you can think of ways to automate the p...

Run a query a few times
Is there a way to get a query to run 10 time. I would like a buttonthat will run a query 10 times.It would take a long time to expalin y i need to run it 10 times butthere is a reasonThanks Use a For..Next loop or a form timer and the DoCmd.RunSQL method....

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...

using Like in query
This is additional question from my post earlier: I'm trying to use combo1 from existing open Form into query builder as criteria for row source of a listbox in another open Form using "Like" to find all title_description has keyword found in combo1 Thanks to Douglas J. Steele gives me a correct syntax for SELECT dshet_apr.Number, dshet_apr.Title_Description FROM dshet_apr INNER JOIN datasheet_d ON dshet_apr.Number = datasheet_d.Number WHERE (((dshet_apr.Title_Description) Like "* " &[Forms]![frmm]! [combo1] & "*") AND ((datasheet_d.Dis)=[Forms]![...

Combo Box Query Help
I have a combo box with hospital names in it. I am wanting to select a hospital from the combo box and have the the address text box automatically fill in with the hospitals address. How can I go about doing this. Please help. -- Message posted via http://www.accessmonster.com On Sat, 29 May 2010 23:00:34 GMT, "Scott_66701 via AccessMonster.com" <u54193@uwe> wrote: One way is to have an extra hidden column in the combobox. For example the rowsource would be a query like this: select HospitalID, HospitalName, Address & " " & City & &quo...

Append Query 07-18-07
I am using Access2003 I am trying to set up an append query that will combine similar fields from two tables onto one. ex: FName, LName from Table A FName, LName from Table B dump into FName, LName on Table C This is for a human resources type of database... Table A being the employee and Table B being the dependents. Table C would represent everyone from those two lists who let's say have elected health insurance... so the employee from Table A that person's dependents from Table B... all together in a single list on Table C... I tried two separate append tables, and I am ...

COUNTIF query #2
Hey all Hopefully a nice easy one.... We have a logging system, and the spreadsheet basically has the person name doing a call, what the call is and how long it took. This is use all day so loads of calls get logged by various people. Say column A is Persons name and Column B is time spent. I need calulation that checks how much time 'person 1', 'person 2', 'person 3 etc has spent on calls during the day and add's it all up. Please can someone show me how to do this, i have tried a COUNTIF bu cant quite get it to work. Many thanks in advance. Darren (digital_life...

Complex Query not working on report
I recently started using 07 on a fe/be db created in 03. The query below worked fine in 03 but now gives me the "enter parameter value" box asking for "tblRECORDS.CREATE" when I open a report based on it with 07. The query still opens and displays fine, but the report seems to hangup. Is it a timing issue? I know the query is kind of busy, but it is based on a table where the data is stored as 1s or 0's to make big queries calculate faster. SELECT tblRECORDS.QAC, tblRECORDS.CHS, Sum(IIf([IO]="INBOUND",[BR],0)) AS IBR, Sum(IIf([IO]="OUTB...

Need focus on query first, then on Form when query is closed
I am using Microsoft Office Prof Ed 2003, SP2 for my access dbs and I wasn't sure if this is a Form Coding question or a Macro question. I am having problems in coding/macro usage on a cmdbutton: I am using the frmList of Reports as Start Up Form. There are several cmdbuttons on this from, linking to several Macros which in turn open several reports. One of my cmdbuttons when pressed via Macro, opens [qryRequirement Status Comp by ETID], which requires [Enter ETID] as a parameter. I want the user to be able to copy and paste the results of that query directly into his own Excel ...

Help with InStr in a query
I have values like "2010 WW14" and "2010 WW7" in a query and I'm trying to grab the number part to the right of "WW". It will always be a 1 or 2 digit number and I always want the format to be a number. I'm currently using Left([WorkWeek2],(InStr(1,[WorkWeek2],"ww")-1)) to grab the 4-digit year to the left of "WW", but I can't figure out how to alter it for what I need above. Any suggestions? Thanks! Try: mid(WorkWeek2,instr(WorkWeek2,'ww')+2) -- Dorian "Give someone a fish and they eat for a day; t...

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...

Editing an External Web Query Import
Hi, I've currently got an Excel sheet with a table I've imported from a web page I've created. This was done using the Data->Import External Data->New Web Query. The page itself is a CGI script and as such takes arguments in the address. I was wondering if anyone knows how I can alter that URL from a cell in Excel. Specifically I want to be able to change the hostname in a cell in Excel and the corresponding ?hostname='blah' to change. Any ideas would be greatly appreciated. Jim Data>get external data>run saved query. Right click the query and select edi...