# Dynamically returning the cell position of a field in a pivot

on
Hi,

I have a pivot with data about search rankings.
The data consists of keywords matching webpages for many given positions.

Objectives: to build a formula that retrieves the rank of each keyword on a per webpage basis such as this one:
=(GETPIVOTDATA(TEXT("Rank",),\$A\$1,"Product","Internet","Webpage",\$A\$5,"Keyword",\$A5))

Constraint:the formula needs to be pasted into a very long column alongside the companion pivot table without any modification.

The Catch: some keywords are associated with more than one webpage. Therefore, the hardcoded "Webpage" cell position (\$A\$5) in the formula above has to be modified as often as there are webpages in the pivot table...

Goal: to replace any hardcoded reference to the "Webpage" (\$A\$5 in the formula above)
by something dynamic (address, match, find ???) which would
- look for the closest cell above with a string containing "http://",
- return its position and
- feed it to the getpivotdata formula

I have included a spreadsheet to illustrate the situation.

Thanks for the time you take to review this request for help

fg
20120412-getpivotdata-parentfiel.xlsx
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
*** Edited ***

LOOKUP approach, put this in cell D5 and copy down:
[D2]=IFERROR(GETPIVOTDATA(TEXT(\$B\$4,),\$A\$1,"Product",\$B\$2,"Webpage",LOOKUP(2,1/(LEFT(\$A\$5:\$A5,4)="http"),\$A\$5:A5),"Keyword",\$A5),"")

The LOOKUP function finds the last row in column A starting with "http" and has built-in array functionality so CTRL-SHIFT-ENTER is not required so should be more efficient than those type of array functions deriving the same result (tho, I may stand corrected!)

For more on the LOOKUP function and how it works in this instance, see this tip:
http:/Q_27669653.html#a37829864

See attached.

Dave
20120412-getpivotdata-parentfiel.xlsx

Commented:
Hello Dave,

I accept your solution for my situation.

More important, you just teached me a neat trick with the (2,1/(A:A<>""),A:A) part, very informative with the linked explanations.

Thanks a lot, much appreciated.

fg

Note: yesternight, I worked on it and came up with a cumbersome solution that provided me with the cell position:

Benching against a named range listing all the URLs and upon an error, I was able to retrieve the position of the above cell containing the url then feed it back into the getpivotdata formula to extract the rank...

Commented:
The provided solution is simple yet effective.

However, the most valuable takeaway from Dave resides in the linked information from a previous contribution he made that explains how the Lookup(2,1/(A:A<>""),A:A) works.

See: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27669653.html#a37829864

I learned something important here.
Thanks Dave for your comprehensive solution.

Do more with