Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Custom Number Format
Is it possible to have text appended to a value entered in a cell that is
dynamic. For example, I know that using the custom number format 0.0" lbs" would give me 4.0 lbs when entering the value 4 into the cell. Is there a way to have the text "lbs" be dynamic based on an input elsewhere in the spreadsheet? Thanks. -- Cordell |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Custom Number Format
Here is an idea.
A1: this will contain your units of measure (other than pounds). Easiest if entered with a space in front of it. Say your unit of measure is HRS, you would put a space followed by HRS in A1. A2: This is the quantity that will be added to unit of measure. A3: =TEXT(A2,"0.0")&$A$1 whenever you wish to calculate with the value, you would use the following: =VALUE(SUBSTITUTE(A3,$A$1,"")) -- John C "Cordell" wrote: Is it possible to have text appended to a value entered in a cell that is dynamic. For example, I know that using the custom number format 0.0" lbs" would give me 4.0 lbs when entering the value 4 into the cell. Is there a way to have the text "lbs" be dynamic based on an input elsewhere in the spreadsheet? Thanks. -- Cordell |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Custom Number Format
Thanks John. I was hoping to do this strictly through formatting for the
very reason you capture, namely, I want to keep these as values versus putting them into text and then converting back to values throughout the spreadsheet. Plus, others will use the model and they aren't quite as Excel saavy. :) -- Cordell "John C" wrote: Here is an idea. A1: this will contain your units of measure (other than pounds). Easiest if entered with a space in front of it. Say your unit of measure is HRS, you would put a space followed by HRS in A1. A2: This is the quantity that will be added to unit of measure. A3: =TEXT(A2,"0.0")&$A$1 whenever you wish to calculate with the value, you would use the following: =VALUE(SUBSTITUTE(A3,$A$1,"")) -- John C "Cordell" wrote: Is it possible to have text appended to a value entered in a cell that is dynamic. For example, I know that using the custom number format 0.0" lbs" would give me 4.0 lbs when entering the value 4 into the cell. Is there a way to have the text "lbs" be dynamic based on an input elsewhere in the spreadsheet? Thanks. -- Cordell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom number format for driver's license number | Excel Discussion (Misc queries) | |||
Moving custom number format to NUMBER | Setting up and Configuration of Excel | |||
dynamic number format | Excel Worksheet Functions | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions |