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