Solved

My formulas are deleted when my query returns no data

Posted on 2015-01-14
2
97 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 31

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

This is about my first experience with programming Arduino.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now