Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format IF? (Excel 2000)

    Is there a way to create a formula that will format the cell (e.g. Percent vs Currency vs General Number) based on specific conditions. For example, if the value of the cell is <1 then the result will be formatted as a %, but if it is >1, then it will use currency? If multiple formulas are needed this is fine.

    Reason for request:
    I have a map that has several text boxes corresponding to each particular state on the map. I have a combo box that selects from a list of categories that is used in a VLOOKUP to produce a number which is combined with the Indirect function, to reference a cell in a pivot table. However, some of these categories result in percentage values while others result in currency.

    Thanks in advance for any assistance.

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format IF? (Excel 2000)

    You can use conditional formatting to change the format of a cell based on its value. Select "Format>Conditional Formatting..."

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format IF? (Excel 2000)

    Using this technique, I cannot change the format from percentage to Currency based on a particular value. This only allows you to change color, font, size, etc.

  4. #4
    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

    Re: Format IF? (Excel 2000)

    You can use a custom format:
    Format - cells - number (tab) custom:
    [<1]0.00%;[>1]$0.00;General

    This will be 2 decimal percent when <1, currency with 2 decimal when >1 and general when =1

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format IF? (Excel 2000)

    The only way I know of to do this would be to use one of the worksheet event routines to change the format. Which event routine you would use would depend on exactly how the cell is being change. From the sould of it, you probably want the worksheet calculate event, but the worksheet change event is another possibility. If you need help doing this, could you upload an example workbook that shows what you want to do. That would save a lot of time and trial and error.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format IF? (Excel 2000)

    Thank you Steve, this is what I was looking for. Will this custom format remain with the workbook or is there something else I need to do to ensure that when I distribute this, the custom formatting continues to work on other computers?

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format IF? (Excel 2000)

    Very true, my brain is not working today <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

  8. #8
    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

    Re: Format IF? (Excel 2000)

    Custom formats are in the workbook and get transferred.

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format IF? (Excel 2000)

    Thanks again.

Posting Permissions

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