need help in formula

[VBABEGINER]

Hi All,

In my below formula, I'm, checking few condition's.
If F cell value blank or with date of (1900,1,1) then it written blank else
if F cell value not blank or with any other date and now K cell value blank then as per the red color part I need answer else
if F cell value not blank or with any other date and now here K cell value is not blank then as per the blue color part I need answer..

Can anyone please help here..

=IF(OR(F75558="",F75558=DATE(1900,1,1)),"",
IF(K75558="",
IF(H75558<=5,"Week 1",
IF(H75558<=10,"Week 2",
IF(H75558<=20,"Week 3 & 4",
IF(H75558<=30,"Week 5 & 6",
IF(H75558<=40,"Week 7 & 8",
IF(H75558<=50,"Week 9 & 10",
IF(H75558<=60,"Week 11 & 12",
IF(H75558<=70,"Week 13 & 14",
IF(H75558<=80,"Week 15 & 16",
IF(H75558<=85,"Week 17",
IF(H75558<=100,"Week 18 & 20",
IF(H75558<=115,"Week 21 & 23",
IF(H75558<=130,"Week 24 & 26",
IF(H75558<=131,"Week 27 onwards",

IF(K75558<>"",
IF(H75558<=10,"Week 1 & 2",
IF(AND(H75558>10,H75558<=20),"Week 3 & 4",
IF(AND(H75558>20,H75558<=30),"Week 5 & 6",
IF(AND(H75558>30,H75558<=40),"Week 7 & 8",
IF(AND(H75558>=40,H75558<=60),"Week 9 & 12",
IF(AND(H75558>60,H75558<=75),"Week 13 & 15",
IF(AND(H75558>75,H75558<=90),"Week 16 & 18",
IF(AND(H75558>90,H75558<=105),"Week 19 & 21",
IF(AND(H75558>105,H75558<=120),"Week 22 & 24","Full"))))))))))

 

[ranman256]

This seems like something youd put in a lookup table, instead of code.
Then you submit the parameter, then it looks up the return value.

 

[VBABEGINER]

This seems like something youd put in a lookup table, instead of code.
Then you submit the parameter, then it looks up the return value.

Thank You for your reply.. But Can I get something specific what to put across

 

[ranman256]

insert a new module and paste the function into it.

usage:
in sheet1, enter 50 in "A2" and put function in A3 as: =GetCaption(A2)

Public Function getCaption(ByVal piGivenVal As Integer)
Dim r As Integer
Dim vCurrVal
Dim bFound As Boolean


r = 0
Sheets("table").Activate
vCurrVal = Sheets("table").Range("A2").Value

    While vCurrVal <> "" And Not bFound
       If piGivenVal <= vCurrVal Then
          getCaption = Sheets("table").Range("A2").Offset(r, 1).Value
          bFound = True
       End If
    
       r = r + 1
       vCurrVal = Sheets("table").Range("A2").Offset(r, 0).Value  'next row
    Wend
End Function

the "table" tab data looks like 2 columns:
LIMIT , NAME
5 , Week 1
10, Week 2
20 , Week 3 & 4
30 , Week 5 & 6
40, Week 7 & 8
50, Week 9 & 10
60 , Week 11 & 12
70, Week 13 & 14
80, Week 15 & 16
85, Week 17
100 , Week 18 & 20
115, Week 21 & 23
130, Week 24 & 26
131, Week 27 onwards

 

[VBABEGINER]

insert a new module and paste the function into it.

usage:
in sheet1, enter 50 in "A2" and put function in A3 as: =GetCaption(A2)

Public Function getCaption(ByVal piGivenVal As Integer)
Dim r As Integer
Dim vCurrVal
Dim bFound As Boolean


r = 0
Sheets("table").Activate
vCurrVal = Sheets("table").Range("A2").Value

    While vCurrVal <> "" And Not bFound
       If piGivenVal <= vCurrVal Then
          getCaption = Sheets("table").Range("A2").Offset(r, 1).Value
          bFound = True
       End If
   
       r = r + 1
       vCurrVal = Sheets("table").Range("A2").Offset(r, 0).Value  'next row
    Wend
End Function

the "table" tab data looks like 2 columns:
LIMIT , NAME
5 , Week 1
10, Week 2
20 , Week 3 & 4
30 , Week 5 & 6
40, Week 7 & 8
50, Week 9 & 10
60 , Week 11 & 12
70, Week 13 & 14
80, Week 15 & 16
85, Week 17
100 , Week 18 & 20
115, Week 21 & 23
130, Week 24 & 26
131, Week 27 onwards

Thank You RanMan.. I appreciate your time and effort to resolve issue and help someone.. Give me little time to check at my end..