Formulas in Workbook causing the Workbook to open very slowly

[Former Member]

Hi Everyone,

I have a very long pending issue fro our users. We have a workbook which has two queries inserted. And there is a Formula which is hardocded up to 5000 rows. And due to thiis workbook is opening very slolwly.

The Formulas is

=IF(BExGetData($X$3,$X$6,$B13,$X$4,$X$5,$G13,W$11,W$12)="#NV",,BExGetData($X$3,$X$6,$B13,$X$4,$X$5,$G13,W$11,W$12))

The above formula replaces #NV value with a blank and then if there is any other value apart from #NV, then it calculates a different value in the back end. Since the business , does not know howmany rows of data comes, so he hardcoded the formula for 5000 rows as this is the maximum count.

Can anyone please provide a solution to avoid so many hardcoded formulas and have an alternative using some macross.

Please feel free to ask questions?

Regards,

A

[Former Member]

Any ideas on this.............. This is a IP Workbook . Basically the formualas are written to 5000 times in the workbook to perform those functions. So, when there are no 5000 rows of records, then also formulas are calculated. Is there any way to write a Macros for the same and include the formula once in the Workbook and loop itusing macros code. And this loop should work for the no.of rows that are present in the workbook. may be 1 rowto 5000 rows and so on.

Regards,

A

[Former Member]

Closed