Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with external data and #reference error
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. -- Thanks in advance Bodo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with external data and #reference error
I don't know what you mean with #1.
the query is probably deleting "unused" cells - that would result in the #Ref error. "Bodo" wrote in message ... 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. -- Thanks in advance Bodo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with external data and #reference error
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. "Patrick Molloy" wrote: I don't know what you mean with #1. the query is probably deleting "unused" cells - that would result in the #Ref error. "Bodo" wrote in message ... 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. -- Thanks in advance Bodo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External xls reference error | Excel Worksheet Functions | |||
External Reference Data Updates. | Excel Programming | |||
pesky invalid external reference error: how to track it down? | Excel Programming | |||
external reference error | Excel Programming | |||
problem with: hidden (invalid) external reference | Excel Programming |