Excel - #REF error!

Asked By Tom on 19-Sep-09 12:38 PM
Excel 2007 SP2+

I am getting a #REF error on this:
=IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" &

I have tried several ideas... none of them work to resolve the #REF errors.
SOME ideas I have tried: Format to be General, Format to be Number (0 decimal
places), Format to be Text for the D$1 cell.  The format on the RawData!
cells are numeric.
In stepping through the calculations, everything appears to work fine except
for the final calculation on
performed I receive the #REF error message.  I receive
IF(TRUE,(SUM(#REF!),"").  So, the issue is evidently relating to the RawData
cells and/or the format of the cell types or ????.

ANY assistance would be appreciated.  The format for the cells to store the
SUM calculated values are numeric.
I am totally lost! ;-(

Lars-Åke Aspelin replied on 19-Sep-09 12:57 PM
Try to remove the second "RawData!", like this:

=IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":F" &

Hope this helps / Lars-?ke
Bernard Liengme replied on 19-Sep-09 01:03 PM
1) now need for sheet refernce twice:
=SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":F" &ROW()))
2) if D1 has value 4,  this formula will give REF error in any row less than
4 since the expression Row()-4+1 will evaluate to a negative number in rows
prior to 4
best wishes
Bernard Liengme
Microsoft Excel MVP
barry houdini replied on 19-Sep-09 01:05 PM
and unless you have a good reason to use INDIRECT then it might be
better to use OFFSET or INDEX, i.e.


barry houdini
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=136395
Tom replied on 19-Sep-09 02:09 PM
Thanks for pointing this out!
Much appreciation!
Tom replied on 19-Sep-09 02:09 PM
THANKS, Lars, how did you know that?
How can I get up to your level of expertise?
Tom replied on 19-Sep-09 02:10 PM
Barry, thanks...
Now when do I use INDIRECT vs OFFSET vs INDEX then?
How'd you learn these?
Bernard Liengme replied on 19-Sep-09 03:45 PM
Here is an example of where INDIRECT could not be replaced by OFFSET:
Let A1:A10 hold a list of sheet names, and we want to pick values from  D10
on each sheet
We could use =INDIRECT(A1&"!D10") or to be more careful (sheet names could
have spaces in them and need to be enclosed in single quotes)
Either formula could eb copried down the column
best wishes
T. Valko replied on 19-Sep-09 05:26 PM
Just to add my 2 cents....

The whole of the formula is based on what row the formula is entered on. If
the OP does not provide that info then it is pretty hard to pinpoint the
problem but a #REF! error would usually mean OFFSET is not using a valid

In general I do not like using ROW() or COLUMN() with no argument. it is safer
to use ROWS(...) and COLUMNS(...).

Microsoft Excel MVP