Query refresh

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having a problem refreshing a query I set up to another Excel worksheet.
My query is set up to extract any records in a named range with the value of
0 in a certain column, which changes every month. The refresh works fine for
records that were already in the range when I set up the query, but if I add
more records, the query will not pull them in. I set the size of my range
much larger than the actual information to allow space for new records. Is
there some way to set up the query so that it will recognize new records in
the range it pulls from? Thanks for any help you can give me.
 
Hi Dave,
If I understand your problem correctly, it's the same one I had awhile ago
while importing a data query with more rows of data than the prior month. If
so, probably what you need is a variable OFFSET range. This will be a named
range like the one you already have, but it will automatically change
depending on the number of rows in the query. When you name your range, add
the formula below in the "Refers to" box.

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,">=0")
Sheet1! – Refers to the sheet the data is on.

$A$1 - Refers to the first cell in the range. If you have headings in Row
1, you must refer to Row 1 ($A$1). If you use $A$1, the header row will be
counted, but the last row of your range will not be included. To avoid that
problem, key the OFFSET range as:
=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,">=0")+1)

0,0 – Refers to the number of rows and columns to offset.

COUNTIF – Refers to the function that will count the rows according to
specific data.

Sheet1!$A:$A, - Refers to the column to count on. The count will continue
until it finds the first cell in Column A in which the criteria is False.

">0" – This is the criteria you specify. The count will continue in Col. A
until the first cell whose contents are not greater than 0.

Good luck, hope this helps.
 
Dave, I forgot to have you add the number of columns you need in your range.
=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,">=0"),10) the ,10 makes the
range 10 columns wide.
 
Thanks a lot for your help. I am finally past that problem, but now I have
another. Is there any way to save the query within the file so that other
people can run it from their own computers? I tried just sending the file
and the query will not run. Both of the query worksheets are in the same
file. Thanks again.
 
Did you ever get a solution to being able to send an Excel file with a query
so others can open it and use it. I have the same problem. Some of my files
with queries work when I send them and some do not. I can't figure out why?
 
Back
Top