Determine Row Number of Last Value in column

**lake54** · 05-15-2009, 02:37 PM

Hi guys,

Trying to do a simple workbook here, with hopefully no macros/VB script.

Essentially, I want a cell to display the row number (-1 to take into account the heading) which has the last value contained in it.

The workbook is a list of users that have signed up in 1 month, and is distributed to staff for a particular reason.

The cell I'm trying to create is part of a 'stats' section.

If it can be done with a simple formula, then that's great!

Any help is appreciated, and I'm a bit of a geek, so you can talk techie with me :-)

Lake54

**NBVC** · 05-15-2009, 02:40 PM

Try either

=Match(9.99999999e+307,A:A)-1 if column of numbers

=Match(rept("z",255),A:A)-1 if column of text strings.

**DonkeyOte** · 05-15-2009, 02:41 PM

I'm not sure you need the -1 adjustment using the MATCH approach.

**lake54** · 05-15-2009, 02:43 PM

Thanks for your replies - I'll just try them now (by the way, they contain both numbers and text, some are just numbers though).

**NBVC** · 05-15-2009, 02:43 PM

You are right... I don't think so either... I think I misread the part the OP had in brackets above...

so should be:

=Match(9.99999999e+307,A:A) if column of numbers

=Match(rept("z",255),A:A) if column of text strings.

**DonkeyOte** · 05-15-2009, 02:44 PM

lake54, be sure to check out Bob Phillips' article: http://www.xldynamic.com/source/xld.LastValue.html

**lake54** · 05-15-2009, 02:45 PM

Wow - thanks!

Just for future reference, the -1 was needed.

Quick response as well. I'll definitely be coming back here in the future!

**NBVC** · 05-15-2009, 02:48 PM

Originally Posted by lake54

Wow - thanks!

Just for future reference, the -1 was needed.

Quick response as well. I'll definitely be coming back here in the future!

well, there you go... I don't know if I misread or was luck... I guess it depended on what exactly you wanted in the end....

Please mark thread as Solved.