Solved

My formulas are deleted when my query returns no data

Posted on 2015-01-14
2
108 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 33

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
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…

630 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