could someone write me a VB code? thanks

[Morgan]

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

 

[JLatham]

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]

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(P22500)
in cell U4:
=SUM(D22500)

thank you very much for your efforts! cheers

 

[JLatham]

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 P22500 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]

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.

 

[Morgan]

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

 

[JLatham]

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.