If Worksheet function

[Lins]

I am using the IF worksheet function to do a vlookup to obtain certai
values for an entered cell. I am limited to 7 by default. is ther
another may to obtain this data. I have 9 different input options t
obtain several output

 

[Ron Rosenfeld]

I am using the IF worksheet function to do a vlookup to obtain certain
values for an entered cell. I am limited to 7 by default. is there
another may to obtain this data. I have 9 different input options to
obtain several outputs

Hard to tell with so little information about your setup. But usually, VLOOKUP
is a better function to use for many instances.


--ron

 

[Martijn]

Lins,

Instead of using 7 if's behind each other you can make a function:

Below an example to calculate the number of weeks of a stock position
together with a forecast.
Put the formula in cel A1, put the stock in b2 and add some forecast in
cells c2 till o2 to see if you can do something with it.

good luck, let me know if it works for you,

grtx's Martijn

Option Explicit
Function WeeksOfStock(Stock, Wk1, Wk2, Wk3, Wk4, Wk5, Wk6, Wk7, Wk8, Wk9,
Wk10, Wk11, Wk12, Wk13 As Long)
Application.Volatile
' In the worksheet
=WeeksOfStock(B2;C2;D2;E2;F2;G2;H2;I2;J2;K2;L2;M2;N2;O2)
If Stock = 0 Then
If Stock - Wk1 <= 0 Then
WeeksOfStock = -1
End If
ElseIf Stock - Wk1 <= 0 Then
WeeksOfStock = 0
ElseIf Stock - Wk1 - Wk2 <= 0 Then
WeeksOfStock = 1
ElseIf Stock - Wk1 - Wk2 - Wk3 <= 0 Then
WeeksOfStock = 2
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 <= 0 Then
WeeksOfStock = 3
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 <= 0 Then
WeeksOfStock = 4
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 - Wk6 <= 0 Then
WeeksOfStock = 5
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 - Wk6 - Wk7 <= 0 Then
WeeksOfStock = 6
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 - Wk6 - Wk7 - Wk8 <= 0 Then
WeeksOfStock = 7
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 - Wk6 - Wk7 - Wk8 - Wk9 <= 0
Then
WeeksOfStock = 8
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 - Wk6 - Wk7 - Wk8 - Wk9 -
Wk10 <= 0 Then
WeeksOfStock = 9
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 - Wk6 - Wk7 - Wk8 - Wk9 -
Wk10 - Wk11 <= 0 Then
WeeksOfStock = 10
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 - Wk6 - Wk7 - Wk8 - Wk9 -
Wk10 - Wk11 - Wk12 <= 0 Then
WeeksOfStock = 11
ElseIf Stock - Wk1 - Wk2 - Wk3 - Wk4 - Wk5 - Wk6 - Wk7 - Wk8 - Wk9 -
Wk10 - Wk11 - Wk12 - Wk13 <= 0 Then
WeeksOfStock = 12
Else
WeeksOfStock = 13

End If
Exit Function

End Function