Problem with external data and #reference error

[Bodo]

Hi,
my Excel workbook has 2 sheets:

1. Source: with an external data reference to a MS Access database
Query property "If number of rows changes after refresh" is set to
option #1:
"Insert cells for new data, delete unused cells"
User chooses external query selection criteria and therefore can
have different result sets.

2. Target:
Formular B4: =Source!P5
Formular B5: =Source!P6
Formular B6: =Source!P7

Problem:
When Source has data in rows 5, 6 and 7 formular gets calculated correctly.
If Source row #5 has data and #6 and #7 is empty then I get a #refe error in
Target cells B5 and B6.
Error details: Invalid cell reference.

I would have expected that in this case the cell shows no data because the
source cell is empty.

Appreciate any thought on how to troubleshoot.

 

[Patrick Molloy]

I don't know what you mean with #1.

the query is probably deleting "unused" cells - that would result in the
#Ref error.

 

[Bodo]

Thanks Patrick for the quick respond.

With option #1 I refer to a query Datarange property that you can set in
excel by clicking on the property icon on the external data toolbar .

This dialog gives you several options one of them is:
If number of rows changes after refresh/update
- Insert cells for new data, delete unused cells
- ...

I tried the other options on that dialog to no avail.

I don't know what you mean with #1.

the query is probably deleting "unused" cells - that would result in the
#Ref error.