Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: CSE(array copying problem)

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi All --
    Goofy question. I have an array formula that when I drag and drop copy to more than one cell, when I save Excel crashes. I can do it one cell at a time. Here are the 3 instances:
    =SUM(IF($C$60:$C$80=1,IF($F$60:$F$80<=H$56,$E$60:$E$80,0),0))
    =SUM(IF($C$60:$C$80=2,IF($F$60:$F$80<=H$56,$E$60:$E$80,0),0))
    =SUM(IF($C$60:$C$80=3,IF($F$60:$F$80<=H$56,$E$60:$E$80,0),0))

    where h56 is a date. I want to drag the column to copy but crashes when I save. I am using Excel97. (I know I know, dont have a choice though.)

    Thanks!!

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    Can you try a similar formula using sumproduct? Something like:

    =Sumproduct(($C$60:$C$80=1)+0,($F$60:$F$80<=H$56)+0,$E$60:$E$80)

    Worth a shot

    You should be able to run array formulas w/o crashing though. Does this only happen in that one file? What happens if you toss some test data into a blank xls file and try the same array formulas? At minimum, I guess you could try deleting *.tmp files, running scandisk, and maybe a defrag when you have time.

    Hope that helps somewhat
    Adam


  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    A Mile High!!
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Adam. I have this problem no matter the file. I can copy one Row at a time but not a column. Your formula works great though. Thanks!!

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ed,

    also, the only incremental there is the 1,2,3 in your arrayed-IF statement

    I have no clue to the internal mechanisms of a computer, but maybe this is what's causing the problem...... there's something adrift in your computer's ability to auto-increment by 1 each time...

    try substituting the 1,2,3,4 etc with "row(A1)"

    this will ask excel to give you the row number of row1, row2, row3 etc etc ad finitum which may be a different non-crashable function or DLL or something


    :: Pharma Z - Family drugstore ::

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,426
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Should H$56 be $H$56? Do you want all date <=H56?

    The following both give the same result; Array enter.

    =SUM(IF(($C$60:$C$80=1)*($F$60:$F$80<=$H$56),$E$60:$E$80),0)

    =SUM(($C$60:$C$80=1)*($F$60:$F$80<=H56)*($E$60:$E$80))


    [ This Message was edited by: Dave Patton on 2002-04-10 16:25 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •