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........................
"WCoaster" <(E-Mail Removed)> wrote in message
news:1E204351-6D39-4EFC-88D2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:E294CA6A-AC41-4894-8F6A-(E-Mail Removed)...
>> >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
>>
>>
>>
|
Similar Threads
