Plotting A Chart From One Variable

Hi

I have a variable in cell A1, that I need to plot a histogram of. I need to
create two axis to create a real time chart, where axis one will be an index
value of when the variable in cell A1 changes value, and axis two will be the
new variable value.

Any Ideas would be appreciated.

Thanks
Alec

A chart needs data to plot. You would need some mechanism, probably
programmatic, to record values from A1 as well as times when A1 changes.
These two additional sets of values, stored perhaps in columns B and C,
would form the source data for the chart.

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


"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
> Hi
>
> I have a variable in cell A1, that I need to plot a histogram of. I need
> to
> create two axis to create a real time chart, where axis one will be an
> index
> value of when the variable in cell A1 changes value, and axis two will be
> the
> new variable value.
>
> Any Ideas would be appreciated.
>
> Thanks
> Alec

Thanks Jon

But how do I create the mechanism to store the data in columns B and C.


"Jon Peltier" wrote:

> A chart needs data to plot. You would need some mechanism, probably
> programmatic, to record values from A1 as well as times when A1 changes.
> These two additional sets of values, stored perhaps in columns B and C,
> would form the source data for the chart.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
> > Hi
> >
> > I have a variable in cell A1, that I need to plot a histogram of. I need
> > to
> > create two axis to create a real time chart, where axis one will be an
> > index
> > value of when the variable in cell A1 changes value, and axis two will be
> > the
> > new variable value.
> >
> > Any Ideas would be appreciated.
> >
> > Thanks
> > Alec
>
>
>

That depends on how the data in A1 is provided. If it's a user-entered
value, you could use the Worksheet_Change event of the worksheet to note the
time of the change in the first blank cell in column B and the new value of
A1 in the adjacent cell in column C. If it's a DDE link, you could use the
Worksheet_Calculate event in the same way.

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


"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:F6263395-85FA-4C8E-AE9B-2828B3799955@microsoft.com...
> Thanks Jon
>
> But how do I create the mechanism to store the data in columns B and C.
>
>
> "Jon Peltier" wrote:
>
>> A chart needs data to plot. You would need some mechanism, probably
>> programmatic, to record values from A1 as well as times when A1 changes.
>> These two additional sets of values, stored perhaps in columns B and C,
>> would form the source data for the chart.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
>> news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
>> > Hi
>> >
>> > I have a variable in cell A1, that I need to plot a histogram of. I
>> > need
>> > to
>> > create two axis to create a real time chart, where axis one will be an
>> > index
>> > value of when the variable in cell A1 changes value, and axis two will
>> > be
>> > the
>> > new variable value.
>> >
>> > Any Ideas would be appreciated.
>> >
>> > Thanks
>> > Alec
>>
>>
>>

You might need to insert a dummy formula just to ensure there is a
recalculation when A1 is updated, something like =A1 in a cell someplace.

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


"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
news:uvUNksa3HHA.5316@TK2MSFTNGP04.phx.gbl...
> That depends on how the data in A1 is provided. If it's a user-entered
> value, you could use the Worksheet_Change event of the worksheet to note
> the time of the change in the first blank cell in column B and the new
> value of A1 in the adjacent cell in column C. If it's a DDE link, you
> could use the Worksheet_Calculate event in the same way.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> news:F6263395-85FA-4C8E-AE9B-2828B3799955@microsoft.com...
>> Thanks Jon
>>
>> But how do I create the mechanism to store the data in columns B and C.
>>
>>
>> "Jon Peltier" wrote:
>>
>>> A chart needs data to plot. You would need some mechanism, probably
>>> programmatic, to record values from A1 as well as times when A1 changes.
>>> These two additional sets of values, stored perhaps in columns B and C,
>>> would form the source data for the chart.
>>>
>>> - Jon
>>> -------
>>> Jon Peltier, Microsoft Excel MVP
>>> Tutorials and Custom Solutions
>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>> _______
>>>
>>>
>>> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
>>> news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
>>> > Hi
>>> >
>>> > I have a variable in cell A1, that I need to plot a histogram of. I
>>> > need
>>> > to
>>> > create two axis to create a real time chart, where axis one will be an
>>> > index
>>> > value of when the variable in cell A1 changes value, and axis two will
>>> > be
>>> > the
>>> > new variable value.
>>> >
>>> > Any Ideas would be appreciated.
>>> >
>>> > Thanks
>>> > Alec
>>>
>>>
>>>
>
>

That's not a charting question. You'd probably be better off asking in
microsoft.public.excel.programming. What causes the value in A1 to change?
--
David Biddulph

"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:F6263395-85FA-4C8E-AE9B-2828B3799955@microsoft.com...
> Thanks Jon
>
> But how do I create the mechanism to store the data in columns B and C.

> "Jon Peltier" wrote:
>
>> A chart needs data to plot. You would need some mechanism, probably
>> programmatic, to record values from A1 as well as times when A1 changes.
>> These two additional sets of values, stored perhaps in columns B and C,
>> would form the source data for the chart.
>>
>> - Jon

>> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
>> news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
>> > Hi
>> >
>> > I have a variable in cell A1, that I need to plot a histogram of. I
>> > need
>> > to
>> > create two axis to create a real time chart, where axis one will be an
>> > index
>> > value of when the variable in cell A1 changes value, and axis two will
>> > be
>> > the
>> > new variable value.
>> >
>> > Any Ideas would be appreciated.
>> >
>> > Thanks
>> > Alec
>>
>>
>>

Thanks Again Jon

It is a DDE Link from a PLC that will be providing the variable in cell A1,
in cell A2 I have a timer variable that counts up in seconds, My problem is
creating the code that puts these values in the first blank cells in columns
B and C

"Jon Peltier" wrote:

> That depends on how the data in A1 is provided. If it's a user-entered
> value, you could use the Worksheet_Change event of the worksheet to note the
> time of the change in the first blank cell in column B and the new value of
> A1 in the adjacent cell in column C. If it's a DDE link, you could use the
> Worksheet_Calculate event in the same way.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> news:F6263395-85FA-4C8E-AE9B-2828B3799955@microsoft.com...
> > Thanks Jon
> >
> > But how do I create the mechanism to store the data in columns B and C.
> >
> >
> > "Jon Peltier" wrote:
> >
> >> A chart needs data to plot. You would need some mechanism, probably
> >> programmatic, to record values from A1 as well as times when A1 changes.
> >> These two additional sets of values, stored perhaps in columns B and C,
> >> would form the source data for the chart.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> _______
> >>
> >>
> >> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> >> news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
> >> > Hi
> >> >
> >> > I have a variable in cell A1, that I need to plot a histogram of. I
> >> > need
> >> > to
> >> > create two axis to create a real time chart, where axis one will be an
> >> > index
> >> > value of when the variable in cell A1 changes value, and axis two will
> >> > be
> >> > the
> >> > new variable value.
> >> >
> >> > Any Ideas would be appreciated.
> >> >
> >> > Thanks
> >> > Alec
> >>
> >>
> >>
>
>
>

Something like this:

dim iLastRow as long
iLastRow = Range("B65536").end(xlup).row + 1
Cells(iLastRow, 2).Value = Range("A1").Value
Cells(iLastRow, 3).Value = Range("A2").Value

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


"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:AC78213C-32E7-4296-ADB3-19E0CA25BE16@microsoft.com...
> Thanks Again Jon
>
> It is a DDE Link from a PLC that will be providing the variable in cell
> A1,
> in cell A2 I have a timer variable that counts up in seconds, My problem
> is
> creating the code that puts these values in the first blank cells in
> columns
> B and C
>
> "Jon Peltier" wrote:
>
>> That depends on how the data in A1 is provided. If it's a user-entered
>> value, you could use the Worksheet_Change event of the worksheet to note
>> the
>> time of the change in the first blank cell in column B and the new value
>> of
>> A1 in the adjacent cell in column C. If it's a DDE link, you could use
>> the
>> Worksheet_Calculate event in the same way.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
>> news:F6263395-85FA-4C8E-AE9B-2828B3799955@microsoft.com...
>> > Thanks Jon
>> >
>> > But how do I create the mechanism to store the data in columns B and C.
>> >
>> >
>> > "Jon Peltier" wrote:
>> >
>> >> A chart needs data to plot. You would need some mechanism, probably
>> >> programmatic, to record values from A1 as well as times when A1
>> >> changes.
>> >> These two additional sets of values, stored perhaps in columns B and
>> >> C,
>> >> would form the source data for the chart.
>> >>
>> >> - Jon
>> >> -------
>> >> Jon Peltier, Microsoft Excel MVP
>> >> Tutorials and Custom Solutions
>> >> Peltier Technical Services, Inc. - http://PeltierTech.com
>> >> _______
>> >>
>> >>
>> >> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
>> >> news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
>> >> > Hi
>> >> >
>> >> > I have a variable in cell A1, that I need to plot a histogram of. I
>> >> > need
>> >> > to
>> >> > create two axis to create a real time chart, where axis one will be
>> >> > an
>> >> > index
>> >> > value of when the variable in cell A1 changes value, and axis two
>> >> > will
>> >> > be
>> >> > the
>> >> > new variable value.
>> >> >
>> >> > Any Ideas would be appreciated.
>> >> >
>> >> > Thanks
>> >> > Alec
>> >>
>> >>
>> >>
>>
>>
>>

Thanks Again Jon

Went to Excel Programming on David Biddulp suggestion.

Been away from my computer for a while, but just tried your code and got it
working on the press of a "button", if I use the Worksheet_Change event excel
locks up because on every change it adds a new line. So I need to modify your
code so that Column B and C are only updated when the variable value changes.

Thanks again for your time.


"Jon Peltier" wrote:

> Something like this:
>
> dim iLastRow as long
> iLastRow = Range("B65536").end(xlup).row + 1
> Cells(iLastRow, 2).Value = Range("A1").Value
> Cells(iLastRow, 3).Value = Range("A2").Value
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> news:AC78213C-32E7-4296-ADB3-19E0CA25BE16@microsoft.com...
> > Thanks Again Jon
> >
> > It is a DDE Link from a PLC that will be providing the variable in cell
> > A1,
> > in cell A2 I have a timer variable that counts up in seconds, My problem
> > is
> > creating the code that puts these values in the first blank cells in
> > columns
> > B and C
> >
> > "Jon Peltier" wrote:
> >
> >> That depends on how the data in A1 is provided. If it's a user-entered
> >> value, you could use the Worksheet_Change event of the worksheet to note
> >> the
> >> time of the change in the first blank cell in column B and the new value
> >> of
> >> A1 in the adjacent cell in column C. If it's a DDE link, you could use
> >> the
> >> Worksheet_Calculate event in the same way.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> _______
> >>
> >>
> >> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> >> news:F6263395-85FA-4C8E-AE9B-2828B3799955@microsoft.com...
> >> > Thanks Jon
> >> >
> >> > But how do I create the mechanism to store the data in columns B and C.
> >> >
> >> >
> >> > "Jon Peltier" wrote:
> >> >
> >> >> A chart needs data to plot. You would need some mechanism, probably
> >> >> programmatic, to record values from A1 as well as times when A1
> >> >> changes.
> >> >> These two additional sets of values, stored perhaps in columns B and
> >> >> C,
> >> >> would form the source data for the chart.
> >> >>
> >> >> - Jon
> >> >> -------
> >> >> Jon Peltier, Microsoft Excel MVP
> >> >> Tutorials and Custom Solutions
> >> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> >> _______
> >> >>
> >> >>
> >> >> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> >> >> news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
> >> >> > Hi
> >> >> >
> >> >> > I have a variable in cell A1, that I need to plot a histogram of. I
> >> >> > need
> >> >> > to
> >> >> > create two axis to create a real time chart, where axis one will be
> >> >> > an
> >> >> > index
> >> >> > value of when the variable in cell A1 changes value, and axis two
> >> >> > will
> >> >> > be
> >> >> > the
> >> >> > new variable value.
> >> >> >
> >> >> > Any Ideas would be appreciated.
> >> >> >
> >> >> > Thanks
> >> >> > Alec
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

A simple test:

dim iLastRow as long
iLastRow = Range("B65536").end(xlup).row
if Cells(iLastRow, 2).Value <> Range("A1").Value then
Cells(iLastRow+1, 2).Value = Range("A1").Value
Cells(iLastRow+1, 3).Value = Range("A2").Value
endif

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


"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:AFCDFD6E-A446-4FBB-B4E1-B24679EEF86B@microsoft.com...
>
> Thanks Again Jon
>
> Went to Excel Programming on David Biddulp suggestion.
>
> Been away from my computer for a while, but just tried your code and got
> it
> working on the press of a "button", if I use the Worksheet_Change event
> excel
> locks up because on every change it adds a new line. So I need to modify
> your
> code so that Column B and C are only updated when the variable value
> changes.
>
> Thanks again for your time.
>
>
> "Jon Peltier" wrote:
>
>> Something like this:
>>
>> dim iLastRow as long
>> iLastRow = Range("B65536").end(xlup).row + 1
>> Cells(iLastRow, 2).Value = Range("A1").Value
>> Cells(iLastRow, 3).Value = Range("A2").Value
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
>> news:AC78213C-32E7-4296-ADB3-19E0CA25BE16@microsoft.com...
>> > Thanks Again Jon
>> >
>> > It is a DDE Link from a PLC that will be providing the variable in cell
>> > A1,
>> > in cell A2 I have a timer variable that counts up in seconds, My
>> > problem
>> > is
>> > creating the code that puts these values in the first blank cells in
>> > columns
>> > B and C
>> >
>> > "Jon Peltier" wrote:
>> >
>> >> That depends on how the data in A1 is provided. If it's a user-entered
>> >> value, you could use the Worksheet_Change event of the worksheet to
>> >> note
>> >> the
>> >> time of the change in the first blank cell in column B and the new
>> >> value
>> >> of
>> >> A1 in the adjacent cell in column C. If it's a DDE link, you could use
>> >> the
>> >> Worksheet_Calculate event in the same way.
>> >>
>> >> - Jon
>> >> -------
>> >> Jon Peltier, Microsoft Excel MVP
>> >> Tutorials and Custom Solutions
>> >> Peltier Technical Services, Inc. - http://PeltierTech.com
>> >> _______
>> >>
>> >>
>> >> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
>> >> news:F6263395-85FA-4C8E-AE9B-2828B3799955@microsoft.com...
>> >> > Thanks Jon
>> >> >
>> >> > But how do I create the mechanism to store the data in columns B and
>> >> > C.
>> >> >
>> >> >
>> >> > "Jon Peltier" wrote:
>> >> >
>> >> >> A chart needs data to plot. You would need some mechanism, probably
>> >> >> programmatic, to record values from A1 as well as times when A1
>> >> >> changes.
>> >> >> These two additional sets of values, stored perhaps in columns B
>> >> >> and
>> >> >> C,
>> >> >> would form the source data for the chart.
>> >> >>
>> >> >> - Jon
>> >> >> -------
>> >> >> Jon Peltier, Microsoft Excel MVP
>> >> >> Tutorials and Custom Solutions
>> >> >> Peltier Technical Services, Inc. - http://PeltierTech.com
>> >> >> _______
>> >> >>
>> >> >>
>> >> >> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:30B72D23-98F5-4395-9C22-85CD9A351954@microsoft.com...
>> >> >> > Hi
>> >> >> >
>> >> >> > I have a variable in cell A1, that I need to plot a histogram of.
>> >> >> > I
>> >> >> > need
>> >> >> > to
>> >> >> > create two axis to create a real time chart, where axis one will
>> >> >> > be
>> >> >> > an
>> >> >> > index
>> >> >> > value of when the variable in cell A1 changes value, and axis two
>> >> >> > will
>> >> >> > be
>> >> >> > the
>> >> >> > new variable value.
>> >> >> >
>> >> >> > Any Ideas would be appreciated.
>> >> >> >
>> >> >> > Thanks
>> >> >> > Alec
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>