U_Shrestha
04-29-2008, 07:58 AM
Hello all,
I am wondering if my problem best fits the Case statement criteria instead of Nested IF. I want to determine what type of Tank-test is required as per State and County Regulations. Basically, I want to fill two columns AG6:downward dyanamic range and AH6: downward dynamic range based on other column values.
Column AG criteria:
Condition 1:if c6="",""
Conditon 2: if H6="*Underground*"
AND I6="1. In-Service" OR "2. Temporarily Out of Service"
AND O6 = "0. None",
Then "5-Yearly Test"
Condition 3: if H6="*Underground*"
AND I6="1. In-Service" OR "2. Temporarily Out of Service"
AND O6 = "*Double-walled*",
Then "Test exempted"
Condition 4: If J6<12/27/1986
AND O6="None"
Then "Illegal Tank"
Column AH criteria:
Condition 1: If AJ6 = "Rockland" OR "Westchester"
M6 = "*Steel*"
O6 = "0. None"
J6 = > 25 years from Today()
I6 = "1. In-Service" OR "2. Temporarily Out of Service"
H6 = *Underground*
Then "Yearly Test"
Else, "Follow State Regs"
I want to use SEARCH function for those texts that are inside asterisks. Right now there is only one criteria for Column AH but it will increase to 2-3 criteria (counties), which I want to be add later.
Thanks.
I am wondering if my problem best fits the Case statement criteria instead of Nested IF. I want to determine what type of Tank-test is required as per State and County Regulations. Basically, I want to fill two columns AG6:downward dyanamic range and AH6: downward dynamic range based on other column values.
Column AG criteria:
Condition 1:if c6="",""
Conditon 2: if H6="*Underground*"
AND I6="1. In-Service" OR "2. Temporarily Out of Service"
AND O6 = "0. None",
Then "5-Yearly Test"
Condition 3: if H6="*Underground*"
AND I6="1. In-Service" OR "2. Temporarily Out of Service"
AND O6 = "*Double-walled*",
Then "Test exempted"
Condition 4: If J6<12/27/1986
AND O6="None"
Then "Illegal Tank"
Column AH criteria:
Condition 1: If AJ6 = "Rockland" OR "Westchester"
M6 = "*Steel*"
O6 = "0. None"
J6 = > 25 years from Today()
I6 = "1. In-Service" OR "2. Temporarily Out of Service"
H6 = *Underground*
Then "Yearly Test"
Else, "Follow State Regs"
I want to use SEARCH function for those texts that are inside asterisks. Right now there is only one criteria for Column AH but it will increase to 2-3 criteria (counties), which I want to be add later.
Thanks.