PAX - Excel calculation based on Dynamic Report (Active Form)

[EP_explorer]

I'm trying to make some calculation in Excel based on data from Dynamic Report (which using MDX filtering in it)
So at first step
I open Dynamic Report (Active Form) which shows data on Sheet2 and make simple link from column J to Sheet1 column A
It seems works

01.JPG (135.09 KiB) Viewed 2501 times

After it I make filtering in Dynamic Report (show rows only which contain TOTAL). Only 3 rows
And the most part of my links on Sheet1 shows like #REF (and next calculations based on column A are imposible)

02.jpg (80.03 KiB) Viewed 2501 times

So may be you know how avoid such sort of behavior

[burnstripe]

On dynamic reports the data rows are destroyed upon rebuild, so any direct reference to these rows will be destroyed on rebuild. Hence the links are broken as you are experiencing.

One workaround would be to use the offset function were the range start is above the dynamic rows, then when the report rebuilds the link is intact because the formulae doesn't refer to any cells in the dynamic report directly

[EP_explorer]

Thank you
it helped

May be you know
another workarounds except offset?

In some places write that it is resource consuming

[WilliamSmith]

You can try using Indirect() and passing in the cell reference as a string.

[burnstripe]

Thank you
it helped

May be you know
another workarounds except offset?

In some places write that it is resource consuming

The other question I guess is does sheet1 need be linked to sheet2 like this. I.e. What's stopping sheet1 from also being a dynamic report sharing the same filters as sheet2? To assist you with this we'd need to know more detail on the content you are trying to create in sheet1

[EP_explorer]

You can try using Indirect() and passing in the cell reference as a string.

Yes
It works as well
Thank you

[EP_explorer]

Thank you
it helped

May be you know
another workarounds except offset?

In some places write that it is resource consuming

The other question I guess is does sheet1 need be linked to sheet2 like this. I.e. What's stopping sheet1 from also being a dynamic report sharing the same filters as sheet2? To assist you with this we'd need to know more detail on the content you are trying to create in sheet1

I'm creating some sort of filter for rows
I find unic elements in column PARENT_ELEMENT_CODE
and put them in list (sort of combobox)

03.JPG (12.81 KiB) Viewed 2424 times

After I choose one of elements I see only rows which contains TOTAL in Column PARENT_ELEMENT_CODE

04.JPG (13.69 KiB) Viewed 2424 times