Results 1 to 7 of 7
  1. #1
    Hall-Of-Famer
    Points: 65,055, Level: 79
    Level completed: 13%, Points required for next Level: 1,395
    Overall activity: 1.0%
    Achievements:
    SocialVeteran50000 Experience Points
    Cyclonesrule91's Avatar
    Join Date
    Apr 2006
    Location
    Waukee
    Posts
    4,932
    Points
    65,055
    Level
    79
    Thumbs Up
    Received: 10
    Given: 97

    Excel 2010 Conditional Formatting question

    I am using Excel 2010. I am building a spreadsheet the covers a couple different types of insurance. Two types of insurance insure pounds of product, 3 types of insurance insure Revenue on the product based on pounds and price. You select the type of policy and I have the formulas in place to do the calculations. I then have a sub total colum where the total Insurance guarantee is calculated. What I want to do is have it formatted so in case I select one of the two types of insurance that insure pounds that the format would be a number, and if I select one of the types that insure revenue that the format would be in dollars.

    Is there a way to do this?

    Thank you in advance.



  2. #2
    Recruit
    Points: 8,409, Level: 27
    Level completed: 44%, Points required for next Level: 341
    Overall activity: 0%
    Achievements:
    Veteran5000 Experience Points

    Join Date
    Jan 2010
    Location
    Norwalk, IA
    Posts
    75
    Points
    8,409
    Level
    27
    Thumbs Up
    Received: 0
    Given: 0

    Re: Excel 2010 Conditional Formatting question

    Not sure you can use conditional formatting in that way (to format cell contents currency vs number) based another cell input. I know you can make a cell certain colors and to say certain things based on the input of another cell, however.

    You could run Macros if you have a Basic ****** that would hide certain rows or columns based on a cell attribute. So if you select "Pounds", the rows that are hidden are the ones with the cells formatted in Currency, and if you select "Revenue", the rows that are hidden are the cells formatted in Numbers. Not sure if you want to go to that extent, but that's how you can format cells as you are describing above.



  3. #3
    Recruit
    Points: 8,409, Level: 27
    Level completed: 44%, Points required for next Level: 341
    Overall activity: 0%
    Achievements:
    Veteran5000 Experience Points

    Join Date
    Jan 2010
    Location
    Norwalk, IA
    Posts
    75
    Points
    8,409
    Level
    27
    Thumbs Up
    Received: 0
    Given: 0

    Re: Excel 2010 Conditional Formatting question

    $cript is what got censored in my reply, btw.



  4. #4
    Hall-Of-Famer
    Points: 84,644, Level: 90
    Level completed: 50%, Points required for next Level: 906
    Overall activity: 0%
    Achievements:
    Veteran50000 Experience PointsCreated Album pictures
    azepp's Avatar
    Join Date
    Dec 2009
    Location
    Ankeny
    Posts
    3,963
    Points
    84,644
    Level
    90
    Thumbs Up
    Received: 183
    Given: 128

    Re: Excel 2010 Conditional Formatting question

    I'm using 2007 and there is a way to do it. Here are the steps in 2007 - hopefully they are similar in 2010.

    Select the cell you want the conditional formatting to apply to. On the Home ribbon clink on Conditional Formatting and select New Rule. A window opens that gives me an option of what kind of rule I want. On of the choices is "Use a formula to determine which cells to format". Select that.

    Then I enter my formula where it says "Format values where this formula is true:". If cell A1 is the has the text "lb" for a policy that is denomimated in pounds, type =$A$1="lb"

    Then click the "Format..." button and select the formatting you want to apply in that case.



  5. #5
    Pro
    Points: 135,658, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 0%
    Achievements:
    50000 Experience PointsVeteran
    Naughtius's Avatar
    Join Date
    Oct 2010
    Location
    Central IA
    Posts
    2,613
    Points
    135,658
    Level
    100
    Thumbs Up
    Received: 18
    Given: 2

    Re: Excel 2010 Conditional Formatting question

    Quote Originally Posted by CyRyder View Post
    $cript is what got censored in my reply, btw.
    Potty mouth


    "Me not winning isn't important. You do!"

  6. #6
    Hall-Of-Famer
    Points: 65,055, Level: 79
    Level completed: 13%, Points required for next Level: 1,395
    Overall activity: 1.0%
    Achievements:
    SocialVeteran50000 Experience Points
    Cyclonesrule91's Avatar
    Join Date
    Apr 2006
    Location
    Waukee
    Posts
    4,932
    Points
    65,055
    Level
    79
    Thumbs Up
    Received: 10
    Given: 97

    Re: Excel 2010 Conditional Formatting question

    Quote Originally Posted by CyRyder View Post
    Not sure you can use conditional formatting in that way (to format cell contents currency vs number) based another cell input. I know you can make a cell certain colors and to say certain things based on the input of another cell, however.

    You could run Macros if you have a Basic ****** that would hide certain rows or columns based on a cell attribute. So if you select "Pounds", the rows that are hidden are the ones with the cells formatted in Currency, and if you select "Revenue", the rows that are hidden are the cells formatted in Numbers. Not sure if you want to go to that extent, but that's how you can format cells as you are describing above.
    Thanks for the reply. I think with this explanation you helped me figure out how to conditionally format it. When you brought up the dissapearing columns based on cell attribute, I went to conditional formatting and formatted based on a formula. Then I made a cell that would either have Pounds or Revenue in it based on which type of insurance. So the formulay I put in was if that cell equals pounds, then number format, otherwise it is revenue based.

    So thanks a lot for the help.....

    Edit: Thanks a lot Azepp. You helped me out as well.


    Last edited by Cyclonesrule91; 12-07-2010 at 03:00 PM.

  7. #7
    Hall-Of-Famer
    Points: 84,644, Level: 90
    Level completed: 50%, Points required for next Level: 906
    Overall activity: 0%
    Achievements:
    Veteran50000 Experience PointsCreated Album pictures
    azepp's Avatar
    Join Date
    Dec 2009
    Location
    Ankeny
    Posts
    3,963
    Points
    84,644
    Level
    90
    Thumbs Up
    Received: 183
    Given: 128

    Re: Excel 2010 Conditional Formatting question

    Create a new rule for revenue where cell A1 maybe says "rev" instead of "lb". The steps are the same except that your formula is now =$A$1="rev" and your chosen formatting would be different.

    Edit: Never mind! Looks like you got it.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
  • TV: FOX
  • Iowa State vs. Baylor
  • September 27, 2014
  • 07:20 PM