Multiple EVDRE

[Former Member]

We have a schedule with multiple EVDRE in two sheets.

Expansion of one will produce a list of members based on criteria which should be the list of members we should expand on the other EVDRE.

How do i ensure the first EVDRE always expands first before the dependent EVDRE expands. What has worked before. For us the sequence is not always consistent.

Appreciate your inputs.

-AD

[former_member190501 · Accepted solution]

Hi,

In Sheet 1put a evdre which produces list of members and check following option.

Set Workbook Options>Refresh Options>EVDRE:Refresh by Sheet

In second sheet, you can put second EvDre which generates out put based on members generates in sheet 1.

When you click expand all in Sheet 1 respective EvDre will get refreshed to generate list of members, movement when you click sheet 2 at that point of time 2nd EvDre will get refreshed by that time your first EvDre got result.

This option will work only if you do not hide Sheet 1.

Hope it helps...

regards,

Raju

[Former Member · Accepted solution]

Raju,

Thank you for your quick response. Let me clarify that..

User will see only one sheet and the hidden sheet with first EVDRE is a supporting sheet hence user would never land or take action on that sheet. Hope i am clear with our requirement.

[Former Member · Accepted solution]

Hi Andrew,

One way that I believe that you could handle with this is via VBA.

I don't understand right if you want to refresh the second/hide sheet after expand, refresh or send data, anyway you will need a function as describe at this [help document|http://help.sap.com/saphelp_bpc75_nw/helpdata/en/40/54267f9051402eb27cbfc70492de87/content.htm], I'll use the expand as example, but you can use all of then.

First of all, you will need mark the option: "EVDRE: Refresh by Sheet". And check the Expand settings too.

So, now you need tip the following VBA code in your sheet, changing it as necessary.

Function AFTER_EXPAND(Argument As String)
    If ActiveSheet.Name = "Plan1" Then
'freeze the screen
        Call SetPerformance(True)
'select the second sheet
        Sheets("Plan2").Select
'refresh the second sheet
        Call expandSheet
'select the first sheet
        Sheets("Plan1").Select
'unfreeze the screen
        Call SetPerformance(False)
    End If
    AFTER_EXPAND = True
End Function

Private Sub SetPerformance(ByVal bAlta As Boolean)

If bAlta Then
    Application.Cursor = xlWait
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
Else
    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
End If

End Sub

Sub expandSheet()
    Application.Run "MNU_ETOOLS_EXPAND"
End Sub

It's just a simple code, you can improve as you need.

Regards.

Lucas

[Former Member]

Hi Andrew,

It sounds like you need to get a list to expand what you need for the business, I had same cases and realized there is no way to make sure the expand sequence as the standard. The only way I made was to use VBA. Here is the step I did.

There is a button to refresh the business data, it contains VBA to control two EvDREs. Let assume there are two EvDREs in A1 cells at two sheets(A and B). A sheet is the hidden and B sheet is to show the business data.

VBA code,

1. Write the EVDRE formula to A1 of A sheet

2. And do MNU_eTOOLS_EXPAND

3. Once it is finished, delete A1 of A sheet.

4. Then update MEMBERSET of EVDRE of B sheet

5. Write the EVDRE formula to A1 of B sheet

6. And do MNU_eTOOLS_EXPAND

7. Once it is finished, delete A1 of B sheet.

Hope this help.

Regards,

YH Seo

====================================================================================================

We have a schedule with multiple EVDRE in two sheets.

Expansion of one will produce a list of members based on criteria which should be the list of members we should expand on the other EVDRE.

How do i ensure the first EVDRE always expands first before the dependent EVDRE expands. What has worked before. For us the sequence is not always consistent.

Appreciate your inputs.

-AD