Last Reference Name function

  • Thread starter Thread starter Joe Blow
  • Start date Start date
J

Joe Blow

I am in the need of some assistance...

I have some number of names, ie wk2, wk6, wk8, wk9 & wk10, the list
of named references could go out to wk52

I have 1,2,3,...52 in rows b3:b54

I am trying to determine the last named reference.

I have been looking at some thing like
{=max(if(isref(indirect("wk"&(b3:b54))),b3:b54,0))}
but that doesn't work

What I need is the last number, in this case 10, for the last valid
reference.

Any help would be appreciated.

Joe
 
Hi
try the following using a helper column
in C3 enter the following
=ISREF(INDIRECT("wk" & B3))
copy down

now use the following formula to get the last valid entry
=INDEX(B3:B54,SUMMENPRODUKT(MAX((C3:C54)*ROW(C3:C54))))
 
SUMMENPRODUKT

You now Frank, I really do envy you for being able to *think* in both
languages at the same time.

Makes me sorry that I never obeyed Grandma and learned my Polish lessons.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi
try the following using a helper column
in C3 enter the following
=ISREF(INDIRECT("wk" & B3))
copy down

now use the following formula to get the last valid entry
=INDEX(B3:B54,SUMMENPRODUKT(MAX((C3:C54)*ROW(C3:C54))))
 
Hi RD
thanks, I was having a private (German) conversation about SUMPRODUCT
and get lost :-)

For the OP use
=INDEX(B3:B54,SUMPRODUCT(MAX((C3:C54)*ROW(C3:C54))))
 
Hi RD
thanks, I was having a private (German) conversation about SUMPRODUCT
and get lost :-)

For the OP use
=INDEX(B3:B54,SUMPRODUCT(MAX((C3:C54)*ROW(C3:C54))))
Click to expand...

Hi Frank,

Sorry for the late thanks, but thanks!

Your help did the job, appreciated.

Joe
 
Back
Top