Find then offset.

P

Pete

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??
 
T

Toppers

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?
 
R

Roger Govier

Hi Pete

One way
=ADDRESS(49,MATCH(LOOKUP(9.999999E+307,B49:IV49),B49: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.
 
P

Pete

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?
 
P

Pete

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
 
P

Pete

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.
 
R

Roger Govier

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,B49:IV49),B49:IV49,0),1))-B49)

should return the result you want.
 
Top