PDA

View Full Version : Sleeper: Case Statement?



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.

Bob Phillips
04-29-2008, 08:56 AM
You can't use Case in a formula, you are stuck with Ifs.

U_Shrestha
04-29-2008, 09:02 AM
Is there a VB solution to this? Thanks.

Bob Phillips
04-29-2008, 09:05 AM
I guess so, but it is so simple why not a WS function?

U_Shrestha
04-29-2008, 09:14 AM
The criteria for second part, Column AH will have different criteria based on each county, right now there is only one condtion for AH, but it will keep increasing as the site are being inspected. So I thought I will learn how to do this with VB, and I couldn't write IF formula too :(