software

I used the Lookup Wizard to create a formula which is
=INDEX('Penalty Table'!$A$7:$F$26, MATCH(1,'Penalty Table'!$A$7:$A$26,),
MATCH(2003,'Penalty Table'!$A$7:$F$7,)). This only works one time.

I am creating a form where the employee enters the contract year, the
practice code number and then hopefully the percentage from the penalty
table is
automatically filled in. There will be several lines where they will be
filling in this information for contract holders. How can I enter this
formula for multiple entries? I think it has something to do with using
Ctrl-Shift-Enter to commit the formula. I don't understand how to use this.

Susan

To make the formulas work for more cell, change the hardcoded lookup values
on the MATCH formulas (1 and 2003 in this case) to cell references, like $A2
or B$5. You may need to play with the $ sign to match you specific layout,
to fix either rows or columns as appropiate.

Hope this helps,
Miguel.

quot;Susanquot; wrote:

gt; I used the Lookup Wizard to create a formula which is
gt; =INDEX('Penalty Table'!$A$7:$F$26, MATCH(1,'Penalty Table'!$A$7:$A$26,),
gt; MATCH(2003,'Penalty Table'!$A$7:$F$7,)). This only works one time.
gt;
gt; I am creating a form where the employee enters the contract year, the
gt; practice code number and then hopefully the percentage from the penalty
gt; table is
gt; automatically filled in. There will be several lines where they will be
gt; filling in this information for contract holders. How can I enter this
gt; formula for multiple entries? I think it has something to do with using
gt; Ctrl-Shift-Enter to commit the formula. I don't understand how to use this.
gt;
gt; Susan

Miguel,

The values 1 and 2003 as shown in my formula are not constant values. They
are just one example of where the data is pulled from a chart on another
worksheet page made up of practice codes which are numbered in rows (1-18)
and the columns are years 2002-2005. Where the row and column converge is
the percentage, which is the value being transferred to the cell containing
the formula. Do you know how I can get this to work with any combination of
code and year that might get put into a form??

Susan

quot;Miguel Zapicoquot; wrote:

gt; To make the formulas work for more cell, change the hardcoded lookup values
gt; on the MATCH formulas (1 and 2003 in this case) to cell references, like $A2
gt; or B$5. You may need to play with the $ sign to match you specific layout,
gt; to fix either rows or columns as appropiate.
gt;
gt; Hope this helps,
gt; Miguel.
gt;
gt; quot;Susanquot; wrote:
gt;
gt; gt; I used the Lookup Wizard to create a formula which is
gt; gt; =INDEX('Penalty Table'!$A$7:$F$26, MATCH(1,'Penalty Table'!$A$7:$A$26,),
gt; gt; MATCH(2003,'Penalty Table'!$A$7:$F$7,)). This only works one time.
gt; gt;
gt; gt; I am creating a form where the employee enters the contract year, the
gt; gt; practice code number and then hopefully the percentage from the penalty
gt; gt; table is
gt; gt; automatically filled in. There will be several lines where they will be
gt; gt; filling in this information for contract holders. How can I enter this
gt; gt; formula for multiple entries? I think it has something to do with using
gt; gt; Ctrl-Shift-Enter to commit the formula. I don't understand how to use this.
gt; gt;
gt; gt; Susan

As Miguel said, use something like

=INDEX('Penalty Table'!$A$7:$F$26, MATCH($A2,'Penalty Table'!$A$7:$A$26,),
MATCH(B$1,'Penalty Table'!$A$7:$F$7,))

play with the cells to get the correct value.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Susanquot; gt; wrote in message
...
gt; Miguel,
gt;
gt; The values 1 and 2003 as shown in my formula are not constant values.
They
gt; are just one example of where the data is pulled from a chart on another
gt; worksheet page made up of practice codes which are numbered in rows (1-18)
gt; and the columns are years 2002-2005. Where the row and column converge is
gt; the percentage, which is the value being transferred to the cell
containing
gt; the formula. Do you know how I can get this to work with any combination
of
gt; code and year that might get put into a form??
gt;
gt; Susan
gt;
gt; quot;Miguel Zapicoquot; wrote:
gt;
gt; gt; To make the formulas work for more cell, change the hardcoded lookup
values
gt; gt; on the MATCH formulas (1 and 2003 in this case) to cell references, like
$A2
gt; gt; or B$5. You may need to play with the $ sign to match you specific
layout,
gt; gt; to fix either rows or columns as appropiate.
gt; gt;
gt; gt; Hope this helps,
gt; gt; Miguel.
gt; gt;
gt; gt; quot;Susanquot; wrote:
gt; gt;
gt; gt; gt; I used the Lookup Wizard to create a formula which is
gt; gt; gt; =INDEX('Penalty Table'!$A$7:$F$26, MATCH(1,'Penalty
Table'!$A$7:$A$26,),
gt; gt; gt; MATCH(2003,'Penalty Table'!$A$7:$F$7,)). This only works one time.
gt; gt; gt;
gt; gt; gt; I am creating a form where the employee enters the contract year, the
gt; gt; gt; practice code number and then hopefully the percentage from the
penalty
gt; gt; gt; table is
gt; gt; gt; automatically filled in. There will be several lines where they will
be
gt; gt; gt; filling in this information for contract holders. How can I enter
this
gt; gt; gt; formula for multiple entries? I think it has something to do with
using
gt; gt; gt; Ctrl-Shift-Enter to commit the formula. I don't understand how to use
this.
gt; gt; gt;
gt; gt; gt; Susan