# Thread: AutoCalculate & AutoFill (2003)

1. ## 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

2. ## 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. ## 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. ## 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
•