+ Reply to Thread
Results 1 to 9 of 9

Dynamic, Spilled, Named Ranges - how to override SPILL

  1. #1
    Registered User
    Join Date
    06-08-2008
    Location
    Boston Massachusetts
    MS-Off Ver
    Excel for Office 365
    Posts
    28

    Dynamic, Spilled, Named Ranges - how to override SPILL

    I have a spreadsheet I put together a couple years ago that used to work great, and now is screwed up by the new dynamic, spilled array .... stuff.

    The spreadsheet uses cell values in a named range, say column B, as input parameters in a variety of formulas located in cells of column C.

    In the template file the first character in the formula cells is an &, so that this column can be used to populate tables for different projects, then can be replaced with = to convert them to functioning formulas.

    I used this file for the first time in a while and now the use of the named range is, apparently, triggering this lovely new dynamic array SPILLED function thing.
    It wants to use the formula in cell C5 to spill down results using the values in cells B5 thru B55.

    None of the formulas contain dymanic spilled operator commands.

    Can we not use named ranges as regular old array functions anymore?

    How do I fix this?

    Updated, any formula I write in the tab with the name of the named range acts as a spilled formula. LOVE IT!
    Last edited by AliGW; 02-22-2020 at 05:52 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,961

    Re: Dynamic, Spilled, Named Ranges, loving it, NOT

    Don't know if this helps ...

    https://support.office.com/en-gb/art...rs=en-GB&ad=GB

    https://blog-insider.office.com/2019...ions-in-excel/

    Gut feeling is you might be stuffed ...
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-08-2008
    Location
    Boston Massachusetts
    MS-Off Ver
    Excel for Office 365
    Posts
    28

    Re: Dynamic, Spilled, Named Ranges, loving it, NOT

    Thanks Trevor, I saw those already. No help that I could make out.

    I added and attachment with the issue if interested.

    I would find it hard to believe that Microsoft would screw up a basic use of named ranges except that, they are Microsoft.
    After all, I can't type 2-4 anywhere in Excel without it insisting it is Feb. 4th.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,961

    Re: Dynamic, Spilled, Named Ranges, loving it, NOT

    Oh, I can believe it. I rarely start threads these days but I did recently. A client had been using a workbook I developed for seven years. They got some new laptops (and Excel) and one of the functions failed. I was able to find a workaround for the problem but I never got to the bottom of what had changed. So, yes, they (MS) can screw you, albeit with the best of intentions.

    I'll have a look when I get a chance (but not tonight) 😴

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,694

    Re: Dynamic, Spilled, Named Ranges, loving it, NOT

    The way to overcome the SPILL default is to first remove the SPILL area completely, then start again with your formula in the first cell - enter it in the old-fashioned array way (CTRL+SHIFT+ENTER), then drag copy down.

    I understand your frustration, but your thread title wasn't quite what we ant, so as a courtesy, I have updated it slightly for you.
    Last edited by AliGW; 02-22-2020 at 05:53 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    06-08-2008
    Location
    Boston Massachusetts
    MS-Off Ver
    Excel for Office 365
    Posts
    28

    Re: Dynamic, Spilled, Named Ranges, loving it, NOT

    Hi I came back to see if there were any suggestions, and I opened my file again this AM to respond to Ali.

    In the course of doing so, I find that my file (a copy on my hard drive I last saved in 2018) is magically acting as intended without defaulting to a spilled dynamic array.

    So Ali, I can't help but be a little snidely ……. Excel, LOVE IT, it's magic! (I wonder what it is going to do on Monday when I try to use it in the project tables.)

    Ali, I appreciate your reply, but I am not using an array formula. I have different formulas going down column E and I am using the named range function so the formulas know to look in the same row of column C for the input parameter.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,694

    Re: Dynamic, Spilled, Named Ranges - how to override SPILL

    I know that you are not using an array formula - however, what I have suggested is the workaround. I am telling you this because I have discovered this issue myself and that IS what solves it. You can take it or leave it, and be just as snide as you like.

  8. #8
    Registered User
    Join Date
    06-08-2008
    Location
    Boston Massachusetts
    MS-Off Ver
    Excel for Office 365
    Posts
    28

    Re: Dynamic, Spilled, Named Ranges - how to override SPILL

    My file is back to defaulting to spill mode, triggered, I presume, by the named range. Perhaps spill mode took the weekend off.

    Ali, I tried your suggestion. I am a novice at array formulas, but as far as I can tell, if I convert the top formula to a "legacy" array formulae then copy down, I just get the same array formula in all the cells.

    I can type shift+control+return, manually for each cell and that un-spills the output, but that defeats the purpose of naming the input range.

    My awkward "fix" is to delete the range name. add a temporary column populated with the cell numbers that have the input data values, and use a substitute function to put the cell numbers in formulas, copy values, replace & with =

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Dynamic, Spilled, Named Ranges - how to override SPILL

    You could use...
    =INDEX(FIND(_Sed01,E3),ROWS($A$1:A1))

    Copy down.

    Or enter first cell (G3) with CTRL + SHIFT + ENTER. Then drag down that cell.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] YTD with dynamic spilled array
    By MatthewHart74 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-14-2019, 07:49 AM
  2. YTD with spilled dynamic array
    By MatthewHart74 in forum Office 365
    Replies: 0
    Last Post: 12-12-2019, 12:05 PM
  3. [SOLVED] Dynamic Named Ranges
    By sandy1977 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-07-2018, 11:13 AM
  4. Dynamic Named Ranges?
    By Dinosaurmapman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-12-2015, 01:10 AM
  5. Dynamic named ranges
    By Hilton1982 in forum Excel General
    Replies: 1
    Last Post: 01-08-2013, 08:37 AM
  6. Named ranges-Should I use a dynamic named range
    By foseco in forum Excel General
    Replies: 4
    Last Post: 06-11-2009, 03:56 PM
  7. Dynamic named ranges
    By SFoley in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-10-2006, 11:30 PM

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