Hi,

I am a little confused over the relationship between to 2 tables. How
does a challenge code of A relate to an Experience award?

Anyway, I hope the following provides a clue as to an answer to the
question.

Set up a sheet to allow entry of the group's level, say in cell E1 and
set the value to 1.

Set-up the Challenge level in cell E2 and set the value to "Easy".

Use DataValidation to ensure that values are between 1 and 20 for
group or are only "easy", "Challenging" or "Extreme" for challenge
level.

Press ALT+11 and insert a new module.

In the new module copy in the following code...

Code:
--------------------

Function AwardXP(pintLevel As Integer, _
pstrChallenge As String)

' Custom Function to return Experience for an encounter
'
' Inputs
' pintLevel Integer Level of group
' pstrChallenge String Level of encounter (Easy, Challenging, or Extreme)
'
' Output
' AwardXP Integer Experience for encounter

'Declarations
Dim intChallenge

'Convert Challenge string in to a number
Select Case pstrChallenge
Case "Easy"
intChallenge = 1
Case "Challenging"
intChallenge = 3
Case "Extreme"
intChallenge = 4
End Select

'Calculate experience
AwardXP = (pintLevel * 100) * intChallenge

End Function

--------------------


Press Alt+F11 again.

In cell E3 enter the following formula

Code:
--------------------

=AwardXP(E1, E2)

--------------------


Cell E3 should now show 100.

How this works...

Each challenge entry for cell E2 is converted into a number under
"Convert Challenge string in to a number". Data validation ensures only
3 challenge types are possible. Hence "Easy" = 1, "Challenging" = 3 and
"Extreme" = 4. You can easily amend these values or add different
challenge codes.

I have set the formula in the code under "Calculate experience" as:
Group's level * 100 * Challenge Code.

Easy (cell E2) has a challenge code of 1, and the Group level (cell E1)
= 1, thus:
Group's level * 100 * Challenge Code becomes
1 * 100 * 1 = 100

If you amend cell E1 to 9 and cell E2 to "Extreme" you get:
9 * 100 * 4 = 3600

Hopefully using the above you can amend the forumla to suit yourself.

One tip.

If you set up a column containing 1 through 20 in different cells, and
another column containing Easy, Challenging and Extreme, you can use
DataValidation, select list and ensure in cell dropdown is checked. In
cell E1 do this using the column of numbers as the source and for cell
E2 the column of Easy, etc as the source. You'll then get dropdown
boxes in the cells that a) save typing and b) ensure that only valid
entries can be set.

HTH

Art


--
HiArt
------------------------------------------------------------------------
HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953
View this thread: http://www.excelforum.com/showthread...hreadid=397117