hi, i have 2 cells, V17 and X25, these cells get values in them depending on
what is entered into on other cells, no data is directly entered into these
cells. Is it possible to have the values that appear in these cells captured
and put into a list on a sheet called 'graphs'? below is what the 'graphs'
sheet would look like.

A B
2 7
3 6
1 3
4 9
5 3
--
thanks for your help

Morgan, AHA!! In your previous efforts, we've always tried to deal with V17
and X25. But what we really need to be dealing with are the cells that you
enter data in to that cause changes in V17 and X25.

So if you will tell us which cell, or cells, you type data into to cause a
change in V17 and do the same for the cell or cells that you type data into
to cause a change in X25, we can come up with the code you need.


"Morgan" wrote:

> hi, i have 2 cells, V17 and X25, these cells get values in them depending on
> what is entered into on other cells, no data is directly entered into these
> cells. Is it possible to have the values that appear in these cells captured
> and put into a list on a sheet called 'graphs'? below is what the 'graphs'
> sheet would look like.
>
> A B
> 2 7
> 3 6
> 1 3
> 4 9
> 5 3
> --
> thanks for your help

thanks J, i will give you the formula that is in V17 and X25 as the formulas
in the cells that are dependent on other cells that themselves have formulas
in them, i will give you the entire list in case it is helpful,

in cell V17:
=V16/25
in cell V16:
=BB28+BC28+BD28
in cells BB28, BC28 & BD28 in order:
=INDEX(J:J;MATCH(9,99999999999999E+307;J:J))
=INDEX(K:K;MATCH(9,99999999999999E+307;K:K))
=INDEX(L:L;MATCH(9,99999999999999E+307;L:L))

in cell X25:
=U25/25
in cell U25:
=U29/U4
in cell U29:
=SUM(P2:P2500)
in cell U4:
=SUM(D22500)

thank you very much for your efforts! cheers

"JLatham" wrote:

> Morgan, AHA!! In your previous efforts, we've always tried to deal with V17
> and X25. But what we really need to be dealing with are the cells that you
> enter data in to that cause changes in V17 and X25.
>
> So if you will tell us which cell, or cells, you type data into to cause a
> change in V17 and do the same for the cell or cells that you type data into
> to cause a change in X25, we can come up with the code you need.
>
>
> "Morgan" wrote:
>
> > hi, i have 2 cells, V17 and X25, these cells get values in them depending on
> > what is entered into on other cells, no data is directly entered into these
> > cells. Is it possible to have the values that appear in these cells captured
> > and put into a list on a sheet called 'graphs'? below is what the 'graphs'
> > sheet would look like.
> >
> > A B
> > 2 7
> > 3 6
> > 1 3
> > 4 9
> > 5 3
> > --
> > thanks for your help

Ok, now it looks like we may have to backtrack even further. What we need to
know are the sheet names and cell addresses where a user actually types
information into a cell that is used by any of the cells leading up to a
change in V17 and X25. Those are going to be cells without a formula in
them, but that are referenced in one of the formulas you've provided here.

For example, you show cell X25 as having formula =U25/25, with U25 also
holding a formula (= U29/U4), and both U29 and U4 have formulas that SUM()
ranges. Perhaps you are typing entries into cells in the P2:P2500 or D22500
ranges?

I begin to see why you had the original code attached to the _Calculate(),
and we may return to that, but it would be much better if we could just test
for a change in a cell/cells where you type in data that eventually affects
the results in V17 and X25. Then we could use the worksheet's _Change()
event rather effectively. The problem with this is that the _Change() event
does not fire when the change is because of the result of a formula
recalculating, only when a user types something into a cell (or deletes
something). So I think the optimum solution is to test for a change in a
cell that is typed into waaaaay back at the very beginning of the chain of
dependencies that lead to a result in V17/X25.

If that's not possible, then we may be able to deal with the Calculate()
event and approach it this way: after recalculating, test V17/X25 for a
change in value and if either has changed then copy that value to the other
location and "remember" the values of V17/X25 for the next time a calculation
is performed to see if they change then or not. The 'remembering' would be
done through a couple of Public (global) variables that would be initialized
during the Workbook_Open() event so we can detect changes later on.


"Morgan" wrote:

