Solved

My formulas are deleted when my query returns no data

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to solve "recursive" database tables 2 59
Copy one row (from Word) into one cell 14 48
Permutacion of 2 numbers COUNT 8 21
Error 1004 Excel 2013 11 16
I have a large data set and a SSIS package. How can I load this file in multi threading?
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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