?
Solved

My formulas are deleted when my query returns no data

Posted on 2015-01-14
2
Medium Priority
?
113 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 34

Accepted Solution

by:
Rob Henson earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Six Sigma Control Plans

621 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