> thanks J, i will give you the formula that is in V17 and X25 as the formulas
> in the cells that are dependent on other cells that themselves have formulas
> in them, i will give you the entire list in case it is helpful,
>
> in cell V17:
> =V16/25
> in cell V16:
> =BB28+BC28+BD28
> in cells BB28, BC28 & BD28 in order:
> =INDEX(J:J;MATCH(9,99999999999999E+307;J:J))
> =INDEX(K:K;MATCH(9,99999999999999E+307;K:K))
> =INDEX(L:L;MATCH(9,99999999999999E+307;L:L))
>
> in cell X25:
> =U25/25
> in cell U25:
> =U29/U4
> in cell U29:
> =SUM(P2:P2500)
> in cell U4:
> =SUM(D22500)
>
> thank you very much for your efforts! cheers
>
> "JLatham" wrote:
>
> > Morgan, AHA!! In your previous efforts, we've always tried to deal with V17
> > and X25. But what we really need to be dealing with are the cells that you
> > enter data in to that cause changes in V17 and X25.
> >
> > So if you will tell us which cell, or cells, you type data into to cause a
> > change in V17 and do the same for the cell or cells that you type data into
> > to cause a change in X25, we can come up with the code you need.
> >
> >
> > "Morgan" wrote:
> >
> > > hi, i have 2 cells, V17 and X25, these cells get values in them depending on
> > > what is entered into on other cells, no data is directly entered into these
> > > cells. Is it possible to have the values that appear in these cells captured
> > > and put into a list on a sheet called 'graphs'? below is what the 'graphs'
> > > sheet would look like.
> > >
> > > A B
> > > 2 7
> > > 3 6
> > > 1 3
> > > 4 9
> > > 5 3
> > > --
> > > thanks for your help

Thanks again J,
Each row from 2 to 2500 constitutes an entry for a work session, Cells
A2:A2500 have the date entered into it for when each session commences,
B2:C2500 have start and finish times entered into them, D22500 will have
the hours in decimal, E2:F2500 also have entries each session, does it matter
if the entries are from a drop-down list?
In any one session an entry will be put into one cell in either columns G H
or I, and J K or L, if an entry is put into G it will be matched by an entry
into J, H to K and I to L. Columns M N and O will also have entries from a
drop down list also.
What will cause V17 and X25 to change is when an entry is put into either of
columns J, K or L for that session for all the rows 2 to 2500.
Thank you.
--
thanks


"JLatham" wrote:

> Ok, now it looks like we may have to backtrack even further. What we need to
> know are the sheet names and cell addresses where a user actually types
> information into a cell that is used by any of the cells leading up to a
> change in V17 and X25. Those are going to be cells without a formula in
> them, but that are referenced in one of the formulas you've provided here.
>
> For example, you show cell X25 as having formula =U25/25, with U25 also
> holding a formula (= U29/U4), and both U29 and U4 have formulas that SUM()
> ranges. Perhaps you are typing entries into cells in the P2:P2500 or D22500
> ranges?
>
> I begin to see why you had the original code attached to the _Calculate(),
> and we may return to that, but it would be much better if we could just test
> for a change in a cell/cells where you type in data that eventually affects
> the results in V17 and X25. Then we could use the worksheet's _Change()
> event rather effectively. The problem with this is that the _Change() event
> does not fire when the change is because of the result of a formula
> recalculating, only when a user types something into a cell (or deletes
> something). So I think the optimum solution is to test for a change in a
> cell that is typed into waaaaay back at the very beginning of the chain of
> dependencies that lead to a result in V17/X25.
>
> If that's not possible, then we may be able to deal with the Calculate()
> event and approach it this way: after recalculating, test V17/X25 for a
> change in value and if either has changed then copy that value to the other
> location and "remember" the values of V17/X25 for the next time a calculation
> is performed to see if they change then or not. The 'remembering' would be
> done through a couple of Public (global) variables that would be initialized
> during the Workbook_Open() event so we can detect changes later on.
>
>
> "Morgan" wrote:
>
> > thanks J, i will give you the formula that is in V17 and X25 as the formulas
> > in the cells that are dependent on other cells that themselves have formulas
> > in them, i will give you the entire list in case it is helpful,
> >
> > in cell V17:
> > =V16/25
> > in cell V16:
> > =BB28+BC28+BD28
> > in cells BB28, BC28 & BD28 in order:
> > =INDEX(J:J;MATCH(9,99999999999999E+307;J:J))
> > =INDEX(K:K;MATCH(9,99999999999999E+307;K:K))
> > =INDEX(L:L;MATCH(9,99999999999999E+307;L:L))
> >
> > in cell X25:
> > =U25/25
> > in cell U25:
> > =U29/U4
> > in cell U29:
> > =SUM(P2:P2500)
> > in cell U4:
> > =SUM(D22500)
> >
> > thank you very much for your efforts! cheers
> >
> > "JLatham" wrote:
> >
> > > Morgan, AHA!! In your previous efforts, we've always tried to deal with V17
> > > and X25. But what we really need to be dealing with are the cells that you
> > > enter data in to that cause changes in V17 and X25.
> > >
> > > So if you will tell us which cell, or cells, you type data into to cause a
> > > change in V17 and do the same for the cell or cells that you type data into
> > > to cause a change in X25, we can come up with the code you need.
> > >
> > >
> > > "Morgan" wrote:
> > >
> > > > hi, i have 2 cells, V17 and X25, these cells get values in them depending on
> > > > what is entered into on other cells, no data is directly entered into these
> > > > cells. Is it possible to have the values that appear in these cells captured
> > > > and put into a list on a sheet called 'graphs'? below is what the 'graphs'
> > > > sheet would look like.
> > > >
> > > > A B
> > > > 2 7
> > > > 3 6
> > > > 1 3
> > > > 4 9
> > > > 5 3
> > > > --
> > > > thanks for your help

