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

johnc
Fri Jul 25 15:13:02 CDT 2008

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

Cordell
Fri Jul 25 15:26:06 CDT 2008

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