software

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

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 :-)
------------------------------*------------------------------*----------------
quot;WCoasterquot; gt; wrote in message
...
gt;I am trying to use GETPIVOTDATA() to reference a specific field in a pivot
gt; table. The criteria I am using is a date field. The formula works fine as
gt; long as the date being searched for is present in the pivot table ELSE
gt; #REF
gt; is returned. I would like to be able to find the next lowest result
gt; available
gt; if the exact date does not exist. Sortof like MATCH(value,array,1).
gt;
gt; I have a couple of ideas, neither of which I can figure out how to
gt; implement.
gt;
gt; 1. Some sort of nested GETPIVOTDATA() for the date field.
gt; 2. A formula that creates an array from the PT and returns the correct
gt; result to the date field in GETPIVOTDATA()
gt; 3. Open to any other suggestions.
gt;
gt; Thanks
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.
quot;Ken Wrightquot; wrote:

gt; Assuming your source data is sorted on date, then why not have a helper cell
gt; that has the target date you are after, then have another helper cell with
gt; an INDEX/MATCH combination that looks up the date in your first helper cell
gt; within the source data table, as this will then return either a match or the
gt; next lowest date.
gt;
gt; Then just refer to the second helper cell as the data argument of the
gt; GETPIVOTDATA formula.
gt;
gt; --
gt; Regards
gt; Ken....................... Microsoft MVP - Excel
gt; Sys Spec - Win XP Pro / XL 97/00/02/03
gt;
gt; ------------------------------Â*------------------------------Â*----------------
gt; It's easier to beg forgiveness than ask permission :-)
gt; ------------------------------Â*------------------------------Â*----------------
gt;
gt;
gt;
gt; quot;WCoasterquot; gt; wrote in message
gt; ...
gt; gt;I am trying to use GETPIVOTDATA() to reference a specific field in a pivot
gt; gt; table. The criteria I am using is a date field. The formula works fine as
gt; gt; long as the date being searched for is present in the pivot table ELSE
gt; gt; #REF
gt; gt; is returned. I would like to be able to find the next lowest result
gt; gt; available
gt; gt; if the exact date does not exist. Sortof like MATCH(value,array,1).
gt; gt;
gt; gt; I have a couple of ideas, neither of which I can figure out how to
gt; gt; implement.
gt; gt;
gt; gt; 1. Some sort of nested GETPIVOTDATA() for the date field.
gt; gt; 2. A formula that creates an array from the PT and returns the correct
gt; gt; result to the date field in GETPIVOTDATA()
gt; gt; 3. Open to any other suggestions.
gt; gt;
gt; gt; Thanks
gt;
gt;
gt;

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,quot;quot;)

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........................quot;WCoasterquot; gt; wrote in message
...
gt; Thanks Ken,
gt;
gt; Actually when I woke up this morning that was exactly the solution that
gt; came
gt; to mind. However, the data is sortd as follows.
gt;
gt; Model, Plant, Due Date, Source, then the Data to be returned. So I first
gt; have to determine what Model then what Plant to get to the sorted date
gt; range
gt; that is causing the trouble.
gt;
gt; I thought of moving Date to the begining of the table because
gt; GETPIVOTDATA()
gt; will still retrieve the correct result but the helper cell would then
gt; return
gt; the first incidence of the next earliest date and that may not be the
gt; record
gt; I am looking for.
gt;
gt; I think I need to determine the number of records available after I have
gt; determined where they are located within the Model and Plant list.
gt;
gt; More ideas greatly appreciated I am still trying to work with your
gt; solution
gt; and some type of lookup or difine array function.
gt;
gt;
gt;
gt; quot;Ken Wrightquot; wrote:
gt;
gt;gt; Assuming your source data is sorted on date, then why not have a helper
gt;gt; cell
gt;gt; that has the target date you are after, then have another helper cell
gt;gt; with
gt;gt; an INDEX/MATCH combination that looks up the date in your first helper
gt;gt; cell
gt;gt; within the source data table, as this will then return either a match or
gt;gt; the
gt;gt; next lowest date.
gt;gt;
gt;gt; Then just refer to the second helper cell as the data argument of the
gt;gt; GETPIVOTDATA formula.
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt; Ken....................... Microsoft MVP - Excel
gt;gt; Sys Spec - Win XP Pro / XL 97/00/02/03
gt;gt;
gt;gt; ------------------------------*------------------------------*----------------
gt;gt; It's easier to beg forgiveness than ask permission :-)
gt;gt; ------------------------------*------------------------------*----------------
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;WCoasterquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am trying to use GETPIVOTDATA() to reference a specific field in a
gt;gt; gt;pivot
gt;gt; gt; table. The criteria I am using is a date field. The formula works fine
gt;gt; gt; as
gt;gt; gt; long as the date being searched for is present in the pivot table ELSE
gt;gt; gt; #REF
gt;gt; gt; is returned. I would like to be able to find the next lowest result
gt;gt; gt; available
gt;gt; gt; if the exact date does not exist. Sortof like MATCH(value,array,1).
gt;gt; gt;
gt;gt; gt; I have a couple of ideas, neither of which I can figure out how to
gt;gt; gt; implement.
gt;gt; gt;
gt;gt; gt; 1. Some sort of nested GETPIVOTDATA() for the date field.
gt;gt; gt; 2. A formula that creates an array from the PT and returns the correct
gt;gt; gt; result to the date field in GETPIVOTDATA()
gt;gt; gt; 3. Open to any other suggestions.
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt;
gt;gt;
gt;gt;