#REF error

[Guest]

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!!

 

[Frank Kabel]

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

 

[Guest]

yea, is there anyway to stop this error?

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

 

[Frank Kabel]

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

 

[Guest]

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]

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