Re: Adding to text's togther in a data value in a chart

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thank you for the 2ndary axis answer, that was easy. Regarding the
concatenating the number and text, is there away to list the
1,000,000lb on the data label? I know not to series the data that has
the 1,000,000lb, but i'm unsure what I should do.


On Jan 9, 6:41 pm, "Jon Peltier" <jonxlmv...@xxxxxxxxxxxxxxxxxxx>
wrote:
When using a custom number format, make sure you put a number in the cell,
like 1000000. If you put 1,000,000lb in the cell, it's text, not a number,
and if you use the Show Value, no telling which value you'll get.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

<ryan.fitzpatri...@xxxxxxxxxxx> wrote in message

news:5b521c91-04f1-43b5-88ac-2933172ba4a3@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I got the concatenate, but it still will not show on the data label, I
went to custom number and tried to type in #,###lb but it wouldn't
accept that as a custom format. so the 1,000,000lb shows up as 0 on
the data label still.

On Jan 2, 5:40 am, "Jon Peltier" <jonxlmv...@xxxxxxxxxxxxxxxxxxx>
wrote:
To plot something, Excel must recognize it as a number. You can get
"1,000,000lbs" either through string concatenation, which produces a
non-numeric string, or through a custom number format (use #,##0lbs)
which
retains the numerical character of the value, but displays it
differently.
If your cells contain numbers displayed using a custom format, the chart
will plot the numbers, and data labels based on these values will display
as
they display in the work***.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

<ryan.fitzpatri...@xxxxxxxxxxx> wrote in message

news:b46ab76b-21f5-43c4-91b6-73e9c5f49ee7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I'm sorry but I'm confused. I really just want the data label on the
series to read text or alphanumeric, since its 1,000,000lbs. D7
contains the 1,000,000 (numeric quantity) and D8 contains the LB (Unit
of Measure). Your text formula worked great, but when I highlight the
data for the series in the chart nothing shows up. It doesn't
recognize the text. If that's VBA how do I apply that code to the
respective chart series?

On Dec 28, 11:48 am, "Jon Peltier" <jonxlmv...@xxxxxxxxxxxxxxxxxxx>
wrote:
Post on top (which the vast majority of folks here do), so it's easier
to
read the thread in sequence.

If cell D7 contains text instead of a number, then

Sheet1.Range("D7").Value

is zero.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

<ryan.fitzpatri...@xxxxxxxxxxx> wrote in message

news:29bfce55-91f1-48fb-8006-972f6def4828@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@xxxxxxxxxxxxxxxxxxx>
wrote:
Combine the label sections in a single cell in the work***. For
example,
in cell D11, enter a formula like this:

=TEXT(D7,"<number format>")&" "&D9

assuming D7 has the value and D9 has the units.

Then add labels to the chart, using any of the built in options.
Select a
single label (two single clicks are needed: the first to select the
series
of labels, the second to select one label), then type = in the
formula
bar
and click on the cell, so the formula bar reads

=Sheet1!$D$11

and press Enter. To do a bunch of labels in one shot, download one
of
the
following utilities:

Rob Bovey's Chart Labeler,http://appspro.com
John Walkenbach's Chart Tools,http://j-walk.com

I don't know quite what this is doing, between the '+' and the
nested
Format
statements:

Format(Sheet1.Range("D7").Value, "0,000" +
Format(Sheet1.Range("D9").Value,
""))

Use an ampersand (&) to concatenate text, because under some
conditions,
VBA
will coerce the text bits into numbers and actually perform an
addition
instead of a concatenation.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

<ryan.fitzpatri...@xxxxxxxxxxx> wrote in message

news:f9e7edb2-71d9-4560-99aa-f80dec70a338@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I have 2 rows of information. 1) volume (1,000,000) 2) unit of
measure
(lb). I would like to have it where on the data value on the
chart
it
would say 1,000,000lbs combined together. I have label code where
this
works. I also would like it where if I change cell value D5
(where
the information is) it would change the value of the data value
( i
have this part just not the combining of text part).

Private Sub Label1_Change()
Range("d5").Select
Me.Label1.Caption = Format(Sheet1.Range("D7").Value, "0,000" +
Format(Sheet1.Range("D9").Value, ""))

End Sub

But i don't have any labels anymore, i imagine the code would be
similar to this. Any help anyone.

Ryan

That works great, but the value on the series does not reflect this.
It shows up as 0 or blank instead of 1,000,000lb for example.

.

Quantcast