# Thread: Determining if a Specific Value Ever Exists for a Project Name

1. ## Determining if a Specific Value Ever Exists for a Project Name

Hi, I have a spreadsheet that in Column F has 1 of 3 choices, "C", "E" or "S". In Column E the spreadsheet has "Project/Activity" which is the name of the Activity/Project. The spreadsheet is around 500 rows in length. It is possible that in Column F for "Project/Activity" to have a "C" in one row and and "E" in another row.

I would like a formula that looks at all of Column F and if it ever finds a "C" associated with an activity, the value of "Capital Projects Expense" is returned. If it never finds a "C" but only finds an "E" I would like for "Expense Only Projects Expense" to be returned in corresponding row for that cell in Column AC. I would like for "S" to return the value of "Sensitivity".

All help is warmly appreciated!

=IF(F1="E", "Expense Only Projects Expense",IF(F1="S","Sensitivity",IF(AND(F1="C",E1= "Activity"),"Capital Projects Expense","??")))
Copy it down the column.

I wasn't sure what you wanted if there was a "C" in col F but it was not associated with "Activity" in Col E.

Steve

3. Hi Steve

In your formula, you will get "Expense Only Projects Expense" if there is an "E" in cell [F1]
But if there is a "C" in say, row345 cell [F345], with the same project name in [E345] as per in cell [E1], then my understanding is that Mitch is asking for the result of "Capital Projects Expense"

I am assuming that every data row must have either C, E, or S in column [F]
I have assigned range names colE and colF to my data ranges to simplify the formula in column [AC]

See my attached file for my interpretation of the request.

zeddy

4. Ok, I renamed the ranges but this formula returns everything as a Sensitivity...

5. Originally Posted by zeddy
I have assigned range names colE and colF to my data ranges to simplify the formula in column [AC]
You have to define the range names in your workbook as well as copy the formula. HTH

6. And also, empty cells in the choice column (in fact, anything other than "C" or "E" ) will return Sensitivity.
Your first post said that "Column F has 1 of 3 choices, "C", "E" or "S", so I assumed it MUST be one of only these three choices.
..so my formula only checks first to see if there is a "C" anywhere,
..and then, if no C is found, check for E, and then if that isn't found, it must be treated as S then.

zeddy

7. I had to change the formula to the following and it appears to work like a champ. I also had to change it to look at specific cells, eg. e2:e569 (absolute) to get it to work. It didn't work correctly when I used the name ranges...

Either way, great concept and THANKS TO ALL>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•