• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Dynamically returning the cell position of a field in a pivot


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

  • 2
1 Solution
*** Edited ***

LOOKUP approach, put this in cell D5 and copy down:

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:

See attached.

fredericgilbertAuthor 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.


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...
fredericgilbertAuthor 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now