If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#REF error!
Excel 2007 SP2+
I'm getting a #REF error on this: =IF(D$1+1=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" & ROW())),"") I've tried several ideas... none of them work to resolve the #REF errors. SOME ideas I've 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 "IF(TRUE,SUM(INDIRECT("RawData!F2:RawData!F5")),") . Once THIS calculation is 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 ????. Thanks! ANY assistance would be appreciated. The format for the cells to store the SUM calculated values are numeric. I'm totally lost! ;-( |
#2
|
|||
|
|||
#REF error!
On Sat, 19 Sep 2009 09:38:01 -0700, Tom
wrote: =IF(D$1+1=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" & ROW())),"") Try to remove the second "RawData!", like this: =IF(D$1+1=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":F" & ROW())),"") Hope this helps / Lars-Åke |
#3
|
|||
|
|||
#REF error!
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 http://people.stfx.ca/bliengme "Tom" wrote in message ... Excel 2007 SP2+ I'm getting a #REF error on this: =IF(D$1+1=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" & ROW())),"") I've tried several ideas... none of them work to resolve the #REF errors. SOME ideas I've 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 "IF(TRUE,SUM(INDIRECT("RawData!F2:RawData!F5")),") . Once THIS calculation is 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 ????. Thanks! ANY assistance would be appreciated. The format for the cells to store the SUM calculated values are numeric. I'm totally lost! ;-( |
#4
|
|||
|
|||
#REF error!
Thanks for pointing this out!
Much appreciation! "Bernard Liengme" wrote: 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 http://people.stfx.ca/bliengme "Tom" wrote in message ... Excel 2007 SP2+ I'm getting a #REF error on this: =IF(D$1+1=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" & ROW())),"") I've tried several ideas... none of them work to resolve the #REF errors. SOME ideas I've 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 "IF(TRUE,SUM(INDIRECT("RawData!F2:RawData!F5")),") . Once THIS calculation is 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 ????. Thanks! ANY assistance would be appreciated. The format for the cells to store the SUM calculated values are numeric. I'm totally lost! ;-( |
#5
|
|||
|
|||
#REF error!
THANKS, Lars, how did you know that?
How can I get up to your level of expertise? Tom "Lars-Ã…ke Aspelin" wrote: On Sat, 19 Sep 2009 09:38:01 -0700, Tom wrote: =IF(D$1+1=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" & ROW())),"") Try to remove the second "RawData!", like this: =IF(D$1+1=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":F" & ROW())),"") Hope this helps / Lars-Ã…ke |
Thread Tools | |
Display Modes | |
|
|