#REF error
 

I am getting an error in a report I am creating, while using the IF
statement. My statement looks like this: =IF(QUERY!F47=""," ",QUERY!F47).
This report pulls data from a query sheet, and puts the data onto the main
sheet. The error that I am receiving looks like this: =IF(QUERY!#REF!="","
",QUERY!#REF!). I get this error for 5 rows on the report. For example, if
my query has 30 records shown on the Query sheet, then rows 1-25 on the main
sheet are displayed with correctdata, rows 26-30 shows the #REF error in the
cell, and then rows 31 shows the last record from the query correctly. Any
ideas on why this is happening? If i were to recopy the statement down the
column after the data is pulled in, the error gets corrected. If i go back
and change the criteria on the query and pull the data in again, the error
comes back. I'm at a loss... Please help!!

Hi
this happens if rows/columns are deleted (and this happens if you
update your query)

--
Regards
Frank Kabel
Frankfurt, Germany

"Christen" schrieb im Newsbeitrag
...
I am getting an error in a report I am creating, while using the IF
statement. My statement looks like this: =IF(QUERY!F47="","
",QUERY!F47).
This report pulls data from a query sheet, and puts the data onto the
main
sheet. The error that I am receiving looks like this:
=IF(QUERY!#REF!="","
",QUERY!#REF!). I get this error for 5 rows on the report. For
example, if
my query has 30 records shown on the Query sheet, then rows 1-25 on
the main
sheet are displayed with correctdata, rows 26-30 shows the #REF error
in the
cell, and then rows 31 shows the last record from the query
correctly. Any
ideas on why this is happening? If i were to recopy the statement
down the
column after the data is pulled in, the error gets corrected. If i
go back
and change the criteria on the query and pull the data in again, the
error
comes back. I'm at a loss... Please help!!

yea, is there anyway to stop this error?

"Frank Kabel" wrote:

Hi
this happens if rows/columns are deleted (and this happens if you
update your query)

--
Regards
Frank Kabel
Frankfurt, Germany

"Christen" schrieb im Newsbeitrag
...
I am getting an error in a report I am creating, while using the IF
statement. My statement looks like this: =IF(QUERY!F47="","
",QUERY!F47).
This report pulls data from a query sheet, and puts the data onto the
main
sheet. The error that I am receiving looks like this:
=IF(QUERY!#REF!="","
",QUERY!#REF!). I get this error for 5 rows on the report. For
example, if
my query has 30 records shown on the Query sheet, then rows 1-25 on
the main
sheet are displayed with correctdata, rows 26-30 shows the #REF error
in the
cell, and then rows 31 shows the last record from the query
correctly. Any
ideas on why this is happening? If i were to recopy the statement
down the
column after the data is pulled in, the error gets corrected. If i
go back
and change the criteria on the query and pull the data in again, the
error
comes back. I'm at a loss... Please help!!

Hi
if your first formula should refer tor row 47 of the other sheet try:
=IF(OFFSET(QUERY!$F$47,ROW(1:1)-1,0)="","
",OFFSET(QUERY!$F$47,ROW(1:1)-1,0))
and copy this formula down



--
Regards
Frank Kabel
Frankfurt, Germany


Christen wrote:
yea, is there anyway to stop this error?

"Frank Kabel" wrote:

Hi
this happens if rows/columns are deleted (and this happens if you
update your query)

--
Regards
Frank Kabel
Frankfurt, Germany

"Christen" schrieb im
Newsbeitrag
...
I am getting an error in a report I am creating, while using the IF
statement. My statement looks like this: =IF(QUERY!F47="","
",QUERY!F47). This report pulls data from a query sheet, and puts
the data onto the main sheet. The error that I am receiving looks
like this: =IF(QUERY!#REF!=""," ",QUERY!#REF!). I get this error
for 5 rows on the report. For example, if my query has 30 records
shown on the Query sheet, then rows 1-25 on the main sheet are
displayed with correctdata, rows 26-30 shows the #REF error in the
cell, and then rows 31 shows the last record from the query
correctly. Any ideas on why this is happening? If i were to
recopy the statement down the column after the data is pulled in,
the error gets corrected. If i go back and change the criteria on
the query and pull the data in again, the error comes back. I'm at
a loss... Please help!!

That formula totally works, but in the blank cells (the ones that have no
data to pull over) there are space boxes. Is there anywhere in the formula
that I can make it so that the blank cells stay blank?
Thanks for your help!!


"Frank Kabel" wrote:

Hi
if your first formula should refer tor row 47 of the other sheet try:
=IF(OFFSET(QUERY!$F$47,ROW(1:1)-1,0)="","
",OFFSET(QUERY!$F$47,ROW(1:1)-1,0))
and copy this formula down



--
Regards
Frank Kabel
Frankfurt, Germany


Christen wrote:
yea, is there anyway to stop this error?

"Frank Kabel" wrote:

Hi
this happens if rows/columns are deleted (and this happens if you
update your query)

--
Regards
Frank Kabel
Frankfurt, Germany

"Christen" schrieb im
Newsbeitrag
...
I am getting an error in a report I am creating, while using the IF
statement. My statement looks like this: =IF(QUERY!F47="","
",QUERY!F47). This report pulls data from a query sheet, and puts
the data onto the main sheet. The error that I am receiving looks
like this: =IF(QUERY!#REF!=""," ",QUERY!#REF!). I get this error
for 5 rows on the report. For example, if my query has 30 records
shown on the Query sheet, then rows 1-25 on the main sheet are
displayed with correctdata, rows 26-30 shows the #REF error in the
cell, and then rows 31 shows the last record from the query
correctly. Any ideas on why this is happening? If i were to
recopy the statement down the column after the data is pulled in,
the error gets corrected. If i go back and change the criteria on
the query and pull the data in again, the error comes back. I'm at
a loss... Please help!!

Hi
if you don't want to copy the formulas individually cell by cell you
have to live with "" as a result

--
Regards
Frank Kabel
Frankfurt, Germany

"Christen" schrieb im Newsbeitrag
...
That formula totally works, but in the blank cells (the ones that
have no
data to pull over) there are space boxes. Is there anywhere in the
formula
that I can make it so that the blank cells stay blank?
Thanks for your help!!


"Frank Kabel" wrote:

Hi
if your first formula should refer tor row 47 of the other sheet
try:
=IF(OFFSET(QUERY!$F$47,ROW(1:1)-1,0)="","
",OFFSET(QUERY!$F$47,ROW(1:1)-1,0))
and copy this formula down



--
Regards
Frank Kabel
Frankfurt, Germany


Christen wrote:
yea, is there anyway to stop this error?

"Frank Kabel" wrote:

Hi
this happens if rows/columns are deleted (and this happens if
you
update your query)

--
Regards
Frank Kabel
Frankfurt, Germany

"Christen" schrieb im
Newsbeitrag
...
I am getting an error in a report I am creating, while using
the IF
statement. My statement looks like this: =IF(QUERY!F47="","
",QUERY!F47). This report pulls data from a query sheet, and
puts
the data onto the main sheet. The error that I am receiving
looks
like this: =IF(QUERY!#REF!=""," ",QUERY!#REF!). I get this
error
for 5 rows on the report. For example, if my query has 30
records
shown on the Query sheet, then rows 1-25 on the main sheet are
displayed with correctdata, rows 26-30 shows the #REF error in
the
cell, and then rows 31 shows the last record from the query
correctly. Any ideas on why this is happening? If i were to
recopy the statement down the column after the data is pulled
in,
the error gets corrected. If i go back and change the criteria
on
the query and pull the data in again, the error comes back.
I'm at
a loss... Please help!!