Pivot Tables and Range Names

[David Evans]

I use a Pivot Table to Summarize from PowerPivot, which itself derives data from numerous sources. I have to group the output, but as the grouping is non-linear, I cannot use the built in Pivot Table grouping. Instead I use a table on an adjacent sheet and use sumproduct to break out the grouping. The dynamic ranges are driven by a named range that counts the values in a column of the Pivot Table entitled "Assets" - if the user decides to vary the pivot Table layout, sometimes the Asset column is in Col C, others in D or E. Is there a way to return the column in which a "Assets" appears on the worksheet, when it exists as part of a Pivot Table header?
Currently I define the Range name for AcctData.Assets as =OFFSET(Pivot!$C$13,1,0,AcctData.NumRows,1) but I'd like the reference to the column to be tied to the Column whcih contains the Pivot Table heading "Assets"

Any solutions will be gratefully accepted!

 

[David Evans]

OK - So I've obviously stumped (Cricket World Cup reference!) the Bored!
Here's a slight variation on my original terribly worded question .... (I knew it had to be me ...)

I use the following formula to return the cell reference that contains the heaing I seek - "Assets" - "=ADDRESS(SUMPRODUCT((Pivot!B4:F20=" Assets")*ROW(Pivot!B4:F20)),SUMPRODUCT((Pivot!B4:F20=" Assets")*COLUMN(Pivot!B4:F20)),4)" it returns "C12" which is correct! now the cell I want to start my dynamic range from is C13, however when I pop the above into OFFSET it tells me that I have a bad formula ... However the following works fine - =ROW(OFFSET(C12,1,0,1,1))
Okay Ninjas - point out my inadequacies one more time ....

 

[KenU]

@David Evans,

I believe you simply need to wrap the ADDRESS formula in INDIRECT in order to evaluate the text as a range reference.

Hope that helps.

Regards,
Ken

 

[David Evans]

@David Evans,

I believe you simply need to wrap the ADDRESS formula in INDIRECT in order to evaluate the text as a range reference.

Hope that helps.

Regards,
Ken

Thanks Ken! - Wrapping the ADDRESS formula in INDIRECT returned "Assets" which is the content of the cell, however, when I throw the "wrapped" formula into other formulas it returns what I need!
Appreciate your help!