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
0
Utf
12/21/2009 12:11:03 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1089 Views

Similar Articles

[PageSpeed] 10

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

0
Patrick
12/21/2009 12:24:14 PM
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@discussions.microsoft.com> wrote in message 
> news:FC432ECD-1E96-422D-85D1-C218E3DCD1F7@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 
> 
0
Utf
12/21/2009 1:47:01 PM
Reply:

Similar Artilces:

Transferring over outlook data to new XP machine
How do I transfer over my old emails, address book to my new XP machine? I have looked over the internet and found nothing the tells me EXACTLY how to do this, any help would be greatly appreciated. senior_tech@yahoo.com If your using MS Outlook copy your .PST file across and import it into the new install. >If your using MS Outlook copy your .PST file across and import it into the new install. No, don't import it. Simply use "File">"Open" -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the nam...

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

multiple Domain name delivery problem
hi, I currently have Exchange Server 2003 Build 7638:2 SP2,. We have multiple domain names being delivered to the exchange store. I have nothad any problems, but i currently have one user that is not receiving emails with attachments from one certain "internet" sender to one of her email addresses, but the other address works fine & if they send emails without attachments, everything works fine from either address. I have had the user send them message with attachments to me & the user with the problem & i get the message, but not the other user! I even use message...

Exchange update problem
I have tried to upgrade exch2k3 sp1 to sp2, but the update fails with "the file pcproxy.dll is in use, and setup cannot identify the app or srvc. setup cannot continue" Any clues/ideas/suggestions? Please. -- ----------------------------------------------------------------------------------------------------------------------- This message has been checked for all known viruses. The information contained in this e-mail and any attachments is confidential and may be the subject of legal, professional or other privilege. It is intended for the named addressee only and may not ...

Error: Invalid byte was found at byte index 63.
Does anyone know what this means: "Invalid byte was found at byte index 63. " If yes, please help. Apogee Apogee wrote: > Does anyone know what this means: > > "Invalid byte was found at byte index 63. " That means exactly what it says: At index 63 XML parser found a byte, which is invalid either according document's encoding or is forbidden in XML documents at all (see list of allowed in XML characters at http://www.w3.org/TR/2000/REC-xml-20001006#charsets) -- Oleg Tkachenko http://www.tkachenko.com/blog Multiconn Technologies, Israel ...

docmd.transfertext problem
Hi, I am using access 97 and tried to import a csv file to the mdb table. I run a code as following: DoCmd.TransferText acImportDelim, "Specification4", "input", DEFAULT_PATH & "online.txt", 1 In online.txt, there is a field which is 10 digit number and I specified it as a double datatype in the specification4. After the import, I found out that the 10 digit number data in the field get empty in the destined table while other fields are all right. Therefore I import manually using specification4 instead of running code. This time the 1...

SQL deadlock problem
I am currently having a big issue with sql deadlocking on the PrincipalObjectAccess table. The last few months I have been working on a synchronization process using a Biztalk orchestration. The sync uses the crm webservices to create and update account and contact records in CRM. But now deployment to the production environment gives me some problems. It seems that when trying to update account records (which is one of the first actions in the sync process) the webservice gives me Generic SQL errors and SQL timeouts. After extensive profiling and tracing in SQL I found that there are...

Parsing data from one spreadsheet into another format
The data that we dump out of one machine comes in like below. %AT_1300 Bottoms|Conductivity| (Water Out) InputRange VDC1to5 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Low 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Hi 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MinScale 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MaxScale 20.0 %AT_1300 Bottoms|Conductivity| (Water Out) EngUnits mhos %AT_1300 Bottoms|Conductivity| (Water Out) StepResponseTime 1.0 %AT_1300 Bottoms|Conductivity| (Water Out) DigFiltTimeCnst 0.016 And I need to convert this data to this f...

Error Generating the Offlice Address Book
I have a mixed site with 3 5.5 server and 4 2003 servers. I installed 2003 SP1 a few weeks back and since then I'm having an issue generating my Offline Address Book. Here the event log messages I'm getting. Event ID 9331: OALGen encountered error 80040107 (internal ID 501023d) accessing the public folder store while generating the offline address list for address list '/'. - Default Offline Address List For more information, click http://www.microsoft.com/contentredirect.asp. Event ID 9335: OALGen encountered error 80040107 while cleaning the offline address list public ...

XCH Error 3092, OAB replication
I am getting error 3092 for OAB in Exchnage 2003 (migrated from 5.5) "Error 1129 occurred while processing a replication event. Folder: (3-8) NON_IPM_SUBTREE\OFFLINE ADDRESS BOOK\EX:/o" Tried to delete offilne addressbook and recreate but error has not stopped. Any help will be a great help on where to look to get rid of this issue. Thanks ...

Exchange 2003 new install can not receive external email.
I have just setup a new Windows Server 2003 standard edition with Exchange 2003 standard edition on it. I have been working for a while trying to get it to receive external email. I can send out and send/ receive internal messages, but when someone trys to send me a message from outside our network they get the following returned mail message This Message was undeliverable due to the following reason: Each of the following recipients was rejected by a remote mail server. The reasons given by the server are included to help you determine why each recipient was rejected. Recipient: <**...

Problems with Public Folders replication
Hi guys, Some problems here. We have decided to install Exchange server 2003 SP1 on a new hardware with W2k3 SP1 on a new DC (yes, I know it is a bad practice, but...) instead of old Exchange server 2003 SP1 on an old DC on an old hardware. New one (additional) DC was brought live (with some minor problems, DNS, time, as always) without FSMO delegation (for now). Replication of users' accounts was smooth. Logs clear. After Exchange 2003 SP1 installation (with some DNS manipulations) also all was Ok. Dcdiag Ok. In ESM all Exchange servers are visible (old and new). So I have started ...