sorry the sheet name is 'Level One 25' & there are nine sheets in total which
will feed into the graphs sheet, but i was hoping to just figure that out
when got the original VB code, cheers
--
thanks


"JLatham" wrote:

> Ok, now it looks like we may have to backtrack even further. What we need to
> know are the sheet names and cell addresses where a user actually types
> information into a cell that is used by any of the cells leading up to a
> change in V17 and X25. Those are going to be cells without a formula in
> them, but that are referenced in one of the formulas you've provided here.
>
> For example, you show cell X25 as having formula =U25/25, with U25 also
> holding a formula (= U29/U4), and both U29 and U4 have formulas that SUM()
> ranges. Perhaps you are typing entries into cells in the P2:P2500 or D22500
> ranges?
>
> I begin to see why you had the original code attached to the _Calculate(),
> and we may return to that, but it would be much better if we could just test
> for a change in a cell/cells where you type in data that eventually affects
> the results in V17 and X25. Then we could use the worksheet's _Change()
> event rather effectively. The problem with this is that the _Change() event
> does not fire when the change is because of the result of a formula
> recalculating, only when a user types something into a cell (or deletes
> something). So I think the optimum solution is to test for a change in a
> cell that is typed into waaaaay back at the very beginning of the chain of
> dependencies that lead to a result in V17/X25.
>
> If that's not possible, then we may be able to deal with the Calculate()
> event and approach it this way: after recalculating, test V17/X25 for a
> change in value and if either has changed then copy that value to the other
> location and "remember" the values of V17/X25 for the next time a calculation
> is performed to see if they change then or not. The 'remembering' would be
> done through a couple of Public (global) variables that would be initialized
> during the Workbook_Open() event so we can detect changes later on.
>
>
> "Morgan" wrote:
>
> > thanks J, i will give you the formula that is in V17 and X25 as the formulas
> > in the cells that are dependent on other cells that themselves have formulas
> > in them, i will give you the entire list in case it is helpful,
> >
> > in cell V17:
> > =V16/25
> > in cell V16:
> > =BB28+BC28+BD28
> > in cells BB28, BC28 & BD28 in order:
> > =INDEX(J:J;MATCH(9,99999999999999E+307;J:J))
> > =INDEX(K:K;MATCH(9,99999999999999E+307;K:K))
> > =INDEX(L:L;MATCH(9,99999999999999E+307;L:L))
> >
> > in cell X25:
> > =U25/25
> > in cell U25:
> > =U29/U4
> > in cell U29:
> > =SUM(P2:P2500)
> > in cell U4:
> > =SUM(D22500)
> >
> > thank you very much for your efforts! cheers
> >
> > "JLatham" wrote:
> >
> > > Morgan, AHA!! In your previous efforts, we've always tried to deal with V17
> > > and X25. But what we really need to be dealing with are the cells that you
> > > enter data in to that cause changes in V17 and X25.
> > >
> > > So if you will tell us which cell, or cells, you type data into to cause a
> > > change in V17 and do the same for the cell or cells that you type data into
> > > to cause a change in X25, we can come up with the code you need.
> > >
> > >
> > > "Morgan" wrote:
> > >
> > > > hi, i have 2 cells, V17 and X25, these cells get values in them depending on
> > > > what is entered into on other cells, no data is directly entered into these
> > > > cells. Is it possible to have the values that appear in these cells captured
> > > > and put into a list on a sheet called 'graphs'? below is what the 'graphs'
> > > > sheet would look like.
> > > >
> > > > A B
> > > > 2 7
> > > > 3 6
> > > > 1 3
> > > > 4 9
> > > > 5 3
> > > > --
> > > > thanks for your help

