Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Eqs w INDEX&Match that return a vector

    HOLD EVERYTHING. PLEASE IGNORE THE "NEVER MIND" COMMENT. I HAVE BEEN UNABLE TO REPEAT THIS CLEAR OR DELETION. IT MUST HAVE BEEN A FLUKE. I DO NEED HELP ON THIS.
    THANKS
    NEVER MIND. I MANAGED TO DELETE THE CELLS BY INCLUDING OTHER CELLS IN THE SAME ROW IN THE DELETE.
    THANKS
    I enter the following formula into sheet1!E253 to retrieve three fields from sheet2, columns K thru M, for a user in sheet1 (cell D253) who has showed up in sheet2!BK5:BK11:

    =INDEX(sheet2!BK5:BM11,MATCH(sheet1!D253,sheet2!BK 5:BK11,0),0)

    I enter the formula as an array and then, if I get a name back in cell sheet1!E253, I hold the shift key and block cells E253:G253. Then I click in the formula bar and again do CNTRL, SHIFT, ENTER and I get the 3 fields I need for that name (the name + 2 other fields). Everything is fine up to this point.
    My ? is how can I delete or clear cells E253:G253 or even F253:G253 if I want to change the formula. I keep getting the message "cn not change an array" or something like that. the only thing I have been able to do is to go to EDIT and keep undoing until I get back to that point.
    Anyone?
    Thanks
    Stephen
    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Eqs w INDEX&Match that return a vector

    When you create an array formula, you are applying the formula to several cells, not just one. In your case, the array formula covers three cells.

    Whenever you need to change the formula (or delete it), you have to change all three cells at once. In your case, you would select E253:G253, make the changes in the formula bar (at which time, the {} should disappear), then type Ctrl-Shift-Enter to apply the changes as an array formula again. To delete it, you would select E253:G253 and press Delete.

    You can't delete or change just one (or two) because Excel wouldn't know what to do with the other cells you didn't delete or change. Think of it like a Rubik cube; you can't scramble just one side.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Eqs w INDEX&Match that return a vector

    John:
    I thought that I had tried to do it this way. I will try again at work tomorrow. Meanwhile, I have not forgotten that you wanted to see that file. I did bring it home and I will review it to see what I can send you.
    Thanks

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Eqs w INDEX&Match that return a vector

    It *is* possible to delete just one cell/part of an array, this is the way to do it:

    - select all cells of the array
    - press F2
    - press control-enter
    - delete/clear the ones you don't want
    - select the ones you do want
    - F2
    - control-shift-enter
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Eqs w INDEX&Match that return a vector

    Thanks, Jan. I will try this when I get to work.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Eqs w INDEX&Match that return a vector

    Technically, no, because the Ctrl-Enter step re-enters the formulas as non-array formulas. But this is a handy tip I did not know about. Thanks!

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Eqs w INDEX&Match that return a vector

    It worked.
    Thanks

Posting Permissions

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