| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getpivot date w/ relative reference
I am or have been attempting to extract data from a pivot table. I would
like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1,item) all my efforts have resulted in #ref results. Any insight would be greatly appreciated |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getpivot date w/ relative reference
While creating the formula, you should be able to go to Sheet 1 and
click on the cell that you want to reference. Using your example, where the "11" is a cell reference on Sheet 1: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2) Spidey wrote: I am or have been attempting to extract data from a pivot table. I would like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1,item) all my efforts have resulted in #ref results. Any insight would be greatly appreciated -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getpivot date w/ relative reference
Thanks for your response, but I am still at a loss:
If i use this formula: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") It will return the correct answer of 405. However, if i simply change the "11" to the cell reference J2 (which contains the number 11), i get the error #ref. =+GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2) Is there some syntax that I am missing???? "Debra Dalgleish" wrote: While creating the formula, you should be able to go to Sheet 1 and click on the cell that you want to reference. Using your example, where the "11" is a cell reference on Sheet 1: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2) Spidey wrote: I am or have been attempting to extract data from a pivot table. I would like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1,item) all my efforts have resulted in #ref results. Any insight would be greatly appreciated -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getpivot date w/ relative reference
The building codes in your pivot table are text, instead of real
numbers. The 11 that you typed in the cell is a number, and "11" in the formula is text, so they'll return different results. In the cell, if you type an apostrophe before the number: '11 or format the cell as Text, then enter a number, the reference should work correctly. Spidey wrote: Thanks for your response, but I am still at a loss: If i use this formula: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") It will return the correct answer of 405. However, if i simply change the "11" to the cell reference J2 (which contains the number 11), i get the error #ref. =+GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2) Is there some syntax that I am missing???? "Debra Dalgleish" wrote: While creating the formula, you should be able to go to Sheet 1 and click on the cell that you want to reference. Using your example, where the "11" is a cell reference on Sheet 1: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2) Spidey wrote: I am or have been attempting to extract data from a pivot table. I would like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1,ite m) all my efforts have resulted in #ref results. Any insight would be greatly appreciated -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getpivot date w/ relative reference
i appreciate your help; but i did it both ways: format as text and '11. Any
other suggestions because i am sure at a loss "Debra Dalgleish" wrote: The building codes in your pivot table are text, instead of real numbers. The 11 that you typed in the cell is a number, and "11" in the formula is text, so they'll return different results. In the cell, if you type an apostrophe before the number: '11 or format the cell as Text, then enter a number, the reference should work correctly. Spidey wrote: Thanks for your response, but I am still at a loss: If i use this formula: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") It will return the correct answer of 405. However, if i simply change the "11" to the cell reference J2 (which contains the number 11), i get the error #ref. =+GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2) Is there some syntax that I am missing???? "Debra Dalgleish" wrote: While creating the formula, you should be able to go to Sheet 1 and click on the cell that you want to reference. Using your example, where the "11" is a cell reference on Sheet 1: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2) Spidey wrote: I am or have been attempting to extract data from a pivot table. I would like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1,ite m) all my efforts have resulted in #ref results. Any insight would be greatly appreciated -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getpivot date w/ relative reference
What's the result if you try:
=GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2&"") Spidey wrote: i appreciate your help; but i did it both ways: format as text and '11. Any other suggestions because i am sure at a loss "Debra Dalgleish" wrote: The building codes in your pivot table are text, instead of real numbers. The 11 that you typed in the cell is a number, and "11" in the formula is text, so they'll return different results. In the cell, if you type an apostrophe before the number: '11 or format the cell as Text, then enter a number, the reference should work correctly. Spidey wrote: Thanks for your response, but I am still at a loss: If i use this formula: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") It will return the correct answer of 405. However, if i simply change the "11" to the cell reference J2 (which contains the number 11), i get the error #ref. =+GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2) Is there some syntax that I am missing???? "Debra Dalgleish" wrote: While creating the formula, you should be able to go to Sheet 1 and click on the cell that you want to reference. Using your example, where the "11" is a cell reference on Sheet 1: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2) Spidey wrote: I am or have been attempting to extract data from a pivot table. I would like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1,i tem) all my efforts have resulted in #ref results. Any insight would be greatly appreciated -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getpivot date w/ relative reference
Wow, now i tried "=" & j2 and it didn't work. But Js&"" worked perfect!
Now, how did you know to do that? What does it represent? And thank you for your help and time. "Debra Dalgleish" wrote: What's the result if you try: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2&"") Spidey wrote: i appreciate your help; but i did it both ways: format as text and '11. Any other suggestions because i am sure at a loss "Debra Dalgleish" wrote: The building codes in your pivot table are text, instead of real numbers. The 11 that you typed in the cell is a number, and "11" in the formula is text, so they'll return different results. In the cell, if you type an apostrophe before the number: '11 or format the cell as Text, then enter a number, the reference should work correctly. Spidey wrote: Thanks for your response, but I am still at a loss: If i use this formula: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") It will return the correct answer of 405. However, if i simply change the "11" to the cell reference J2 (which contains the number 11), i get the error #ref. =+GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2) Is there some syntax that I am missing???? "Debra Dalgleish" wrote: While creating the formula, you should be able to go to Sheet 1 and click on the cell that you want to reference. Using your example, where the "11" is a cell reference on Sheet 1: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2) Spidey wrote: I am or have been attempting to extract data from a pivot table. I would like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1,i tem) all my efforts have resulted in #ref results. Any insight would be greatly appreciated -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getpivot date w/ relative reference
You're welcome! Joining an empty string ("") to a number is another way
to change a number to text. Since the other techniques didn't work for you, I hoped this one would. Spidey wrote: Wow, now i tried "=" & j2 and it didn't work. But Js&"" worked perfect! Now, how did you know to do that? What does it represent? And thank you for your help and time. "Debra Dalgleish" wrote: What's the result if you try: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2&"") Spidey wrote: i appreciate your help; but i did it both ways: format as text and '11. Any other suggestions because i am sure at a loss "Debra Dalgleish" wrote: The building codes in your pivot table are text, instead of real numbers. The 11 that you typed in the cell is a number, and "11" in the formula is text, so they'll return different results. In the cell, if you type an apostrophe before the number: '11 or format the cell as Text, then enter a number, the reference should work correctly. Spidey wrote: Thanks for your response, but I am still at a loss: If i use this formula: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") It will return the correct answer of 405. However, if i simply change the "11" to the cell reference J2 (which contains the number 11), i get the error #ref. =+GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2) Is there some syntax that I am missing???? "Debra Dalgleish" wrote: While creating the formula, you should be able to go to Sheet 1 and click on the cell that you want to reference. Using your example, where the "11" is a cell reference on Sheet 1: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2) Spidey wrote: I am or have been attempting to extract data from a pivot table. I would like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1 ,item) all my efforts have resulted in #ref results. Any insight would be greatly appreciated -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getpivot date w/ relative reference
I see, said the Blind man...thanks again, much appreciated.
"Debra Dalgleish" wrote: You're welcome! Joining an empty string ("") to a number is another way to change a number to text. Since the other techniques didn't work for you, I hoped this one would. Spidey wrote: Wow, now i tried "=" & j2 and it didn't work. But Js&"" worked perfect! Now, how did you know to do that? What does it represent? And thank you for your help and time. "Debra Dalgleish" wrote: What's the result if you try: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2&"") Spidey wrote: i appreciate your help; but i did it both ways: format as text and '11. Any other suggestions because i am sure at a loss "Debra Dalgleish" wrote: The building codes in your pivot table are text, instead of real numbers. The 11 that you typed in the cell is a number, and "11" in the formula is text, so they'll return different results. In the cell, if you type an apostrophe before the number: '11 or format the cell as Text, then enter a number, the reference should work correctly. Spidey wrote: Thanks for your response, but I am still at a loss: If i use this formula: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") It will return the correct answer of 405. However, if i simply change the "11" to the cell reference J2 (which contains the number 11), i get the error #ref. =+GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2) Is there some syntax that I am missing???? "Debra Dalgleish" wrote: While creating the formula, you should be able to go to Sheet 1 and click on the cell that you want to reference. Using your example, where the "11" is a cell reference on Sheet 1: =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2) Spidey wrote: I am or have been attempting to extract data from a pivot table. I would like to use a reference in "sheet 1" cell $A1 as the "item" in the getpivotdata (the pivot table is in a separate sheet) formula below. =GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11") =GETPIVOTDATA(data_field,pivot_table,field1 ,item) all my efforts have resulted in #ref results. Any insight would be greatly appreciated -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date formulas | Excel Discussion (Misc queries) | |||
| picture relative to cell reference | Excel Discussion (Misc queries) | |||
| search for latest date | Excel Worksheet Functions | |||
| reference to a bimonthly date | Excel Discussion (Misc queries) | |||
| How Do I Get a date reference when linking excel worksheets? | Excel Worksheet Functions | |||
Linear Mode
