use of a variable

[geraldjbartlett]

I need to enter codes into the two columns marked



In the first column “AL” is the job descriptor this narrows it down t
three feel levels £400, £500 & £600



Then there are three or (however many) variables in the next colum
they are 1D, 2D & 3D whichever one I enter creates a fee payable in th
results column.



So if I enter AL and 2D the sum = 500

If I enter VS and 2D the sum = 550 etc.

How do I do this

 

[Dave Hawley]

Not too sure I understand, but try named constants. Go to
Insert>Name>Define enter the name: AL give it a value (in the refers to
box) of $500

***** Posted via: http://www.ozgrid.com
Excel Templates, Training,Add-ins & Business Software.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Ragdyer]

If I understand what you're asking, you could make a data list in an
"out-of-the-way" portion of the sheet, and then simply access this list to
obtain your result.

For example, using columns W, X, Y, and Z,
in W2, W3, and W4 enter
AL - VS - JZ (whatever)
Then, in X1, Y1, and Z1 enter
1D - 2D - 3D.

Now, fill in the array with your values,
X2, Y2, and Z2 with your
£400 - £500 - £600
and then, all the rest.

Now, assume that the codes will be entered A1 and B1, and the result will
display in C1,
Enter this in C1:
..
=INDEX(W1:Z4,MATCH(A1,W1:W4,0),MATCH(B1,W1:Z1,0))

If this formulas is to be copied down the column, you must change the list
references to absolute:

=INDEX($W$1:$Z$4,MATCH(A1,$W$1:$W$4,0),MATCH(B1,$W$1:$Z$1,0))