Function problems

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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.