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

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
_______


<(E-Mail Removed)> wrote in message
news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>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

On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> 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
> _______
>
> <ryan.fitzpatri...@safeway.com> wrote in message
>
> news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>
> >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.

On Dec 28, 9:52 am, ryan.fitzpatri...@safeway.com wrote:
> On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>
>
>
> > 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
> > _______
>
> > <ryan.fitzpatri...@safeway.com> wrote in message
>
> >news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>
> > >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.

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

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
_______


<(E-Mail Removed)> wrote in message
news:29bfce55-91f1-48fb-8006-(E-Mail Removed)...
> On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>> 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
>> _______
>>
>> <ryan.fitzpatri...@safeway.com> wrote in message
>>
>> news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>>
>> >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.

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...@SPAMpeltiertech.com>
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...@safeway.com> wrote in message
>
> news:29bfce55-91f1-48fb-8006-(E-Mail Removed)...
>
> > On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> > wrote:
> >> 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
> >> _______
>
> >> <ryan.fitzpatri...@safeway.com> wrote in message
>
> >>news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>
> >> >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.

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



On Dec 28, 2:59 pm, ryan.fitzpatri...@safeway.com wrote:
> 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...@SPAMpeltiertech.com>
> 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...@safeway.com> wrote in message
>
> >news:29bfce55-91f1-48fb-8006-(E-Mail Removed)...
>
> > > On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> > > wrote:
> > >> 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
> > >> _______
>
> > >> <ryan.fitzpatri...@safeway.com> wrote in message
>
> > >>news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>
> > >> >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.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


<(E-Mail Removed)> wrote in message
news:b46ab76b-21f5-43c4-91b6-(E-Mail Removed)...
> 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...@SPAMpeltiertech.com>
> 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...@safeway.com> wrote in message
>>
>> news:29bfce55-91f1-48fb-8006-(E-Mail Removed)...
>>
>> > On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
>> > wrote:
>> >> 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
>> >> _______
>>
>> >> <ryan.fitzpatri...@safeway.com> wrote in message
>>
>> >>news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>>
>> >> >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.
>

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







On Jan 2, 5:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
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 worksheet.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> <ryan.fitzpatri...@safeway.com> wrote in message
>
> news:b46ab76b-21f5-43c4-91b6-(E-Mail Removed)...
>
> > 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...@SPAMpeltiertech.com>
> > 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...@safeway.com> wrote in message
>
> >>news:29bfce55-91f1-48fb-8006-(E-Mail Removed)...
>
> >> > On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> >> > wrote:
> >> >> 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
> >> >> _______
>
> >> >> <ryan.fitzpatri...@safeway.com> wrote in message
>
> >> >>news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>
> >> >> >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.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


<(E-Mail Removed)> wrote in message
news:5e47e7ac-583c-4a08-b2e4-(E-Mail Removed)...
> 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
>
>
>
>
>
>
>
> On Jan 2, 5:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> 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 worksheet.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. -http://PeltierTech.com
>> _______
>>
>> <ryan.fitzpatri...@safeway.com> wrote in message
>>
>> news:b46ab76b-21f5-43c4-91b6-(E-Mail Removed)...
>>
>> > 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...@SPAMpeltiertech.com>
>> > 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...@safeway.com> wrote in message
>>
>> >>news:29bfce55-91f1-48fb-8006-(E-Mail Removed)...
>>
>> >> > On Dec 27, 10:40 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
>> >> > wrote:
>> >> >> 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
>> >> >> _______
>>
>> >> >> <ryan.fitzpatri...@safeway.com> wrote in message
>>
>> >> >>news:f9e7edb2-71d9-4560-99aa-(E-Mail Removed)...
>>
>> >> >> >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.
>