Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    array formula worked nad then failed (2003)

    Under what circumstances does an array formula return #Value!

    the following formula in an old version of the workbook worked successfully

    =SUM(('This period YTD Invoices'!Q$2:$Q$65000=B172)*('This period YTD Invoices'!$E$2:$E$65000='Fire Macros'!$H$11)*('This period YTD Invoices'!$I$2:$I$65000))

    However when I brought the formula forward to the new financial year and shortened the name on one lookup sheet only it returns #values on all lines where it is used

    =SUM(('YTD Invoices'!Q$2:$Q$65000=B3)*('YTD Invoices'!$E$2:$E$65000='Fire Macros'!$H$11)*('YTD Invoices'!$I$2:$I$65000))

    I have checked the source data sheets for invalid data and re-checked the names of the altered sheets. I have also de-constructed the formula into its component parts as far as possible, but am unable to trace the source of the error. Can somebody please advise what I should be looking for?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: array formula worked nad then failed (2003)

    Did you remember to use Ctrl+Shift+Enter to confirm the formula? Otherwise it won't be an array formula.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: array formula worked nad then failed (2003)

    Yes

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: array formula worked nad then failed (2003)

    I created a test workbook with the sheet names you mention, and both the old and the new formulas work correctly, so I don't know what goes wrong. Could you post a much reduced and stripped down copy of your database? Remove sensitive data.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: array formula worked nad then failed (2003)

    Hans

    So as to minimise the size of the workbook I purged a lot of the data and the formulas in question started to work again.

    Also when deleting some of the lines a few other anomolies arose such as the following message appeared "Cannot delete part of an array", when I was not trying to do so.

    I guess the workbook may require re-constructing?

    I still could not reduce the workbook below 300k though

    Alex

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: array formula worked nad then failed (2003)

    If it's not too much work, it might be a good idea to reconstruct the worksheet or workbook.

    (Did you try zipping the workbook in order to attach it?)

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: array formula worked nad then failed (2003)

    Problem solved!

    A pesky little line of eroneous data was imported and hidden inside the 5000 other rows of good data !!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: array formula worked nad then failed (2003)

    Phew! Glad you found it. Such problems can be hard to solve!

Posting Permissions

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