+ Reply to Thread
Results 1 to 9 of 9

Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me pls?

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Cool Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me pls?

    Hi everybody... not very familiar with the syntax of formulas and arrays (the "ctrl+shift+enter" ones). Here's my problem.

    Got a formula =MATCH($W2&X$1,$T$2:$T$620&$F$2:$F$620,0)... basically it combines the values in column W and row X (hence the ampersand) making it a uniqueID and looks such ID inside the combined values from columns T and F (again, I'm using the ampersand to match the ID). The output of this formula is meant to be copied in a range as an array (the "ctrl+shift+enter" stuff)
    >> BTW, I found this in another post from "mmmarks"... kudos to him for the excellent idea!!

    Here's my problem... how can I make the formula as an array (you know, F2 then ctrl+shift+enter) and still make the cells within the formulas as variables and changing according with the cells??

    In other words, within the formula above the $w2&x$1 is meant to change from cell to cell once the array is complete however, everytime I copy the array formula it fixes the w and x to a single value throughout the whole array (and therefore, the formula gets funky)

    Can you give me some light here?
    thanks!!!

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me p

    you problem i think is using absolute references.
    note the dollar signs.
    $A1 -> the A will not change the 1 will change
    A$1 -> A will change 1 will not
    $A$1 both will not change.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    01-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Thumbs up Re: Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me p

    thanks Vlady... however, even if I don't use any "$" at all, after I shadows the array range, then do the "F2 and ctrl+shift+enter", the whole array gets filled with the same formula.

    Let me explain it differently... if the formula in the cell where I do the "F2 then Ctl+Shift+Entr" is MATCH($W2&X$1,$T$2:$T$620&$F$2:$F$620,0)
    ... I would expect the formula just under it to be MATCH($W3&X$1,$T$2:$T$620&$F$2:$F$620,0)
    ... and the formula to its right to be MATCH($W2&Y$1,$T$2:$T$620&$F$2:$F$620,0)

    Perhaps I'm not understanding how the arrays work... can you give some input??

    Thanks!
    Jose

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me p

    Sorry for the delay. I can't replicate your problem.
    When i do it in my unit the columns and row changes when if use the fill handle.
    Maybe you can post the problem file with the formula you can delete the other datas inside.

  5. #5
    Registered User
    Join Date
    01-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Cool Re: Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me p

    Please check the attached file. Although, I included some explanation notes... let me know if I omit anything.
    Thanks much in advance!!

    DemoBook2.xlsx

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me p

    h2 =IFERROR(INDEX($B$2:$B$37,MATCH(H$1&$G2,$A$2:$A$37&$C$2:$C$37,0)),"") array enter then
    fill across and down
    if you array enter the whole range at once it simply repeats the formula in all cells
    try on a spare sheet say in b1 put =a1
    then select b1:e5
    press f2 then ctrl+shift+enter
    b1:e5 will all show {=a1}
    Attached Files Attached Files
    Last edited by martindwilson; 02-10-2014 at 12:51 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Registered User
    Join Date
    01-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me p

    martindwilson THANKS !!!

    so, it was a matter of creating the array in the first cell and only then fill it across and down... it worked, thanks much!!!

    now I know better about arrays,

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me p

    there are array formulas likE TRANSPOSE need to be entered as a range
    if you put in a1 down to a4 1,2,3,4
    then in b1 put TRANSPOSE(A1:A4) OR TRANSPOSE($A$1:$A$4) ARRAY ENTER then fill across to D1
    you will get 1,0,1,0 in b1:d1 for the first and 1,1,1,1 for the second
    but for this formula to work properly
    you enter either TRANSPOSE(A1:A4) OR TRANSPOSE($A$1:$A$4) in B1
    hit f2 select across to D1 THEN array enter
    B1:D1 will show as 1,2,3,4

  9. #9
    Registered User
    Join Date
    01-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Formula with array (ctrl+shift+enter) BUT with variable ranges... can anyone help me p

    nice... thanks!!

+ 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] Multiple Array Formula & CTRL+SHIFT+ENTER
    By makingtrails in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-30-2014, 09:54 PM
  2. With VBA Enter Ctrl Shift Enter For Array Formula
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-17-2013, 09:02 AM
  3. Array Formula: Ctrl+Shift+Enter does not work
    By georgroth in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2013, 12:15 PM
  4. [SOLVED] CTRL+SHIFT+ENTER array formula not working
    By frusterated in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2012, 12:02 AM
  5. Array Formula: CTRL+SHIFT+ENTER?
    By WebKill in forum Excel General
    Replies: 2
    Last Post: 05-27-2011, 01:56 PM

Tags for this Thread

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