I dunno if this was done already but,

One way i use pivot tables is often to layout some complex Data that i want
to VLOOKUP into afterwards. I know what i could use GetPivotData, but it
requires too much specific information at times to be an efficient way to
lookup my values.

I also often add some information from other data source in different sheets
(tables,queries,pivots,etc) in the columns following the pivot table columns.

The way the data is display is very efficient but accessing that data for
use in formulas often gets tricky.

Picture this display of pivot (not the real layout of pivot):

A | B | C |
------+-----------+-----------------+-------------------+
1 | region | salesperson | Sum of Sales |
------+-----------+----------------+--------------------+
2 | North | John | 100$ |
------+ +----------------+--------------------+
3 | | Paul | 50$ |
------+-----------+----------------+--------------------+

if in D2 and D3 i add the following formula, i get into problems

=VLOOKUP(A2&"-"&B2,MarginsPerRegionPerSalesPerson, 2, 0)

The proble comes on Row 3. Since A3 is blanked because it is grouped with
A2, A3's value will be empty.

Would it be possible that references to any cell in a grouped cell range to
return the value of the grouped cell, instead of the value of the looked up
cell ?

What i would love is for A3 to return "North" when i access it. A3 should
know it is grouped with A2 so the real value of A3 is really the same as A2.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc