#REF error

[Hoosier03]

I am using Excel 2007. I have the #REF error in 6 cells on my spreadsheet. I pasted what it looks like below. The formula works on all the cells in the column to the left and the 4 cells above and 9 cells below these 6. The data is excatly the same in the SAP query in the cells that do work. Is this a formating issue?

Thanks,

Rodney


<TABLE style="WIDTH: 167pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=221 border=0><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 18pt" height=24><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 55pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" width=73 height=24> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=74> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=74> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#REF!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18> - </TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> - </TD></TR></TBODY></TABLE>

 

[Hoosier03]

The green arrow in the upper right corner says "Moving or deleting caused an invalid cell reference or function is teturning reference error"

I have not moved or deleted any rows or columns.

 

[shg]

If you look in the formula in the formula bar, chances are you'll see #REF within it.

If it's supposed to be the same formula as those above, copy them down.

 

[Hoosier03]

It is not in there.

The formula above is:

=IF(ISNA(VLOOKUP($D379,'Prior Forecast Query'!$C$22:$DJ$453,BT$375,FALSE)),0,VLOOKUP($D379,'Prior Forecast Query'!$C$22:$DJ$453,BT$375,FALSE))


The formula on the first one that does not work is

=IF(ISNA(VLOOKUP($D380,'Prior Forecast Query'!$C$22:$DJ$453,BT$375,FALSE)),0,VLOOKUP($D380,'Prior Forecast Query'!$C$22:$DJ$453,BT$375,FALSE))



The only difference I see is $D379 changes to $D380 becuase it is one cell down.

 

[shg]

What happens when you watch the formula evaluate using the formula auditing toolbar?

 

[Hoosier03]

$D380 is underlined.

After hitting evaluate you get what D380 references in quotes "11009295101341600035"

If you hit evaluate again you get the formula changed to
=IF(ISNA(#REF!),0,VLOOKUP($D380,'Prior Forecast Query'!$C$22:$DJ$453,BT$375,FALSE))

Next you get
=IF(FALSE,0,VLOOKUP($D380,'Prior Forecast Query'!$C$22:$DJ$453,BT$375,FALSE))

Next you get
=IF(FALSE,#N/A,VLOOKUP("11009295101341600035",'Prior Forecast Query'!$C$22:$DJ$453,BT$375,FALSE))

Next you get
IF(FALSE,#N/A,#REF!)

Finally
=#REF



If D380 is the problem how does it work on the first 51 columns on the spreadsheet?

 

[shg]

I'm missing why that should generate a #REF! error.

What's the value in D379 that works?

Perpendicular to your question, if you're using Excel 2007+, you could use instead

=IFERROR(VLOOKUP($D380, 'Prior Forecast Query'!$C$22:$DJ$453, BT$375, FALSE), 0)

 

[Hoosier03]

This is the value in D379

<TABLE style="WIDTH: 80pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=106 border=0><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=106 height=17>11009295101341600185</TD></TR></TBODY></TABLE>


The ones that are coming up with #REF contain data in the other rows. The ones that are not have no data in any other row.

 

[shg]

Hold on -- what's in BT375?

 

[Hoosier03]

The number 113 which references the column number for the vlookup. 113 is the correct column number for week 52 in the data set.