How to translate a getpivotdata formula into a cell reference

Hi all,

I want to be able to get the cell reference (in the format A1) of a 
GETPIVOTDATA reference. For example:

=(GETPIVOTDATA("Sum of Qty",'$A$5,"Customer Name","ABC Company"))

pulls data from a corresponding cell in the table. That reference is A10 but 
I want to get this from the actual GETPIVOTDATA reference itself. Is this 
possible?

Regards

Morgan
0
Utf
2/11/2010 10:01:01 AM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
1309 Views

Similar Articles

[PageSpeed] 38

Reply:

Similar Artilces:

Is this an Index/Match formula?
Hello - I have 2 tabs of data. The first tab has info by product and its qualifying vendors and non-qualifying vendors (the vendors are in seperate columns) ColumnA ColumnB ColumnsC ColumnD ColumnE Product X Vendor1 Vendor2 Vendor3 Vendor4 Column A will always be unique Columns B and C are qualifying vendors and Column D and E are non-qualifying. In the second tab I have the following: ColumnA ColumnB Product Vendor What formula do I use to tell me if the vendor is Qualifying (Q) or Non-Qualifying (N) or blank if the vendor is not included in the li...

copy rows in range if data in first cell
I need to select from a range of rows - only those having data (formula result) in the first cell. There will be some rows without data in the first cell but they would contain a formula - these shoudl not be selected for copy. I want to select and copy all the rows having data in the first cell in order to paste these rows as values into a separate sheet in another workbook. Any suggestion? Try some code like the following: Sub AAA() Dim Destination As Range Dim RowNumber As Long Set Destination = Worksheets(2).Range("A1") For RowNumber = 1 To 10 ...

Format when viewing formulas
I'm trying to get two prints of the same document: one showing absolute numbers and the other showing the formulas. When I'm pressing ctrl+~ to show functions for my 2nd print, the format is changing. The cells are getting too wide, so I end up getting only the left half of the document in print preview. How do I change the document to show functions with the format staying the same so I can print it??? Thanks. One way: Page Setup/Page/Fit to 1 page(s) wide. In article <D2EAE056-FC1E-4F40-BA48-974B191C4B8D@microsoft.com>, "bruno" <bruno@discussions.micro...

A formula problem.
Hi all Im new to the forum. I have 2 questions re excel 97. I hav created a spread sheet for a friends lawncare business. It has columns. The first one is a description of the service. The second on is the quantity of service. The third one is the unit price. And th fourth one is the total price. I have it set up so that the fourt column multiplies the coulmn with the quantity. It works fine excep that it prints a $0.00 in the total column if there is nothing in th quintity column. Is there a formula that would allow me to make it s if there is no number in the quantity column , it would ju...

Help with If formula
This is my formula which works ok. =IF(D6<D16,"","over") What I am trying to do is extend this and in the same formula have D6,D7,D8,D9,D10 included,ie if any are greater than D16 then "over" but I get to many arguements. I have tried D6:D7<D16 and a couple of other things but cant get it to work. "ss" <weels51@hotmail.com> wrote: > This is my formula which works ok. > =IF(D6<D16,"","over") > What I am trying to do is extend this and in the same > formula have D6,D7,D8,D9,D10 included,ie if any are > ...

moving from cell to cell #2
When I first start up excel and click on a cell, it will not let me release that cell. It's like its stuck on there. It will high light every cell, but it won't let go of the very first cell, and I can't use any menus after that....Any thoughts??? This may help: http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&mid=55aeb349-f4d5-4b8f-94ff-f98b605c40ae&sloc=en-us -- Gary's Student "Stumped" wrote: > When I first start up excel and click on a cell, it will not let me release > that cell. It's like ...

Array Function to do a Sum of VLookUps that translate a letter gra
Suppose I have letter grade data in a range of cells, B12:D12 as follows A-, B-, B+/A- I have another range of cells, named GradeList that gives numerical equivalents for each of these letter grades. I want a function that will look up each of these grades in the table, and return their sum, average etc. I tried the following as an array formula: {=SUM(VLOOKUP(B12:D12,GradeList,2,FALSE))} And it didn't work; looks like it just returns the value of the lookup of B12. Actually, I want this formula to sum the highest N grade values; I know how to use an array formula to ...

How do I average a formula without calculating zero values?
I want to average a column, except for the cells that have zero as a value. How do I do this? Thanks! =AVERAGE(IF(rng<>0,rng)) which is an array formula so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips "KMHarpe" <KMHarpe@discussions.microsoft.com> wrote in message news:DA83589C-D838-4A91-8703-40B48668DBD6@microsoft.com... > I want to average a column, except for the cells that have zero as a value. > How do I do this? > > Thanks! ...

Send the Document, may differ some place due to cell contents...
I think subject wasn't so good that tell you what i wanted to do... I have a text that is about an a4 page in Word. I have an Database of about 150 firms. And two colums; Company name & Company Productivety. Company name Producktivety a 90 b 50 ... .. ... .. And so goes. And the text is like; -------------------------- .................................................. ............ .. ......... Company name: a Company productivety: 90 -...

"grep" the formulas?
Is there some way to ask Excel to identify all the cells that contain some particular content in the formulas themselves? For example, I'm finding that the MROUND function is causing some trouble, so I'd like to find every place in a very large workbook where "MROUND" occurs in a formula and rewrite that formula. I've run into this before, where I've wanted to find every formula that contained some particular character sequence, and some of the formulas are very long and very numerous. What I'd like to be able to do is "grep" the formulas and highlight t...

long text not visible in the cell of excel
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel when the number of words get beyond a certain (a small) amount, the text suddently becomes unreadable, turning into the #####. the formatting has been set to &quot;wrap&quot; the text. was there some setting i should change? <br><br>the following text is ok: <br><br>&quot;2009 deficit is $1.4tn, an all time record in dollar terms. It was $958bn above the 2008 deficit, the previous record holder. It's 9.9%gdp. The record percentage belongs to 1945 of wwii-era 21.5% <br&g...

DTD translation to Schema
Hello, I need to write a DTD to XML Schema translator in C#. Is there some source code available I can start from? It does not have to be C#, it can be C, C++ or Java. Thanks Yorg YorgZero wrote: > I need to write a DTD to XML Schema translator in C#. Is there some source > code available I can start from? It does not have to be C#, it can be C, C++ > or Java. There is a perl script at W3C site that does the job - http://www.w3.org/2000/04/schema_hack/ Also in .NET you can make use of Chris Lovett's Dtd2Xsd tool - http://www.gotdotnet.com/Community/UserSamples/Details....

Some help in refernceing some cells
Hi there, I need help. I have a long list of numbers, and in each cell next to each number, want to display a color if that number appeared in the last 42 numbers So if it did - color "Green" and if not - color " RED" PLEASE HELP!! Anybody!! Thanks K -- kylekoopma ----------------------------------------------------------------------- kylekoopman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2736 View this thread: http://www.excelforum.com/showthread.php?threadid=46869 Use conditional formatting. Select row 43 down and use this form...

CELL FORMATTING #9
WHY IS MY CELL FORMAT AT WORK DISPLAYED PROPERLY AS -1,500.OO IS DISPLAYED AS (1,500.00) AND WHEN i DO IT AT HOME Ii CANNOT GET THE CLOSEST I GOT IS -1,500.00 is there a diffence between the formatting cell options between Excel's programs.. Thanx. Hi Bumpa! Excel takes it from your Windows Regional options. Use: Start > Settings > Control Panel > Regional options Change the negative number format You'll find that you now have () options. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classific...

Adaptation Of A Formula
I have the following formula in a cell. This gives me the number of how many Bank Holidays have passed in this tax year. =HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713,38719;0,1,2,3,4,5,6},2) (courtesy of N. Harkawat) In any one tax year there can be up to 10 Bank Holidays. (This year there are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an automated way. On a separate sheet, I could put in A1 to J1 for instance, the dates of the Bank Holidays in the current tax year. In cells B1 to J1 I could have the numbers 1 to 10. (Or any other way o...

hyperlink formula
=HYPERLINK("#a123"; "Next week") The first week is placed on a123, then the second 41 rows under at a164, and then third further 41 rows down at 205. Is there a formula I could use in this case. -- kimare ------------------------------------------------------------------------ kimare's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16501 View this thread: http://www.excelforum.com/showthread.php?threadid=320384 =HYPERLINK("#" & ADDRESS(ROW()+41,1,4),"next week") Will allow you to put the formula on any cell in the...

#VALUE! on correct formulas
I am using 'vlookup' and 'match' functions that result in #VALUE!, but when I hit function wizard, it shows the correct number value I am looking for and expecting in the result area. Apparently my syntax, etc. is accurate. Why, then, does #VALUE! show up when everything is ok? thanks, mike allen mike allen wrote... >I am using 'vlookup' and 'match' functions that result in #VALUE!, but when >I hit function wizard, it shows the correct number value I am looking for >and expecting in the result area. Apparently my syntax, etc. is accurate. >...

Unlocked cell formatting
I have some cells locked & sheet protected. After the sheet is protected, all formatting tools get inoperative. I want the users to have choice for formatting unlocked cells. How to solve this? --- Message posted from http://www.ExcelForum.com/ Hi, I hope this make sense. If you go in the menu to TOOLS -> PROTECTION --> PROTECT SHEET Then you have the chance to allow user to format Cells, Columns and Rows by ticking in the small square boxes that are right below the space where you write your password. So, by ticking in these options you choose what level of protection you wa...

How to Clear autoshapes out of a cell
I have a cell that has 10 autoshape squares in it. how can i just clear the cell of them? its part of about 100 other cells full of these shapes. the squares are tiny and to click on each one to delete or to group them and then delete is too time consuming. is there a better way? deleting the cell causes issues with the rest of the form, with me having to insert a new cell in its place.. I also have another Q if your a cell making wizard! this is basically an inventory worksheet for bunch of toys we use as decor. the stock on hand changes daily its set up as follows Item number: item...

Shading cell in custom colour
Can I shade a cell in a custom colour, or is there a bigger choice than the default choices (I want a lighter grey than the 25% grey) Thanks -- Turnipboy ------------------------------------------------------------------------ Turnipboy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24527 View this thread: http://www.excelforum.com/showthread.php?threadid=486869 XL can display only the 56 colors in the color palette. However, if you open the palette (Tools/Options/Color) and click Modify, you can replace any of the colors with a custom color. In articl...

Pasting into merged cells
I have a merged cell in which I want to paste text from another cell. Every time I try get an error message saying that the cellsI have to unmerge the merged cells first, paste, and them remerge. Is there any way to avoid doing this? It is a real pain. If you copy only the text from the formula bar of the first cell you can paste it into the formula bar of the second cell without going throught the unmerge dance. Alternately you could just put a formula in the merged cell referencing the cell you want to copy from. You can than Copy-PasteSpecial-Values in the merged ...

How do I add a formula?
How do I add a formula to find 15% of an amount? The amounts are listed in column D. I want to see 15% in Column E Hi, =D1*15% Regards, Jaleel "Lo" wrote: > How do I add a formula to find 15% of an amount? The amounts are listed in > column D. I want to see 15% in Column E On the off-chance that the 15% is included in the amount, you can use =A1*(1-1/1.15) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jaleel" <Jaleel@discussions.microsoft.com> wrote in message news:717ABC7C-2FAD-4C35-9759-CFA83A86844...

Count numbers formed from another formula
Hi. I'm trying to count a set of #s that is formed by another formula. For example, I have a list of numbers from c1-c6 which all have formulas in the cell which creates that number. When I try to do functions counting the #s, etc., it will not bring back a value. It doesn't want to count what has been formed from another formula. Please help! Thanks! -- Thanks! Stephen Normally, COUNT(C1:C6) will count all of the values in column A, including the zeros, but will ignore nulls ( "" ).............. Normally, COUNTA(C1:C6) will also include the nulls.......... What is ...

cell reference
hi, i have formula in cell A5 and when i double click, it takes me to the respective cell. my question how can i go back to that cell (A5) Hit the F5 key / enter A5 / hit OK. If my comments have helped please hit Yes. Thanks. "Rohit" wrote: > hi, > i have formula in cell A5 and when i double click, it takes me to the > respective cell. my question how can i go back to that cell (A5) As ALWAYS, post your code for comments. Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Address <> Range("g...

Formula?
I'm trying to add a formula in a cell which will leave the cell blank if column A is blank. I use the following: ActiveCell.FormulaR1C1 = "=if(+RC[-10]>0,2.5,"")" but I get a run-time error (1004). I know the problem is the quotes in the third argument. I tried replacing the quotes with zero and it enters zero correctly with no error. But I need the cell to be blank, not zero, if column A is blank. Hi Try four quotes instead of two: "=if(+RC[-10]>0,2.5,"""")" -- HTH. Best wishes Harald Followup to newsgroup only please ...