I need to create a formula that can update when new entries and entered in
the rows of a single page spreadsheet.
Here is the problem. This is a standardized format for my office so all I
can do is fix their equation. The prior year numbers are on the 2nd row of
the spreadsheet, and they enter new entires in successive rows on the form so
that they can track staff strength numbers on a weekly basis for the entire
year. They want an equation that will recompute the weekly comparison to the
prior year's ttl number. I can't just use SUM because we're not tracking
growth. We're trying to track variation to the prior year, and each week the
cell I need to refer to for the current week's numbers changes because it is
one row lower.
So I need to figure out an equation that will allow me to refer to the last
cell in a column that has a value, and I need it to update as new values are
added in cells below the previous last cell to track those cells as the new
reference cells.
Thank you for your time.
--
Adapt, adjust and overcome.

This can get you the last value in column A assuming that your formula is in
A2

=MATCH(9.99999999999999E+307,A3:A65536)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SPCjcMIARNG" wrote in message
...
I need to create a formula that can update when new entries and entered in
the rows of a single page spreadsheet.
Here is the problem. This is a standardized format for my office so all I
can do is fix their equation. The prior year numbers are on the 2nd row
of
the spreadsheet, and they enter new entires in successive rows on the form
so
that they can track staff strength numbers on a weekly basis for the
entire
year. They want an equation that will recompute the weekly comparison to
the
prior year's ttl number. I can't just use SUM because we're not tracking
growth. We're trying to track variation to the prior year, and each week
the
cell I need to refer to for the current week's numbers changes because it
is
one row lower.
So I need to figure out an equation that will allow me to refer to the
last
cell in a column that has a value, and I need it to update as new values
are
added in cells below the previous last cell to track those cells as the
new
reference cells.
Thank you for your time.
--
Adapt, adjust and overcome.

Sorry, not last value, I meant the last cell index, so you need to add 2 to
get the row number.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SPCjcMIARNG" wrote in message
...
I need to create a formula that can update when new entries and entered in
the rows of a single page spreadsheet.
Here is the problem. This is a standardized format for my office so all I
can do is fix their equation. The prior year numbers are on the 2nd row
of
the spreadsheet, and they enter new entires in successive rows on the form
so
that they can track staff strength numbers on a weekly basis for the
entire
year. They want an equation that will recompute the weekly comparison to
the
prior year's ttl number. I can't just use SUM because we're not tracking
growth. We're trying to track variation to the prior year, and each week
the
cell I need to refer to for the current week's numbers changes because it
is
one row lower.
So I need to figure out an equation that will allow me to refer to the
last
cell in a column that has a value, and I need it to update as new values
are
added in cells below the previous last cell to track those cells as the
new
reference cells.
Thank you for your time.
--
Adapt, adjust and overcome.

If your staff strength is in column B, then =OFFSET($B$1,COUNT(B:B),0) should
pick up the last numeric entry in that column.
--Bruce

"SPCjcMIARNG" wrote:

I need to create a formula that can update when new entries and entered in
the rows of a single page spreadsheet.
Here is the problem. This is a standardized format for my office so all I
can do is fix their equation. The prior year numbers are on the 2nd row of
the spreadsheet, and they enter new entires in successive rows on the form so
that they can track staff strength numbers on a weekly basis for the entire
year. They want an equation that will recompute the weekly comparison to the
prior year's ttl number. I can't just use SUM because we're not tracking
growth. We're trying to track variation to the prior year, and each week the
cell I need to refer to for the current week's numbers changes because it is
one row lower.
So I need to figure out an equation that will allow me to refer to the last
cell in a column that has a value, and I need it to update as new values are
added in cells below the previous last cell to track those cells as the new
reference cells.
Thank you for your time.
--
Adapt, adjust and overcome.

Would that give me the variance, or a listing of the initial value and the
current?
--
Adapt, adjust and overcome.


"bpeltzer" wrote:

If your staff strength is in column B, then =OFFSET($B$1,COUNT(B:B),0) should
pick up the last numeric entry in that column.
--Bruce

"SPCjcMIARNG" wrote:

I need to create a formula that can update when new entries and entered in
the rows of a single page spreadsheet.
Here is the problem. This is a standardized format for my office so all I
can do is fix their equation. The prior year numbers are on the 2nd row of
the spreadsheet, and they enter new entires in successive rows on the form so
that they can track staff strength numbers on a weekly basis for the entire
year. They want an equation that will recompute the weekly comparison to the
prior year's ttl number. I can't just use SUM because we're not tracking
growth. We're trying to track variation to the prior year, and each week the
cell I need to refer to for the current week's numbers changes because it is
one row lower.
So I need to figure out an equation that will allow me to refer to the last
cell in a column that has a value, and I need it to update as new values are
added in cells below the previous last cell to track those cells as the new
reference cells.
Thank you for your time.
--
Adapt, adjust and overcome.

I tired that, the "0" gives it a no column value for the count, so the
formula will always come up with 0 for an answer. When the column width is
increased to 1 to allow for the column included by the reference, the
equation spits out how many cells in that column have values not the value in
the last filled cell. The input in these cells are numerical values for
those weeks, not by name listings that are updated weekly. Thank you for the
input though.
--
Adapt, adjust and overcome.


"bpeltzer" wrote:

If your staff strength is in column B, then =OFFSET($B$1,COUNT(B:B),0) should
pick up the last numeric entry in that column.
--Bruce

"SPCjcMIARNG" wrote:

I need to create a formula that can update when new entries and entered in
the rows of a single page spreadsheet.
Here is the problem. This is a standardized format for my office so all I
can do is fix their equation. The prior year numbers are on the 2nd row of
the spreadsheet, and they enter new entires in successive rows on the form so
that they can track staff strength numbers on a weekly basis for the entire
year. They want an equation that will recompute the weekly comparison to the
prior year's ttl number. I can't just use SUM because we're not tracking
growth. We're trying to track variation to the prior year, and each week the
cell I need to refer to for the current week's numbers changes because it is
one row lower.
So I need to figure out an equation that will allow me to refer to the last
cell in a column that has a value, and I need it to update as new values are
added in cells below the previous last cell to track those cells as the new
reference cells.
Thank you for your time.
--
Adapt, adjust and overcome.

=OFFSET($A$1,MATCH(9.99999999999999E+307,A:A)-1,0)

Assuming the numbers are in Column A

- Search

"SPCjcMIARNG" wrote:

I need to create a formula that can update when new entries and entered in
the rows of a single page spreadsheet.
Here is the problem. This is a standardized format for my office so all I
can do is fix their equation. The prior year numbers are on the 2nd row of
the spreadsheet, and they enter new entires in successive rows on the form so
that they can track staff strength numbers on a weekly basis for the entire
year. They want an equation that will recompute the weekly comparison to the
prior year's ttl number. I can't just use SUM because we're not tracking
growth. We're trying to track variation to the prior year, and each week the
cell I need to refer to for the current week's numbers changes because it is
one row lower.
So I need to figure out an equation that will allow me to refer to the last
cell in a column that has a value, and I need it to update as new values are
added in cells below the previous last cell to track those cells as the new
reference cells.
Thank you for your time.
--
Adapt, adjust and overcome.