Excel-getting the extreme values of a data group

Hi Emma

In continuation to the query-response on the subject, I have one more concern.
Please find the attached.

Function in D6:
=VLOOKUP(GETPIVOTDATA("Min of Sl. No.",$A$4,"Operator","Kumar"),Sheet1!A:B,2)
Its result in D6:
1234_0458.1019.tif
This function in column-D has the operator "Kumar".
When I drag down this function to further cells, the operator is not changing and so is the result.

Why isn't the operator not changing in the function? Do I need to edit the operator every time? Please advise.

Another point on which I need your advise is...
In column-A I have just put a Sl. No. instead of changing the Document # (text field)into a unique number (numeric field)using functions "Value" and "Substitute".
It is working good for now, but I want to check with you before I go further.
Will there be any complications in future using Sl. No. Please advise.

Thanks & Regards
Kumar.

AttachmentSize
Allotment.xlsx17.8 KB
Almir's picture

Excel-getting the extreme values of a data group

I hope Emma will not mind if I interfere...
Problem with D6 cell along the pivot table is argument "Kumar", which is fixed value. When you copy formula down, it does not change. Instead of "Kumar" select cell A6. Then copy formula down. The same applies to the cell E6.
Regarding changing name of pivot table field from Document # to Sl. No., I see no problem in the future.
Maybe Emma has something to add?

Hi Almir Good to hear from

Hi Almir

Good to hear from you again.

As suggested, I have made the changes in the function as
=VLOOKUP(GETPIVOTDATA("Min of Sl. No.",$A$4,"Operator",A6),Sheet1!A:B,2)
and now I am able to get the results properly by duplicating the same down to the cells.

Happy to hear that there is no problem with the usage of Sl. No. to get the Min & Max values.

Thank you for the response.

Regards
Kumar.