Prev: How to browse folder and open xls file automatically in vba?
Next: Getting Data from a 2nd sheet to show in a dropdown box on sheet 1
From: Bodo on 21 Dec 2009 07:11
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
From: Patrick Molloy on 21 Dec 2009 07:24
I don't know what you mean with #1.

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


"Bodo" <Bodo(a)discussions.microsoft.com> wrote in message
news:FC432ECD-1E96-422D-85D1-C218E3DCD1F7(a)microsoft.com...
> 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

From: Bodo on 21 Dec 2009 08:47
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" <Bodo(a)discussions.microsoft.com> wrote in message
> news:FC432ECD-1E96-422D-85D1-C218E3DCD1F7(a)microsoft.com...
> > 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
>
 | 
Pages: 1
Prev: How to browse folder and open xls file automatically in vba?
Next: Getting Data from a 2nd sheet to show in a dropdown box on sheet 1