LASTINROW function difficulty

Posted by Tom Urtis on March 12, 2001 10:01 PM

Hello fellow Excel-ites --

In range C9:N9 of an Excel 2000 worksheet, I maintain a dynamic display of job titles. Whatever the last job title of that range is, I would like to have appear in cell B7.

I have a copy of John Walkenbach's book "Microsoft Excel 2000 Formulas", and on page 615 is an example of how to use the LASTINROW function, seemingly for this situation. However, I keep getting the #NAME? error when I enter the formula =LASTINROW(C9:N9) in cell B7 (or any cell for that matter), which looks like it should work if I am interpreting his book correctly.

Anyone else have trouble with the LASTINROW & LASTINCOLUMN functions?

Any idea how I could return the last value in my C9:N9 range?

Thanks.

Tom Urtis

Posted by David Hawley on March 13, 2001 12:26 AM

Hi Tom

I would say the "LASTINROW" function is a custom fumction.

If you go to my Web page and click the link "Dynamic Named Ranges" you will see 5 types of dynamic ranges, one of which will suit your list of "Job Titles"

Now lets say you call your dynamic range "MyRange" you could then use this formula to return the last entry:

=INDEX(MyRange,ROWS(MyRange))

..Or If your range has only text, Or you only want the last text entry in a column of Numbers and text and you don't want a dynamic range (for whatever reason) you could use:

=INDEX(A:A,MATCH("*",A:A,-1))

This will return the last text entry in Column A.

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on March 13, 2001 11:05 AM

Tom

Try the following formula (in B7):

=INDEX(C9:N9,COUNTA(C9:N9))

Aladin

Posted by Tom Urtis on March 13, 2001 2:02 PM

Thanks Aladin, both your's and Dave's formulas did the trick and worked perfectly.

Tom