MS Money 95 data files
I hope that some one can answer this for me. I have used MS Money 95 for years, and it works just fine for me on Windows XP, however, I now have to reformat my hard drive, and have discovered that I can nolonger find my original install disk. Will the latest versions of Money still read the MS Money 95 data files. All that I have ever used the program for is to track my investments, and am unlikely to do any different in the future. Thanks Stan B In microsoft.public.money, Stan Banner wrote: >I hope that some one can answer this for me. >I have used MS Money 95 for years, and...

HELP! Need to export hourly sales data on POS (NOT RMS)
How can I export hourly sales data across a date range? For instance, I want to show hourly sales for the month of October so I can graph it and post it in our break room. If I can't export hourly data, can I export daily sales? The built-in reports don't address this data format. This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C826DC.CBC512D0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to load...

Invalid XML error when I open customization setting
I have a problem when I try to open customization setting after I import an entity. The system errored "Invalid XML" "The XML passed to the platform is not well-formed XML". Please recommend how to resolve this problem. Thanks. ...

Strange Access Denied Problem with Windows 7
I got a new computer about six months ago that came with Windows Vista Home Premium 64bit. Before that I had done all of my .NET development either on an XP Pro VM or my former XP Pro computer at home. Shortly after getting my new computer at home, I also got a license for VMWare to be able to test my software on multiple platforms and configurations. I had wrote an application originally in VB.NET that was a simple backup utility. It supports mutiple backup configurations. Any given copnfiguration would define a backup which would be a list of files to backup, a list of folders to ...

Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...

Linker Error after upgrade from VC7.1
Hello all, After upgrading a VC7.1 project to visual studio 2005, it failed to build in the release configuration with the follwoing error : 1>nafxcw.lib(winocc.obj) : error LNK2005: "public: class CWnd * __thiscall CWnd::GetDlgItem(int)const " (?GetDlgItem@CWnd@@QBEPAV1@H@Z) already defined in InstallDlg.obj The debug build works fine. The project uses MFC in a static library. Well, after doing some research, it seems that this one is tied to the fact that in a release configuration, _AFX_ENABLE_INLINES is defined, so inline functions are embedded in the .obj file. Sure there...

Sorting Data #5
Is there formula or anyway to be able sort the below data into a format that I could create a pivot table on? I spend to many hours doing this every month. Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight: Account #: 1234 Description: Name Reference 1 Amount: $100.00 Account #: 4321 Description: Name Reference 2 Amount: $100.00 Account #: 9876 Description: Name Reference 3 Amount: $100.00 Any help would be much appreciated!! You need to show a Before and After version. You still might not get any help, but your ...

error on upgrade: ID 4386
Hello, I am upgrading from DPM 2010 RC to DPM 2010 RTM, 64 bit version, on Win 2008 R2 standard. The RC is working without any issues. The upgrade scenario is supported. At the very end of the process the upgrade fails giving me the following. _____ The SQL Server installation failed because a restart was pending on this computer. Restart the computer and then start DPM Setup again. ID: 4386. Details: Unknown error (0x84be0bc2) _____ Restarting the server does not correct the issue. On the next attempt I am getting the same error message. Where to look for the pending rest...

Border problems
Not sure why all of a sudden all my borders in my tables created with Publisher can only be white. No other color will show when selected. Opening a pub file done on another computer where the borders show color, shows white only. I have attempted to do a repair on publisher, which gave no help. Have attempted to uninstalled and reinstall Publisher without clearing the problem. Anyone have any ideas or suggestions? Look in the Accessibility Options in the control panel, display tab, disable "use high contrast." If that doesn't solve the issue, read the third FAQ here....

HELP! remote data not accessible msg
Hello, I have a use who is currently using a Bloomberg DDE add- in. Whenever he attempts to activate the add-in to retreive remote data, the system hangs. If I go to task manager, I then see a message stating "Remote data not accessible. To access this data Excel needs to open another program.... I have searched the knowledge base and didn't find much help. Does anyone have any ideas? I am desperate!!!! We are currently using Excel 2003 in XP Professional. TIA, Ramissah ...

error 0x800cc0f
i installed windows xp, and i set up all my email accounts. they are all working , except one: i receive 0x800cc0f message, which states that the service has been interrupted, contact your ISP...., but this is not the case, since my internet conneciton is working fine all the account settings are correct I am having the same problem. I have to close outlook and reopen to retrieve all of my messages. Have you found a resolution yet? "Kerstin" wrote: > i installed windows xp, and i set up all my email > accounts. they are all working , except one: i receive > 0x800c...

[b]Can I download Excel data to a MS Access database?[/b]
I've built an Excel 2002 form that I want our internal customers to access from our intranet, and use. Once completed, they will send it to us as an e-mail attachment. I'd like to be able to open it, and somehow download the data from the form into an MS Access 2002 database I've built (so that we don't have to rekey it into the database). Is this possible or even feasible? Any and all help is appreciated. Thanks. :D --------- Message sent via www.excelforums.com Hi in Access check 'File - Import External data' -- Regards Frank Kabel Frankfurt, Germany "...

External Images
I have come accross an issue with reporting services when using external images and accessing via report viewer .net components. Within the RDL I am using an expression to access a bmp via a URL. The report works fine in VS2005/2008 and when running the report through IE. However if the same report is run with the same criteria using an application with reporting services .net components the images are no longer shown, even though the rest of report is. I believe the unattended users/ passwords have been set within reporting services and IIS. The reports will be based on a...