Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GetPivotData with variable criteria input? (revisited!)
Hi,
Back in 2004, Jeff Borden asked about using GetPivotData with variable criteria output. Here's his original question: http://tinyurl.com/2saafw I have exactly the same issue. Debra Dalgleish supplies a working solution, which is great, but its complexity makes maintenance an issue. I am asking this question again in case anyone has any other ideas? I have a pivot table with 5 column fields. On a different sheet, I want the user to be able to enter between one and 5 criteria values and to have one GetPivotData function that will catch them all. For example, a user could enter any one of the three following options in the five criteria boxes: c1 c2 c3 c4 c5 In In Out Off On In Out Out In The problem is that the GETPIVOTDATA function returns '#REF!' because nothing in the pivot table matches for c4 and c5 criteria of "" (blank). My first thought it is to write a custom VBA function, GetPivotDataEx which first checks for blank criteria, and then calls GetPivotData itself with the empty parameters removed. Using VBA is okay within our organisation, but I would rather avoid it. Thanks in advance! Andy |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a worksheet name as an input variable to a formula? | Excel Discussion (Misc queries) | |||
With QUERY how to input a variable | Excel Discussion (Misc queries) | |||
Formulas containing variable input | Excel Discussion (Misc queries) | |||
Variable Input Range for Combo Box | Excel Worksheet Functions | |||
can you use a variable or cell reference in a getpivotdata formul. | Excel Worksheet Functions |