Prev: Pivot Table Default Setting
Next: How to find errors in documents
From: may25 on 24 Mar 2010 23:36
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
From: Dave Peterson on 25 Mar 2010 08:15
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
From: may25 on 25 Mar 2010 21:39
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
> .
>
 | 
Pages: 1
Prev: Pivot Table Default Setting
Next: How to find errors in documents