View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
WCoaster
 
Posts: n/a
Default Creating Array from Pivot Table

Thanks Ken,

Actually when I woke up this morning that was exactly the solution that came
to mind. However, the data is sortd as follows.

Model, Plant, Due Date, Source, then the Data to be returned. So I first
have to determine what Model then what Plant to get to the sorted date range
that is causing the trouble.

I thought of moving Date to the begining of the table because GETPIVOTDATA()
will still retrieve the correct result but the helper cell would then return
the first incidence of the next earliest date and that may not be the record
I am looking for.

I think I need to determine the number of records available after I have
determined where they are located within the Model and Plant list.

More ideas greatly appreciated I am still trying to work with your solution
and some type of lookup or difine array function.



"Ken Wright" wrote:

Assuming your source data is sorted on date, then why not have a helper cell
that has the target date you are after, then have another helper cell with
an INDEX/MATCH combination that looks up the date in your first helper cell
within the source data table, as this will then return either a match or the
next lowest date.

Then just refer to the second helper cell as the data argument of the
GETPIVOTDATA formula.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------



"WCoaster" wrote in message
...
I am trying to use GETPIVOTDATA() to reference a specific field in a pivot
table. The criteria I am using is a date field. The formula works fine as
long as the date being searched for is present in the pivot table ELSE
#REF
is returned. I would like to be able to find the next lowest result
available
if the exact date does not exist. Sortof like MATCH(value,array,1).

I have a couple of ideas, neither of which I can figure out how to
implement.

1. Some sort of nested GETPIVOTDATA() for the date field.
2. A formula that creates an array from the PT and returns the correct
result to the date field in GETPIVOTDATA()
3. Open to any other suggestions.

Thanks