+ Reply to Thread
Results 1 to 3 of 3

troubleshooting SUMPRODUCT

  1. 11-02-2007, 11:08 AM #1
    rob_barbour
    rob_barbour is offline
    Registered User
    Join Date
    11-02-2007
    Location
    berkshire
    Posts
    2

    Exclamation troubleshooting SUMPRODUCT

    I have a model which uses a table of SUMPRODUCT forumlae to feed a series of graphs. i've discovered the nasty way that deleting the original data before inserting new data (rather than 'clearing contents' or over-writing) causes every SUMPRODUCT function to return an #N/A error (and hence clearing all graphs and tables feeding off this)

    It's too late to UNDO - how can I get the functions to look up on a new batch of data, or am I going to have to completely rebuild the model?

    =SUMPRODUCT((Data!$F$4:$F$5000=$R4)*(Data!$K$4:$K$5000=S$3)) is the format i'm using.
    Reply With QuoteRegister To Reply
  2. 11-02-2007, 12:03 PM #2
    Excelenator
    Excelenator is offline
    Valued Forum Contributor Excelenator's Avatar
    Join Date
    07-25-2006
    Location
    Wantagh, NY
    Posts
    333
    There's no previously saved version of the file to refer back to and possibly insert the new data into?

    Otherwise, I believe it is FUBAR.
    ---------------------------------------------------
    ONLY APPLIES TO VBA RESPONSES WHERE APPROPRIATE
    To insert code into the VBE (Visual Basic Editor)
    1. Copy the code.
    2. Open workbook to paste code into.
    3. Right click any worksheet tab, select View Code
    4. VBE (Visual Basic Editor) opens to that sheets object
    5. You may change to another sheets object or the This Workbook object by double clicking it in the Project window
    6. In the blank space below the word "General" paste the copied code.
    Reply With QuoteRegister To Reply
  3. 11-02-2007, 01:50 PM #3
    rob_barbour
    rob_barbour is offline
    Registered User
    Join Date
    11-02-2007
    Location
    berkshire
    Posts
    2
    Quote Originally Posted by Excelenator
    There's no previously saved version of the file to refer back to and possibly insert the new data into?

    Otherwise, I believe it is FUBAR.
    As i suspected. that's my Friday night taken care of then!

    ANOTHER QUERY THOUGH -> is there a way of getting the SUMPRODUCT function to recognise the values of a column rather than its formulae?
    Last edited by rob_barbour; 11-02-2007 at 01:58 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1