Dynamic report linked to pivot table

Hello murarihyd

I am trying to return our spreadsheet returned with some ideas on use of DSUM, DMAX and DMIN functions bye-passing your pivot tables

When and if you receive it, please see Formulae and Criteria areas on your SUMMARY worksheet. I have given the data on your MASTER worksheet the range name 'MyData'.

I am having trouble making the attachment. I have all the required rights, but the buttons are not working for me.

There we go ...

Hi murarihyd,

Have you considered Dynamic Named Ranges? This might be just what you need.
http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.beyondtechnology.com/geeks007.shtml

I think MarvinP's point was more that you create a DNR based off Pivot2 data such that as the Pivot itself expands/contracts so too does the Named Range, eg:

Name: _Pivot2Table
RefersTo: ='Pivot-2'!$A$5:INDEX('Pivot-2'!$A:$IV,MATCH(REPT("Z",255),'Pivot-2'!$5:$5),MATCH(9.99E+307,'Pivot-2'!$A:$A))

we assume we're not interested in Grand Total row/column as may not be visible at any given time and that "DATE" appears in A5 at all times.

You can then reference the Named Range using INDEX as you are presently with the fixed range.

I don't know what you mean by "Name Box" but if you're referring to the drop down above A1 it won't appear there.

Having added the Name as outlined - if you then use:

=ROWS(_Pivot2Table)

what is the result ? #REF! or other ?

As outlined the Defined Name just created will not appear in that box but it does exist - hence my request you test the ROWS function as outlined and report back with the result.

On an aside - do not use CAPS in your general narrative - CAPS are seen as shouting.

In terms of guidance - I think we're doing the best we can - "spoon feeding" to the extent that you don't do these things for yourself is not going to help you in the long run.
Far better that you get to grips creating and using these Names/formulae yourself than us simply hand you the finished article.
If you're looking for the latter then you should be employing consultants.

Hi,

When I suggested Dynamic Named Ranges - here is an example.

On your Summary Sheet you have a formula in E3: of

=+SUM(INDEX('PIVOT-2'!$B$6:$I$58,0,ROWS(E$3:E3)))

You said that this range will expand and the $B$6:$J$58 will get bigger as more rows are added to the Pivot Table.

If you define a Dynamic Named Range of

Pvt2Data = OFFSET('PIVOT-2'!$B$6,0,0,COUNTA('PIVOT-2'!$B:$B),COUNTA('PIVOT-2'!$5:$5))

Then in your formula above instead of using the Static Range you replace it with the name and it becomes:

=+SUM(INDEX(Pvt2Data,0,ROWS(E$3:E3)))

Now when your pivot table data grows the Dynamic Named Range of Pvt2Data also grows and all your formulas continue to work.

Looking at some of your CSE formulas, you may need to create a few Dynamic Named Ranges to have them continue to work.

I hope this helps explain where I was going.
http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm for more examples.

FWIW, given the nature of the calculations I'd be wary of using a DNR with a Volatile construct (hence prior INDEX based suggestion)