At 11:55 18/01/2020 -0500, Vince Bonly wrote:
Note: Cells with a yellow background identify required data must be
entered. Cells with a (darker) gray background identify cells which
show calculated results.
I have a Calc sheet that I enter data on a regular basis, as shown below:
[D] [E] [F] [G] [H] <-------- Column headings
*/_Today is Week #_/* */_Today's Date Is_/* */_Game
#1_/* */_Game #2 _/* */_Game #3_/* */_Match
Series_/* */_Match Average_/*
*1* 6-Sep 152 146 187 485 161.7
*2* 13-Sep 133 159 141 433 144.3
*3* 20-Sep 147 155 148 450 150.0
*4* 27-Sep 161 151 142 454 151.3
*5* 4-Oct 130 139 116 385 128.3
*6* 11-Oct *Did Not Bowl. *
*7* 18-Oct 148 187 119 454 151.3
*8* 25-Oct 155 167 169 491 163.7
*9* 1-Nov 140 141 157 438 146.0
*10* 8-Nov 147 135 155 437 145.7
*11* 15-Nov 139 133 155 427 142.3
*12* 22-Nov 164 162 164 490 163.3
*13* 29-Nov 120 110 155 385 128.3
*14* 6-Dec 150 136 125 411 137.0
*15* 13-Dec 170 187 132 489 163.0
*16* 20-Dec 160 168 146 474 158.0
*17* 27-Dec 160 144 150 454 151.3
*18* 3-Jan 151 141 138 430 143.3
*/_---Half Season---_/*
*19* 10-Jan *Did Not Bowl. *
*20* 17-Jan 161 134 147 442 147.3
*21* 24-Jan
I use the results generated in columns G and H in another section of
the sheet, as shown:
[AL] [AN] <-------- Column headings
*Team #2- ACHES & PAINS v2.0
Post Match Play*
*Russ: * 458 *Player's Average: * 152.67
*Vince: * 442 147.33
*Dave: * 555 185.00
*Bob: * 600 200.00
Cells AL7 and AN7 are set to = the results calculated and provided
within cells G26 and H26, i.e., they show: 442 and 147.3.
My problem is that after calculating an update result within columns
G and H, e.g., on 24 Jan, (week # 21's data), the results based on
entry of data in D27:F27, there will be new results in the next row,
at cells G27 and H27. Therefore, I am forced to manually change the
formulae within AL7 and AN7 to correctly reflect the updated results
provided in columns AL and AN, by changing =G26 to =G27 and by
changing =H26 to =H27.
Is there an easier method of handling this task, through use of a
function or a formula?
I hope this is described clearly enough. If needed, I will attempt
to store the complete calc sheet at a Dropbox link. Let me know.
On another mailing list to which I am subscribed - nothing to do with
office suites - enquirers are expected to provide *minimal* examples,
and that lesson could be learned here. Almost everything you've
included in your message is irrelevant, and it is a matter of some
work to try to guess what your underlying problem is. People are
perhaps unlikely to want to carry out that work, especially if they
cannot be guaranteed that their efforts will transpire to be
relevant. That work of simplification should in all fairness fall, of
course, to you. And there are no yellow or gray backgrounds showing
in your message, so that's not relevant, I hope.
I think your problem reduces to finding a reference to the last
member of a list in a column. Is that right?
One way to do something like this is to add a "sentinel" value at the
end of the list in at least one of your columns. You need to choose
some value that cannot otherwise appear in the headings or data. If
you put, say, "X" in the first unused row of column A (you could
choose any column), then
=MATCH("X";A1:A999;0)
will return the row number of the row containing the sentinel. Note
that MATCH() returns the index within the range provided, so would
not return the row number if you gave it a data range did not start
in row 1. But as long as you choose a sentinel value that does not
appear in your headings, you can start the range from row 1.
Now you need to harvest the values in the cells above the sentinel
row in the relevant columns, and there are probably many ways to do
this. Examples for the value in column G might be:
=OFFSET(G1;MATCH("X";A1:A999;0)-2;0)
or
=INDIRECT("G"&MATCH("X";A1:A999;0)-1)
But I fear (and hope!) that someone may be able to provide a neater solution.
One other possibility is to reverse your original table, so that the
most recent week is at the top. You would use Insert | Rows (or
right-click | Insert Rows) to insert a new row for each new week.
Then the row from which values need to be harvested does not change.
Does that fit your needs?
Incidentally, it is not obvious what you want to appear in your
second table if the current week has "Did Not Bowl." and appears to
have no values in the relevant columns (G and H) of the first table.
Do you require blanks, zeroes, or the values from the previous row?
If the last case, you might choose to put your sentinel value instead
in one of the columns G or H, where it could stay in the current row.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
