+ Reply to Thread
Results 1 to 10 of 10

Array Formula stopped working

  1. #1
    Registered User
    Join Date
    06-30-2010
    Location
    swindon, england
    MS-Off Ver
    Office 365
    Posts
    90

    Array Formula stopped working

    I've been using a spreadsheet for the past two years with several array formula's in it and it's been working perfectly fine.
    I made some changes to the layout, basically only changing the order items were listed in the column and the order items were listed in the range that the formula referenced and now I cannot get the formula to work.

    I have attached a simplified version of the spreadsheet and the formula is found in column D in the sheet titled XMN to UK Lowest Cost FCL.
    The ranges are in the sheet titled Pricing (FOB & Landed)

    Please can someone help, it's been driving me round the twist for quite some time.
    Attached Files Attached Files

  2. #2
    Forum Contributor corinereyes's Avatar
    Join Date
    12-02-2003
    Location
    Philippines
    MS-Off Ver
    MS Excel 2016
    Posts
    520

    Re: Array Formula stopped working

    Hi,

    Array formula should be enclosed with brackets as example:

    Please Login or Register  to view this content.
    Select the formula then press CTRL+SHIFT+ENTER
    Corine

  3. #3
    Registered User
    Join Date
    06-30-2010
    Location
    swindon, england
    MS-Off Ver
    Office 365
    Posts
    90

    Re: Array Formula stopped working

    Even when I add the brackets, nothing happens when I press CTRL+SHIFT+ENTER.

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Array Formula stopped working

    Don't manually add the braces
    Select the formula in the formula bar and confirm with Ctrl+Shift+Enter not just enter

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Array Formula stopped working

    Even when I add the brackets, nothing happens when I press CTRL+SHIFT+ENTER.
    You don't add brackets your self. See in my signature link how ARRAY formula work.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Registered User
    Join Date
    06-30-2010
    Location
    swindon, england
    MS-Off Ver
    Office 365
    Posts
    90

    Re: Array Formula stopped working

    Marcol / Fotis, thanks both for the response. If I leave the brackets off the formula, which is what I had been doing, and I press Ctrl+Shift+Enter, the formula does not work. It does not work for me even on the spreadsheet sent back by Fotis.

    Do you know what could cause the Ctrl+Shift+Enter function not to work?

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Array Formula stopped working

    As Marcol states:

    Select the formula in the formula bar and confirm with Ctrl+Shift+Enter not just enter
    I have done just that, and the attached WorkBook seems to be working fine now.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  8. #8
    Registered User
    Join Date
    06-30-2010
    Location
    swindon, england
    MS-Off Ver
    Office 365
    Posts
    90

    Re: Array Formula stopped working

    Thanks all. I must confess to being very stupid here and was not highlighting the formula in the formula bar. It had been some time since I'd used array formula's. Thanks all for the help.

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Array Formula stopped working

    ...................

  10. #10
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Array Formula stopped working

    You are most welcome, we all sometimes "hit the wall".

    Thank you for the "Rep".
    Last edited by Winon; 06-11-2013 at 09:32 AM. Reason: Spelling

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1