Formula Optimization

Asked

Viewed 57 times

2

Could you help me find a way to optimize the formula below? The spreadsheet has more than 125 thousand lines with this formula in a 4 columns and is generating a lot of slowness to process the information and even to open it.

=SE(
    $D125883="Receita";
    SEERRO(
        SEERRO(
            PROCV(
                $C125883;
                'X:\(Caminho Arquivo)\[Ago.15 a Jan.18.xlsx]%Venc'!$B$5:$AK$1048576;CB$1;FALSO
            )*$R125883;
                SE(
                    PROCV(
                        $J125883;
                        'X:\(Caminho Arquivo)\[Ago.15 a Jan.18.xlsx]%Venc_Cliente'!$H$4:$J$1048576;3;FALSO
                    )=CB$2;$R125883;0
                )
        );
        'X:\(Caminho Arquivo)\[Ago.15 a Jan.18.xlsx]%Venc'!AF$9454*$R125883
    );
    SE(
        SEERRO(
            PROCV(
                $J125883;
                'X:\(Caminho Arquivo)\[Ago.15 a Jan.18.xlsx]%Venc_Cliente'!$H$4:$J$1048576;3;FALSO
            );
            'X:\(Caminho Arquivo)\[Ago.15 a Jan.18.xlsx]%Venc_Cliente'!$F$2
        )=CB$2;$R125883;0
    )
)

Thank you for your help.

  • Or you reprogram the spreadsheet in VBA with the optimized ways to perform them. Or disable the automatic cell calculation and only enable at a certain time of day, for example: at lunch or after making the changes. And then disable again. There are many lines with automatic calculation in each cell and interaction with other sheets.

  • Thanks for the info @danieltakeshi!!

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.