+ Reply to Thread
Results 1 to 3 of 3

Shortcut to replace the Range reference in an array formula with the actual Values

  1. #1
    Forum Contributor
    Join Date
    10-30-2014
    Location
    England
    MS-Off Ver
    2007 / 365
    Posts
    279

    Shortcut to replace the Range reference in an array formula with the actual Values

    This is driving me mad and I can't find the right words for Google to get what I need.

    If I have an array formula like {=A1:A9}, how do I replace the range in the formula with the actual values held in the array e.g. ={1,2,3,4,5,6,7,8,9}

    Possibly it wasn't a shortcut so much as a 'technique' to do this, but I've definitely done it before.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Shortcut to replace the Range reference in an array formula with the actual Values

    find and replace?

  3. #3
    Forum Contributor
    Join Date
    10-30-2014
    Location
    England
    MS-Off Ver
    2007 / 365
    Posts
    279

    Re: Shortcut to replace the Range reference in an array formula with the actual Values

    Finally found it, it's F9 when editing the formula.

    So if anyone's interested, example...

    In A1-A9 enter the values 1-9.

    In B1 enter the array formula {=A1:A9}

    Then click into the formula to edit (or press F2 for edit mode) then press F9.

    The formula should be replaced with the array values {=1;2;3;4;5;6;7;8;9}

+ 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. Replies: 11
    Last Post: 10-27-2016, 07:33 PM
  2. [SOLVED] VBA to replace cells containing specific text in a large range with an array formula
    By DaveBre in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-22-2016, 01:21 AM
  3. [SOLVED] Replace a cell reference in a formula with dynamic reference
    By jkj115 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2016, 11:04 AM
  4. Replies: 8
    Last Post: 06-29-2012, 10:20 PM
  5. Replies: 3
    Last Post: 11-24-2011, 06:11 AM
  6. Replace hard coded array in formula with link to input array
    By David Brown in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2011, 07:45 AM
  7. Possible to reference column of named range in array formula?
    By Kel Good in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-15-2005, 02:50 AM

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