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

[ryan.fitzpatrick3]

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

 

[Jon Peltier]

Combine the label sections in a single cell in the worksheet. 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

 

[ryan.fitzpatrick3]

Combine the label sections in a single cell in the worksheet. 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
_______

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.

 

[ryan.fitzpatrick3]

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.

Im not using labels anymore, just the value on the series.

 

[Jon Peltier]

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

 

[ryan.fitzpatrick3]

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?

 

[ryan.fitzpatrick3]

I'm not adding in a control tooblox label. FYI. I had that for example
of what I did, but I erased those labels.

 

[Jon Peltier]

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 worksheet.

- Jon

 

[ryan.fitzpatrick3]

Jon,
Thanks for the help, it works great. I got another question though.
How do I get 2ndary Y axis labels on the right side of the chart with
figures I have in a range?

Ryan

 

[Jon Peltier]

You need to format the scale of the secondary X axis, and check 'Y Value
Axis Crosses At Maximum'. If you don't see the secondary X axis, go to Chart
menu > Chart Options > Axes tab, and check the box for Secondary Category
(X) Axis, then fix the scale, then return to Chart Options and remove the
axis.

- Jon

 

[ryan.fitzpatrick3]

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.

 

[Jon Peltier]

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

 

[ryan.fitzpatrick3]

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.

 

[ryan.fitzpatrick3]

On my chart I have 6 items, on the 2ndary axis is the inventory, is
there a way to embed a text box or label into the chart that will sum
up the total inventory? I figured out the suming up part on the text
box or label, but what I haven't figured out is how to embed the text
box into the chart where if I put the chart into a powerpoint the
textbox shows up with the chart or stays in the same place if I resize
the chart. Also is there away I can automatically changed the sum,
without running the macro to update the total? Is there a way to
format in $ to. Thanks.

 

[Jon Peltier]

You can add a linked textbox to a chart. Calculate your total in a cell,
format the cell in the appropriate currency format. Select the chart, type =
in the formula bar, and click on the cell. A link to the cell appears:

=Sheet1!$C$16

Press enter to lock it in. The textbox will update whenever the cell
recalculates.

- Jon

 

[ryan.fitzpatrick3]

Regarding the 2ndary x axis, i have my 2ndary data as a variance,
where zero is the middle of the graph and the variance can go from -.4
to positive .4. But when I add the 2ndary axis information, that data
is not in the middle of the graph anymore and gets right justified. Is
there away to keep the 2ndary axis information and still have the
graph formatted how I orginally had it?

 

[Jon Peltier]

What do you mean by right-justified? Is it scrunched to the right part of
the chart? Then you'd better look at the X values.

- Jon

 

[ryan.fitzpatrick3]

Yes it's on the right part of the chart instead of the middle where it
should be at.