In a table, i have one column (let say Column A) where the formula is
referred to another workbook. The formula works well, until i do an
auto-filtered on another column (Column B). The formula in Column A would
then displayed "#Value!". The formula would works well only if i have the
other workbook opened.

How do i do an auto-filter without getting the error on the formula?

Pls advice.

Thanks

I know that there are some worksheet functions that only work if the other
workbook is open. A few of them are =countif(), =sumif(), =indirect().

There may be workarounds with =sumproduct() or =index(), but you'd have to share
the offending formula to get any sort of guess.

And I don't have a guess why the autofilter causes a problem.

may25 wrote:

In a table, i have one column (let say Column A) where the formula is
referred to another workbook. The formula works well, until i do an
auto-filtered on another column (Column B). The formula in Column A would
then displayed "#Value!". The formula would works well only if i have the
other workbook opened.

How do i do an auto-filter without getting the error on the formula?

Pls advice.

Thanks

--

Dave Peterson

Hi,

Thanks. I am using SumIf(). Will try to replace it with SumProduct().

Cheers!

"Dave Peterson" wrote:

I know that there are some worksheet functions that only work if the other
workbook is open. A few of them are =countif(), =sumif(), =indirect().

There may be workarounds with =sumproduct() or =index(), but you'd have to share
the offending formula to get any sort of guess.

And I don't have a guess why the autofilter causes a problem.

may25 wrote:

In a table, i have one column (let say Column A) where the formula is
referred to another workbook. The formula works well, until i do an
auto-filtered on another column (Column B). The formula in Column A would
then displayed "#Value!". The formula would works well only if i have the
other workbook opened.

How do i do an auto-filter without getting the error on the formula?

Pls advice.

Thanks

--

Dave Peterson
.