Creating Array from Pivot Table

[Guest]

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

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Guest]

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]

Ok well lets assume you put the date column first in Col A and then sort on
that. Now lets also assume you have 3 fields you need to match in order to
get a valid record and these are in Cols B C D. Create 3 helper cells
somewhere (say Z3 Z4 Z5) and label them Val1 Val2 Val3, and in these cells
put the required Model / Plant etc. Using data Validation would be best
here because it ensures the values are absolutely correct.

Now create a helper column to the right of your data (I'll assume Col H) and
use a formula as follows in cell H2 :-

=IF(AND(B2=$Z$3,C2=$Z$4,D2=$Z$5),A2,"")

and then copy down as far as your data goes. Now just use the method I gave
you, but do the Lookup on the helper column which will now contain just the
dates for the valid records according to your criteria.

Regards
Ken........................