"misstrious" wrote in message
ups.com...
In an ideal world I would do a 22 nested If statement to check but
unfortunately Excel 2000 will only allow 7.

What I have is a random subset of 5 values from 22 possibilities.

The "english" for the formula is like this:

If A2 = "Fri" then If B2 = Medic, a+b, if B2 = Home, b+c, so on and so
forth for 22 possible options.

I am unfamiliar with VBA but am sure this is not that difficult an
equation to programme.

VBA doesn't suffer such a nesting limitation.

If [DV22] = 1 Then
MsgBox [DV22]
ElseIf [V22] = 2 Then
MsgBox [V22]
ElseIf [D2] = 3 Then
MsgBox [D2]
ElseIf [F3] = 4 And [G3]=6 Then
MsgBox [G3]
ElseIf [DV22] = 5 And [A2]=3 Or [K12]="wss" Then
..........................
End If

Bruno

In an ideal world I would do a 22 nested If statement to check but
unfortunately Excel 2000 will only allow 7.

What I have is a random subset of 5 values from 22 possibilities.

The "english" for the formula is like this:

If A2 = "Fri" then If B2 = Medic, a+b, if B2 = Home, b+c, so on and so
forth for 22 possible options.

I am unfamiliar with VBA but am sure this is not that difficult an
equation to programme.

Any help would be appreciated.

Thank you

Hi

One way would be to set up a list of the different outcomes. Say on
sheet2, in cells A1:B22 you put a list with
Medic a+b
Home b+c etc.
Then on sheet1, you could use the formula
=IF(A2<"Fri","",VLOOKUP(B2,Sheet2!$A$1:$B$22,2,0) )
So if the day in A2 is not "Fri" then there is a null return, otherwise
it looks up the value of B2 in your table.

--
Regards

Roger Govier


"misstrious" wrote in message
ups.com...
In an ideal world I would do a 22 nested If statement to check but
unfortunately Excel 2000 will only allow 7.

What I have is a random subset of 5 values from 22 possibilities.

The "english" for the formula is like this:

If A2 = "Fri" then If B2 = Medic, a+b, if B2 = Home, b+c, so on and so
forth for 22 possible options.

I am unfamiliar with VBA but am sure this is not that difficult an
equation to programme.

Any help would be appreciated.

Thank you

And nor do Excel formulas when the right one is used, ie VLOOKUP :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Bruno Campanini" wrote in message
...
"misstrious" wrote in message
ups.com...
In an ideal world I would do a 22 nested If statement to check but
unfortunately Excel 2000 will only allow 7.

What I have is a random subset of 5 values from 22 possibilities.

The "english" for the formula is like this:

If A2 = "Fri" then If B2 = Medic, a+b, if B2 = Home, b+c, so on and so
forth for 22 possible options.

I am unfamiliar with VBA but am sure this is not that difficult an
equation to programme.

VBA doesn't suffer such a nesting limitation.

If [DV22] = 1 Then
MsgBox [DV22]
ElseIf [V22] = 2 Then
MsgBox [V22]
ElseIf [D2] = 3 Then
MsgBox [D2]
ElseIf [F3] = 4 And [G3]=6 Then
MsgBox [G3]
ElseIf [DV22] = 5 And [A2]=3 Or [K12]="wss" Then
.........................
End If

Bruno

Just for info - You only need to post in one newsgroup - they are all read
by most of the regulars, and posting to a number of them simply fragments
responses, especially when the question has already been answered in another
group.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"misstrious" wrote in message
ups.com...
In an ideal world I would do a 22 nested If statement to check but
unfortunately Excel 2000 will only allow 7.

What I have is a random subset of 5 values from 22 possibilities.

The "english" for the formula is like this:

If A2 = "Fri" then If B2 = Medic, a+b, if B2 = Home, b+c, so on and so
forth for 22 possible options.

I am unfamiliar with VBA but am sure this is not that difficult an
equation to programme.

Any help would be appreciated.

Thank you

Thank you very much for you help, this solution works well.

However, I have hit another snag

This calculation needs to be applied to a large table, the a+b formulas
don't change but the cells used in the formulas do increment.

So we have the following scenario:

Row 3 Check column A if this is a fri then check the contents of column
B, if it matches column heading on sheet 2 then perform calculation
using values contained in the column on sheet 2 row 3.

Row 4 Check column A if this is a fri then check the contents of column
B, if it matches column heading on sheet 2 then perform calculation
using values contained in the column on sheet 2 row 4.

And so on and so forth.

Forgive me if I am being stupid but I cannot see a way of using a
Lookup table to increment the cell references in the calculations, so
that they stay in step with the calculations

I hope this makes sense

Once again, thank you for your help

Theresa