Return Address of GetPivotData result?
Dear Forum,
let's assume following setting:
I have a workbook consisting of two Sheets. "B" contains a Pivot Table.
"A" contains "GetPivotData" formulas reffering to sheet "B". Here certain criteria of Sheet "B", e.g. Month = 10 and Year = 2009, are checked and if found, the respective PivotData returned.
My question: Is there a possibility, to not return the value, but the respective cell's address in sheet "B"?!
Hope You can help me fix this!
Regards,
Jan
Maybe I should add, that a regular Lookup is not possible, because in sheet "B" there are several Pivot Tables and one specific value might occur to be listed several times though.
Regards,
Jan
After several different trial & error approaches, the problm still exists.
ANY SUGGESTIONS? Plz help me!
I don't really see this as being viable - for me the possibilities in terms of layout etc are too vast... for ex.
a) a given field may appear multiple times in any PT
(used in row & data fields etc...)
and
b) the criteria for a given field may appear multiple times (aggregation) - establishing the correct one is thus potentially convoluted.
Based purely on the above I would assume that establishing the intersect point is then pretty difficult... in fact it's makes you realise just how clever the GETPIVOTDATA function is.
Perhaps you could iterate each cell within the databodyrange and analyse against the result of the GETPIVOTDATA function and where it matches further validate the parent fields (row items [1-n], column items[1-n]) against those used in the GETPIVOTDATA function to see if all tie out ?
Very interesting question though and I would dearly love to be shown an way of doing this!
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks