PC Review


Reply
 
 
Tom
Guest
Posts: n/a
 
      19th Sep 2009
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! ;-(

 
Reply With Quote
 
 
 
 
Lars-ke Aspelin
Guest
Posts: n/a
 
      19th Sep 2009
On Sat, 19 Sep 2009 09:38:01 -0700, Tom
<(E-Mail Removed)> 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


 
Reply With Quote
 
 
 
 
Bernard Liengme
Guest
Posts: n/a
 
      19th Sep 2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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! ;-(
>

 
Reply With Quote
 
Tom
Guest
Posts: n/a
 
      19th Sep 2009
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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! ;-(
> >

>

 
Reply With Quote
 
Tom
Guest
Posts: n/a
 
      19th Sep 2009
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
> <(E-Mail Removed)> 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
>
>
>

 
Reply With Quote
 
Tom
Guest
Posts: n/a
 
      19th Sep 2009
Barry, thanks...
Now when do I use INDIRECT vs OFFSET vs INDEX then?
How'd you learn these?

"barry houdini" wrote:

>
> and unless you have a good reason to use INDIRECT then it might be
> better to use OFFSET or INDEX, i.e.
>
> =IF(D$1<ROW(),SUM(OFFSET(rawdata!F$1,ROW()-D$1,,D$1)),"")
>
>
> --
> barry houdini
> ------------------------------------------------------------------------
> barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=136395
>
>

 
Reply With Quote
 
Bernard Liengme
Guest
Posts: n/a
 
      19th Sep 2009
Tom,
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)
=INDIRECT("'"&A1&"'!D10")
Either formula could eb copried down the column
best wishes

"Tom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Barry, thanks...
> Now when do I use INDIRECT vs OFFSET vs INDEX then?
> How'd you learn these?
>
> "barry houdini" wrote:
>
>>
>> and unless you have a good reason to use INDIRECT then it might be
>> better to use OFFSET or INDEX, i.e.
>>
>> =IF(D$1<ROW(),SUM(OFFSET(rawdata!F$1,ROW()-D$1,,D$1)),"")
>>
>>
>> --
>> barry houdini
>> ------------------------------------------------------------------------
>> barry houdini's Profile:
>> http://www.thecodecage.com/forumz/member.php?userid=72
>> View this thread:
>> http://www.thecodecage.com/forumz/sh...d.php?t=136395
>>
>>

 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      19th Sep 2009
Just to add my 2 cents....

>=IF(D$1<ROW(),SUM(OFFSET(rawdata!F$1,ROW()-D$1,,D$1)),"")


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

In general I don't like using ROW() or COLUMN() with no argument. It's safer
to use ROWS(...) and COLUMNS(...).

--
Biff
Microsoft Excel MVP


"Bernard Liengme" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Tom,
> 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)
> =INDIRECT("'"&A1&"'!D10")
> Either formula could eb copried down the column
> best wishes
>
> "Tom" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Barry, thanks...
>> Now when do I use INDIRECT vs OFFSET vs INDEX then?
>> How'd you learn these?
>>
>> "barry houdini" wrote:
>>
>>>
>>> and unless you have a good reason to use INDIRECT then it might be
>>> better to use OFFSET or INDEX, i.e.
>>>
>>> =IF(D$1<ROW(),SUM(OFFSET(rawdata!F$1,ROW()-D$1,,D$1)),"")
>>>
>>>
>>> --
>>> barry houdini
>>> ------------------------------------------------------------------------
>>> barry houdini's Profile:
>>> http://www.thecodecage.com/forumz/member.php?userid=72
>>> View this thread:
>>> http://www.thecodecage.com/forumz/sh...d.php?t=136395
>>>
>>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:03 PM.