Microsoft Excel

Discussion in 'General Discussion' started by Delkancott, Oct 4, 2016.

  1. Delkancott

    Delkancott Member

    Messages:
    2,522
    Likes Received:
    4,206
    Location:
    SEMA
    Is anyone familiar with Array Formulas in Excel?

    I am trying to use an array to assign values to text for some equations, but when I paste the array formula in additional cells, excel keeps updating the array selection range. While I know I can turn off autoformatting, some portions of the formula need the autoformatting in the event that rows are added.

    On row 2, the formula looks like this: =VLOOKUP(J2,'Membership Values'!$A2:$D11,3,FALSE)*K2

    On row 10, it looks like this:=VLOOKUP(J10,'Membership Values'!$A10:$D19,3,FALSE)*K10

    I want it to look like this:=VLOOKUP(J10,'Membership Values'!$A12:$D11,3,FALSE)*K10
     
  2. Delkancott

    Delkancott Member

    Messages:
    2,522
    Likes Received:
    4,206
    Location:
    SEMA
    Nevermind, I think I figured it out. Absolute reference.
     
    Jerry likes this.
  3. Jerry

    Jerry Member

    Messages:
    9
    Likes Received:
    21
    Gender:
    Male
    Location:
    Indiana
    Yup. F4 is your friend.
     
  4. AddictedToSteel

    AddictedToSteel Member

    Messages:
    894
    Likes Received:
    840
    Location:
    Michigan
    Yep, absolute references. The $D11 means that the column, D, is absolute, but the row, 11, is relative. Absolute and relative are pretty much only for copying and otherwise serve little to no purpose, as far as I know.
     

Share This Page