+ Reply to Thread
Results 1 to 3 of 3

Is it possible to format cells based on their value? (Not a conditional format query)

  1. #1
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Is it possible to format cells based on their value? (Not a conditional format query)

    Hi,

    I've created a report that shows the sales for our various contracts, the majority are for several million but some of the newer ones have only had a couple of grand go through to date. At the moment the sales are presented rounded to the nearest million (£#,##0.0,,"m"), but where the sales are currently less than £50k the value shows as £0.0m which gives the impression that the contract hasn't done any business yet.

    Is it possible to have a format that covers all of the sales cells and formats them to the nearest million (£#,##0.0,,"m") when the value is +£50k but formats them to the nearest thousand (£#,##0.0,"k") when the value is less than £50k?

    Thanks in advance,

    Snook
    Last edited by The_Snook; 09-26-2012 at 04:50 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Is it possible to format cells based on their value? (Not a conditional format query)

    Try this format
    [<50000] #,##0.0,"k";[>=50000] #,##0,,"m"

    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Is it possible to format cells based on their value? (Not a conditional format query)

    Bob on! Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1