I'll see what I can come up with this evening - now that we have a starting
point, should go much smoother than it has to date. I'll try to write up the
code in such a fashion that it can be easily adapted/extended to other sheets.

"Morgan" wrote:

> sorry the sheet name is 'Level One 25' & there are nine sheets in total which
> will feed into the graphs sheet, but i was hoping to just figure that out
> when got the original VB code, cheers
> --
> thanks
>
>
> "JLatham" wrote:
>
> > Ok, now it looks like we may have to backtrack even further. What we need to
> > know are the sheet names and cell addresses where a user actually types
> > information into a cell that is used by any of the cells leading up to a
> > change in V17 and X25. Those are going to be cells without a formula in
> > them, but that are referenced in one of the formulas you've provided here.
> >
> > For example, you show cell X25 as having formula =U25/25, with U25 also
> > holding a formula (= U29/U4), and both U29 and U4 have formulas that SUM()
> > ranges. Perhaps you are typing entries into cells in the P2:P2500 or D22500
> > ranges?
> >
> > I begin to see why you had the original code attached to the _Calculate(),
> > and we may return to that, but it would be much better if we could just test
> > for a change in a cell/cells where you type in data that eventually affects
> > the results in V17 and X25. Then we could use the worksheet's _Change()
> > event rather effectively. The problem with this is that the _Change() event
> > does not fire when the change is because of the result of a formula
> > recalculating, only when a user types something into a cell (or deletes
> > something). So I think the optimum solution is to test for a change in a
> > cell that is typed into waaaaay back at the very beginning of the chain of
> > dependencies that lead to a result in V17/X25.
> >
> > If that's not possible, then we may be able to deal with the Calculate()
> > event and approach it this way: after recalculating, test V17/X25 for a
> > change in value and if either has changed then copy that value to the other
> > location and "remember" the values of V17/X25 for the next time a calculation
> > is performed to see if they change then or not. The 'remembering' would be
> > done through a couple of Public (global) variables that would be initialized
> > during the Workbook_Open() event so we can detect changes later on.
> >
> >
> > "Morgan" wrote:
> >
> > > thanks J, i will give you the formula that is in V17 and X25 as the formulas
> > > in the cells that are dependent on other cells that themselves have formulas
> > > in them, i will give you the entire list in case it is helpful,
> > >
> > > in cell V17:
> > > =V16/25
> > > in cell V16:
> > > =BB28+BC28+BD28
> > > in cells BB28, BC28 & BD28 in order:
> > > =INDEX(J:J;MATCH(9,99999999999999E+307;J:J))
> > > =INDEX(K:K;MATCH(9,99999999999999E+307;K:K))
> > > =INDEX(L:L;MATCH(9,99999999999999E+307;L:L))
> > >
> > > in cell X25:
> > > =U25/25
> > > in cell U25:
> > > =U29/U4
> > > in cell U29:
> > > =SUM(P2:P2500)
> > > in cell U4:
> > > =SUM(D22500)
> > >
> > > thank you very much for your efforts! cheers
> > >
> > > "JLatham" wrote:
> > >
> > > > Morgan, AHA!! In your previous efforts, we've always tried to deal with V17
> > > > and X25. But what we really need to be dealing with are the cells that you
> > > > enter data in to that cause changes in V17 and X25.
> > > >
> > > > So if you will tell us which cell, or cells, you type data into to cause a
> > > > change in V17 and do the same for the cell or cells that you type data into
> > > > to cause a change in X25, we can come up with the code you need.
> > > >
> > > >
> > > > "Morgan" wrote:
> > > >
> > > > > hi, i have 2 cells, V17 and X25, these cells get values in them depending on
> > > > > what is entered into on other cells, no data is directly entered into these
> > > > > cells. Is it possible to have the values that appear in these cells captured
> > > > > and put into a list on a sheet called 'graphs'? below is what the 'graphs'
> > > > > sheet would look like.
> > > > >
> > > > > A B
> > > > > 2 7
> > > > > 3 6
> > > > > 1 3
> > > > > 4 9
> > > > > 5 3
> > > > > --
> > > > > thanks for your help