Solved

My formulas are deleted when my query returns no data

Posted on 2015-01-14
2
103 Views
Last Modified: 2015-01-14
I have an ODBC query pulling data from my SQL database into Excel 2003. I have about 10 columns where data is queried. I have a parameter setup where I can enter the value I am looking for and the query will return the data I am looking for. I have also inserted a few columns within the query that have vlookups and varies formulas. My issue is when I put in my parameter and the query returns no data, my formulas are all deleted. How do I setup my query so these formulas aren't deleted. I also want to make sure whether my returns 1 or 1,000 rows of data, my formulas fill next to the data. How do I fix the issue of my formulas being deleted when my query returns no data? Thanks!!
0
Comment
Question by:brasiman
2 Comments
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40549212
I would suggest putting the formulas in columns alongside the Query Range result rather than columns within the query result.

I am no SQL / Query expert but I believe that when the query is refreshed it will remove rows from the query range; ie if the query is columns A1:N1500 when run on one occasion but only 1300 on the next occasion, the query will remove cells A1301:N1500. If you have inserted columns within the query, extending the range out to column Z then cells A1301:Z1500 will be removed; removing your formulas as well.

Thanks
Rob H
0
 

Author Closing Comment

by:brasiman
ID: 40549345
Thanks Rob. That worked to an extent. When I added the formula outside the query, that fixed half the issue. When I refreshed the query, the formula was still deleted if the query returned no data. But, I went to the Data Range Properties and changed the option to "Insert cells for new data, delete unused cells". I then checked the "Fill down formulas in columns adjacent to data" box. With this combination of options checked and the formula outside the query, it works. Thanks for your help.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question