+ Reply to Thread
Results 1 to 10 of 10

Formatting the number in a cell based upon the value of another cell

  1. #1
    Registered User
    Join Date
    02-24-2013
    Location
    Apex, North Carolina, USA
    MS-Off Ver
    365® for Mac
    Posts
    9

    Formatting the number in a cell based upon the value of another cell

    I want to format a number in a particular cell based upon the value of another cell in different worksheet within the same workbook. For example, I have an index worksheet in my workbook that uses a particular index cell whose value changes depending upon the units of measurement that have been selected by the user when entering a particular value. If the value of of this index cell (Worksheet4! $B$1) is 1, then I want the format of the number in Worksheet2 $L$5 to be 0.0000; and if the value of Worksheet4! $B$1 is 2, then I want the format of the number in Worksheet2 $L$5 to be 0.00; and if the value of Worksheet4! $B$1 is 3, then I want the format of the number in Worksheet1 $L$5 to be 0.0; and if the value of Worksheet4! $B$1 is 4, then I wan't the format of the number in Worksheet2 $L$5 to be 0.0.

    I haven't been able to figure out how to program this. (I'm using Excel 2011.)
    Fred L Johnson
    Apex, North Carolina, USA

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Formatting the number in a cell based upon the value of another cell

    .
    In Sheet2 / L5 :
    Please Login or Register  to view this content.
    Format the cell to have 4 spaces after the decimal.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Formatting the number in a cell based upon the value of another cell

    I would expect some basic conditional formatting to achieve this. https://support.office.com/en-us/art...b-f1951ff89d7f Your formatting rules will use a formula like =Worksheet4!$B$1=1 and you will set an appropriate number format for each possible value. Follow the instructions in the help file, and let us know if you get stuck.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    02-24-2013
    Location
    Apex, North Carolina, USA
    MS-Off Ver
    365® for Mac
    Posts
    9

    Re: Formatting the number in a cell based upon the value of another cell

    Quote Originally Posted by Logit View Post
    .
    In Sheet2 / L5 :
    Please Login or Register  to view this content.
    Format the cell to have 4 spaces after the decimal.
    This formula appears to set the VALUE of Sheet2/L5 to zero. I want to set the number format (not the value) of the cell to a particular number of decimal places based upon the value in Sheet4/L1.

  5. #5
    Registered User
    Join Date
    02-24-2013
    Location
    Apex, North Carolina, USA
    MS-Off Ver
    365® for Mac
    Posts
    9

    Re: Formatting the number in a cell based upon the value of another cell

    Quote Originally Posted by MrShorty View Post
    I would expect some basic conditional formatting to achieve this. [Link to help file deleted.] Your formatting rules will use a formula like =Worksheet4!$B$1=1 and you will set an appropriate number format for each possible value. Follow the instructions in the help file, and let us know if you get stuck.
    The help file appears to only apply to formatting the color of the font or the color of the cell. I need more specific information on how to conditionally format the number. How do you construct the formula for conditionally setting the number format of a cell?
    Last edited by Cheddington; 09-13-2018 at 01:12 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Formatting the number in a cell based upon the value of another cell

    Yes, it seems that most of the examples out there look at font or color, but conditional formatting can do more than colors/fonts. If you try this in your own version of Excel (assuming it is similar to mine), at step "5. Click format" step "6. In the color box...", there should also be a "number" box/tab. If you click in the number format box/tab, you should be able to set the number format that goes with the conditional format rule you are working with. Make conditional format rules for each condition you anticipate.

    As for constructing the formula for the conditional formatting rule, it can be any formula that will evaluate to TRUE/FALSE. In your case, I would expect a formula like =Worksheet4!$B$1=1.

    Does that help clarify?

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Formatting the number in a cell based upon the value of another cell

    .
    Does this help ?

    Paste in Sheet2 Module"

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-24-2013
    Location
    Apex, North Carolina, USA
    MS-Off Ver
    365® for Mac
    Posts
    9

    Re: Formatting the number in a cell based upon the value of another cell

    Quote Originally Posted by MrShorty View Post
    Yes, it seems that most of the examples out there look at font or color, but conditional formatting can do more than colors/fonts. If you try this in your own version of Excel (assuming it is similar to mine), at step "5. Click format" step "6. In the color box...", there should also be a "number" box/tab. If you click in the number format box/tab, you should be able to set the number format that goes with the conditional format rule you are working with. Make conditional format rules for each condition you anticipate.

    As for constructing the formula for the conditional formatting rule, it can be any formula that will evaluate to TRUE/FALSE. In your case, I would expect a formula like =Worksheet4!$B$1=1.

    Does that help clarify?
    Using Excel 2011, under Format/ Conditional Formatting... I select New Rule... I then must select a style from 2-Color Scale, 3-Color Scale, Data Bar, Icon Set, and Classic. I select the style Classic. There is no step offered that resembles Step 4 below shown in the help file:

    "4. Under Format values where this formula is true, type the formula: =A2>TODAY()"
    Last edited by Cheddington; 09-13-2018 at 02:09 PM.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Formatting the number in a cell based upon the value of another cell

    With a little research, it appears that the "custom format using a formula" is not available to the Mac version of Excel. I saw one suggestion to create the spreadsheet rules on a windows machine, and the Mac version will correctly run the conditional formatting.

    A Macro solution like Logit proposed may work, though I know that some VBA has trouble in the Mac environment. Since my spreadsheets never interact with an Apple OS, I don't know anything about the incompatibilities.

  10. #10
    Registered User
    Join Date
    02-24-2013
    Location
    Apex, North Carolina, USA
    MS-Off Ver
    365® for Mac
    Posts
    9

    Re: Formatting the number in a cell based upon the value of another cell

    Quote Originally Posted by Logit View Post
    .
    Does this help ?

    Paste in Sheet2 Module"

    Please Login or Register  to view this content.
    Thanks, Logit, for your suggestion. I have not been able to get the following customization of the macro to change the format of Recipe $K$59. The number format of the value in $K$59 is always the formatting assigned to the cell, i.e. 0.0000, regardless of the value in Indices $J$19.

    Please Login or Register  to view this content.
    Last edited by Cheddington; 09-15-2018 at 11:55 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create number list for a second cell based on number selected in first cell
    By AceCo55 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-31-2018, 10:40 AM
  2. Replies: 8
    Last Post: 05-17-2014, 02:33 PM
  3. Replies: 1
    Last Post: 04-18-2013, 02:51 PM
  4. Replies: 2
    Last Post: 07-08-2010, 05:52 AM
  5. Formatting the zoom based on max number of characters in a cell
    By poopdish in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2007, 01:13 PM
  6. formatting cell number based on previous cell number
    By Pasquini in forum Excel General
    Replies: 3
    Last Post: 06-20-2006, 01:40 AM
  7. number formatting based on cell value
    By helen@bgs in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-24-2005, 10:05 PM

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