Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoCalculate & AutoFill (2003)

    The attached workbook has three columns of data entry (B,C and D). I want column D autocalculate based on the intersecting values of the combination matrix (G6:J9). That is when a record for Atribute 1 is selected as A and the Attribute 2 value is C, then I want column D in that record to autocalculate with B based on what the combination matrix would dictate.

    I guess I would apply the same conditional formatting to column D that I did with columnns B and C.

    I imagine this could be done with either a formula array or VBA? Thoughts? Any and all help is always appreciated.

    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AutoCalculate & AutoFill (2003)

    You can enter this formula in D4:
    <code>
    =INDEX($H$6:$J$8,MATCH(B4,$G$6:$G$8,0),MATCH(C4,$H $9:$J$9,0))
    </code>
    and fill down. You can indeed apply the same conditional formatting to column D.

    If you want to suppress the #N/A error value if column B or C is blank, you can use ISERROR to detect it:
    <code>
    =IF(OR(ISERROR(MATCH(B4,$G$6:$G$8,0)), ISERROR(MATCH(C4,$H$9:$J$9,0))), "", INDEX($H$6:$J$8, MATCH(B4,$G$6:$G$8,0), MATCH(C4,$H$9:$J$9,0)))
    </code>
    (The longer formulas may be displayed on two lines if your browser window isn't wide enough to display them on one line, but each is a single formula.)
    or
    <code>
    =IF(ISERROR(INDEX($H$6:$J$8, MATCH(B4,$G$6:$G$8,0), MATCH(C4,$H$9:$J$9,0))), "", INDEX($H$6:$J$8, MATCH(B4,$G$6:$G$8,0), MATCH(C4,$H$9:$J$9,0)))
    </code>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoCalculate & AutoFill (2003)

    Hans,
    This worked perfectly. Thanks.

    Were I to change increase the number of attribute values from 3 to 5 and make a corresponding matrix change from 9 to 25, then the formula that you drafted would still work after making the corresponding changes to the cell references? Please advise.

    Amy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AutoCalculate & AutoFill (2003)

    Yes, as long as you modify the cell references consistently, it'll work with a table of any height and width.

Posting Permissions

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