Find then offset.
 

I'm Looking for a formula that will allow me to select a starting cell (B49),
then find and select the second to last active cell in that row. I have a
living spread sheet that grows and shrinks based on user input and want a
formula to calculate the days between the start (B49) and finish (B???).

Can anyone help??
--
Pete

Find then offset.
 

Assuming no embedded blanks between B49 and the last cell then:

=INDIRECT("B"&COUNTA(B49:B1000)+47)

will return contents of last but one cell in column B. Change upper limit of
B to suit.

Does that help?

"Pete" wrote:

I'm Looking for a formula that will allow me to select a starting cell (B49),
then find and select the second to last active cell in that row. I have a
living spread sheet that grows and shrinks based on user input and want a
formula to calculate the days between the start (B49) and finish (B???).

Can anyone help??
--
Pete

Find then offset.
 

Hi Pete

One way
=ADDRESS(49,MATCH(LOOKUP(9.999999E+307,B49:IV49),B 49:IV49,0),1)

In my test I had a date entered in L49 and the formula return $K$49.
It returns one column less, because we are starting from column B, and
not column A. If you wanted column L returned, then put a +1 before the
,1 at the end of the formula.

You said the second to last active cell, in my case there was no data in
I,J or K but L was the last entry in the row that had any value at all.
I'm not certain that this is what you meant, or whether there will
always be contiguous data in the row for you and in which case there
will be a date in K49 for you to use in your calculation.

--
Regards

Roger Govier


"Pete" wrote in message
...
I'm Looking for a formula that will allow me to select a starting cell
(B49),
then find and select the second to last active cell in that row. I
have a
living spread sheet that grows and shrinks based on user input and
want a
formula to calculate the days between the start (B49) and finish
(B???).

Can anyone help??
--
Pete

Find then offset.
 

Kind of, but not really, I think I missed spoke on my previous message. Here
is my version of the formula and what im trying to do.

*****
=SUM(GETPIVOTDATA("Qty Prod",$A$48)/DAYS360(B49,J49))

*********
The first part of the formula is lookig at the "Grand Total" of pivot table
where I want to take that number and divide it by the days between my first
entry and my last. The table grows and shrinks based on the description I
choose. Sometimes my last entry will be in cell "J49" and sometimes it will
be in Cell "ZZ49". The "Day360" formula would work here, if I could figure
out where that last entry will be everytime the decription changes.

Any thoughts?
--
Pete


"Toppers" wrote:

Assuming no embedded blanks between B49 and the last cell then:

=INDIRECT("B"&COUNTA(B49:B1000)+47)

will return contents of last but one cell in column B. Change upper limit of
B to suit.

Does that help?

"Pete" wrote:

I'm Looking for a formula that will allow me to select a starting cell (B49),
then find and select the second to last active cell in that row. I have a
living spread sheet that grows and shrinks based on user input and want a
formula to calculate the days between the start (B49) and finish (B???).

Can anyone help??
--
Pete

Find then offset.
 

Roger, thanks for the idea, but when I put your formula in my spreadsheet it
returns "$L$49"?

Please review the question thread and look at my second entry for further
clarification.
Thanks
--
Pete


"Roger Govier" wrote:

Hi Pete

One way
=ADDRESS(49,MATCH(LOOKUP(9.999999E+307,B49:IV49),B 49:IV49,0),1)

In my test I had a date entered in L49 and the formula return $K$49.
It returns one column less, because we are starting from column B, and
not column A. If you wanted column L returned, then put a +1 before the
,1 at the end of the formula.

You said the second to last active cell, in my case there was no data in
I,J or K but L was the last entry in the row that had any value at all.
I'm not certain that this is what you meant, or whether there will
always be contiguous data in the row for you and in which case there
will be a date in K49 for you to use in your calculation.

--
Regards

Roger Govier


"Pete" wrote in message
...
I'm Looking for a formula that will allow me to select a starting cell
(B49),
then find and select the second to last active cell in that row. I
have a
living spread sheet that grows and shrinks based on user input and
want a
formula to calculate the days between the start (B49) and finish
(B???).

Can anyone help??
--
Pete

Find then offset.
 

Just to let you know, I figured this out using a range name and offset.
Formula below.

=SUM(GETPIVOTDATA("Qty Prod",$A$48)/DAYS360(B49,OFFSET(Grand_Total,0,-1)))

I'm sure there is an easier way, but this will work for me right now.
--
Pete


"Pete" wrote:

I'm Looking for a formula that will allow me to select a starting cell (B49),
then find and select the second to last active cell in that row. I have a
living spread sheet that grows and shrinks based on user input and want a
formula to calculate the days between the start (B49) and finish (B???).

Can anyone help??
--
Pete

Find then offset.
 

Hi Pete

As I said, the formula returns the cell you are looking for.
As you have since said how you want to use it, then
=GETPIVOTDATA("Qty Prod",$A$48)/
(INDIRECT(ADDRESS(49,MATCH(LOOKUP(9.999999E+307,B4 9:IV49),B49:IV49,0),1))-B49)

should return the result you want.
--
Regards

Roger Govier


"Pete" wrote in message
...
Roger, thanks for the idea, but when I put your formula in my
spreadsheet it
returns "$L$49"?

Please review the question thread and look at my second entry for
further
clarification.
Thanks
--
Pete


"Roger Govier" wrote:

Hi Pete

One way
=ADDRESS(49,MATCH(LOOKUP(9.999999E+307,B49:IV49),B 49:IV49,0),1)

In my test I had a date entered in L49 and the formula return $K$49.
It returns one column less, because we are starting from column B,
and
not column A. If you wanted column L returned, then put a +1 before
the
,1 at the end of the formula.

You said the second to last active cell, in my case there was no data
in
I,J or K but L was the last entry in the row that had any value at
all.
I'm not certain that this is what you meant, or whether there will
always be contiguous data in the row for you and in which case there
will be a date in K49 for you to use in your calculation.

--
Regards

Roger Govier


"Pete" wrote in message
...
I'm Looking for a formula that will allow me to select a starting
cell
(B49),
then find and select the second to last active cell in that row. I
have a
living spread sheet that grows and shrinks based on user input and
want a
formula to calculate the days between the start (B49) and finish
(B???).

Can anyone help??
--
Pete