Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA in Excel 2007
Hello.
I'm using Excel 2007. I have this formula in a cell: =GETPIVOTDATA("Overall Rating",$DU$7,"fweek","200829") When I change the fweek value to a cell refrence as in the formula below, I get a #REF error (where DY22 contains the value "200829"): =GETPIVOTDATA("Overall Rating",$DU$7,"fweek",DY22) What is puzzling is that I have many other formulas just like this that work. The only difference is that those formulas reference pivot tables that were originally built in 2003 (the file was converted from 2003 to 2007) and my formula that errors out is referencing pivot tables built in 2007. Any thoughts on what the problem could be? Any assistance is appreciated. Thanks. -- Jeff |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA in Excel 2007
Hi Jeff,
I can't duplicate your problem. If I change a GETPIVOTDATA static argument to a cell reference containing that entry everything works fine. If there are other formulas of the same nature that are working I would look to make sure the 200829 entry in cell DY22 does infact match an entry in the pivot table. For example if you enter 200829 followed by a space in DY22 it will fail. Or if the pivot table entry 200829 is followed by a space but the entry in DY22 is not you will get an error. Simply put the two entries must be exactly the same. -- Cheers, Shane Devenshire "Jeff Reese" wrote: Hello. I'm using Excel 2007. I have this formula in a cell: =GETPIVOTDATA("Overall Rating",$DU$7,"fweek","200829") When I change the fweek value to a cell refrence as in the formula below, I get a #REF error (where DY22 contains the value "200829"): =GETPIVOTDATA("Overall Rating",$DU$7,"fweek",DY22) What is puzzling is that I have many other formulas just like this that work. The only difference is that those formulas reference pivot tables that were originally built in 2003 (the file was converted from 2003 to 2007) and my formula that errors out is referencing pivot tables built in 2007. Any thoughts on what the problem could be? Any assistance is appreciated. Thanks. -- Jeff |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA in Excel 2007
Hi again,
The longer formula is: =IF(ISERR(INDEX($C$4:$C$22,LARGE((MAX($E$4:$E$22)= $E$4:$E$22)*ROW($E$4:$E$22),ROW(A1))-3,1)),"",INDEX($C$4:$C$22,LARGE((MAX($E$4:$E$22)=$ E$4:$E$22)*ROW($E$4:$E$22),ROW(A1))-3,1)) Again it must be array entered. -- Cheers, Shane Devenshire "Jeff Reese" wrote: Hello. I'm using Excel 2007. I have this formula in a cell: =GETPIVOTDATA("Overall Rating",$DU$7,"fweek","200829") When I change the fweek value to a cell refrence as in the formula below, I get a #REF error (where DY22 contains the value "200829"): =GETPIVOTDATA("Overall Rating",$DU$7,"fweek",DY22) What is puzzling is that I have many other formulas just like this that work. The only difference is that those formulas reference pivot tables that were originally built in 2003 (the file was converted from 2003 to 2007) and my formula that errors out is referencing pivot tables built in 2007. Any thoughts on what the problem could be? Any assistance is appreciated. Thanks. -- Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GETPIVOTDATA in Excel 2007 | Excel Worksheet Functions | |||
GETPIVOTDATA in Excel 2007 | Excel Worksheet Functions | |||
GETPIVOTDATA....Excel 2000....Desperation! | Excel Worksheet Functions | |||
GetPivotData in excel for Mcintosh | Excel Worksheet Functions | |||
Dynamic Getpivotdata in Excel XP | Excel Worksheet Functions |