Hi all,

I have a weird problem here.

I have some formulas in a tab looking up data from an external workbook.

As usual, if the external workbook is open, there is no issue; but with the
source data workbook closed, I get the usual #N/A link

Nothing unusual so far

When I open up the workbook and say 'Dont update' to external links, as
expected, I see the last data that was in the spreadsheet when I saved/closed
it.

I can use the spreadsheet with no issues at all.

On the tab, there is a filter. I can change the filter, play around it with,
no issue

However, I have a custom view with filter settings saved in it.

When I select the custom view - BANG.
All the #N/As are back. For some reason, selecting the custom view seems to
be refreshing the data in my externally linked values

I cannot see the logic in this. Is it a bug?
Much more importantly, can anyone suggest a work-round?

Thanks

Nel post
*phil* ha scritto:

Hi all,

I have a weird problem here.

I have some formulas in a tab looking up data from an external
workbook.

As usual, if the external workbook is open, there is no issue; but
with the source data workbook closed, I get the usual #N/A link

Nothing unusual so far

When I open up the workbook and say 'Dont update' to external links,
as expected, I see the last data that was in the spreadsheet when I
saved/closed it.

I can use the spreadsheet with no issues at all.

On the tab, there is a filter. I can change the filter, play around
it with, no issue

However, I have a custom view with filter settings saved in it.

When I select the custom view - BANG.
All the #N/As are back. For some reason, selecting the custom view
seems to be refreshing the data in my externally linked values

I cannot see the logic in this. Is it a bug?
Much more importantly, can anyone suggest a work-round?

Thanks

When you select a custom view the worksheet is recalculated, so if oyur
source workbook is closed you get errors #N/A.
Maybe should be better to eliminate this errors.
Maybe you can replace some SUMIF or also COUNTIF with SUMPRODUCT or INDEX +
MATCH.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Franz
Thank-you for the reply

I still don't understand this though.
Doing a normal recalc does not try and update external links after you said
'don't update'

why does selecting a custom view do so?
how can i stop it?

But it doesn't make sense. Why would selecting a custom view force a
recalculate when i have the

"Franz Verga" wrote:

Nel post
*phil* ha scritto:

Hi all,

I have a weird problem here.

I have some formulas in a tab looking up data from an external
workbook.

As usual, if the external workbook is open, there is no issue; but
with the source data workbook closed, I get the usual #N/A link

Nothing unusual so far

When I open up the workbook and say 'Dont update' to external links,
as expected, I see the last data that was in the spreadsheet when I
saved/closed it.

I can use the spreadsheet with no issues at all.

On the tab, there is a filter. I can change the filter, play around
it with, no issue

However, I have a custom view with filter settings saved in it.

When I select the custom view - BANG.
All the #N/As are back. For some reason, selecting the custom view
seems to be refreshing the data in my externally linked values

I cannot see the logic in this. Is it a bug?
Much more importantly, can anyone suggest a work-round?

Thanks

When you select a custom view the worksheet is recalculated, so if oyur
source workbook is closed you get errors #N/A.
Maybe should be better to eliminate this errors.
Maybe you can replace some SUMIF or also COUNTIF with SUMPRODUCT or INDEX +
MATCH.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Nel post
*phil* ha scritto:

"Franz Verga" wrote:

[cut]

When you select a custom view the worksheet is recalculated, so if
oyur source workbook is closed you get errors #N/A.
Maybe should be better to eliminate this errors.
Maybe you can replace some SUMIF or also COUNTIF with SUMPRODUCT or
INDEX + MATCH.

Franz
Thank-you for the reply

I still don't understand this though.
Doing a normal recalc does not try and update external links after
you said 'don't update'

why does selecting a custom view do so?
how can i stop it?

But it doesn't make sense. Why would selecting a custom view force a
recalculate when i have the

I don't know why Excel works in this way, but I think you can't stop it.

It should be better to eliminate the #N/As, by replacing the functions that
give this kind of errors, as I wrote above.




--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy