Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Not exactly sure, but how about this in AC1?
    =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. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, I renamed the ranges but this formula returns everything as a Sensitivity...
    Last edited by mitch06251962; 2013-03-12 at 13:11.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by zeddy View Post
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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. #7
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
  •