View Full Version : Solved: Convert Formula to Return a Row
Cyberdude
01-14-2006, 10:58 AM
The following formula will return the address of the last used value in array "A1:A500". It?s an array formula, so CTL+SHIFT+ENTER must be used to enter it into a cell.
=ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),COLUMN(A:A))
What change must I make to the formula so it will to return the row instead of the address?
Bob Phillips
01-14-2006, 11:10 AM
=MAX(IF(ISBLANK(A1:A500),0,ROW(A1:A500)))
also an array formula.
Ken Puls
01-14-2006, 11:21 AM
That's pretty concise!
I was curious if I could extract the number portion using my regular Right(Text,Len(Text)-Find(Character,Text,Start)) method with your array, which does work (array entered):
=RIGHT(ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),COLUMN(A:A)),
LEN(ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),
COLUMN(A:A)))-FIND("$",ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),
COLUMN(A:A)),2))
I'd definitely go Bob's route, though, but thought I'd share my curiosity. :)
EDIT: Had to break up the formula to prevent hugely wide post.
Bob Phillips
01-14-2006, 11:26 AM
Had to break up the formula to prevent hugely wide post.
For info Ken, I tend to enter big formulae in that style in a worksheet as it makes it easier to read and to debug.
Ken Puls
01-14-2006, 11:28 AM
For info Ken, I tend to enter big formulae in that style in a worksheet as it makes it easier to read and to debug.
Hey! Cool tip! :) Something that had never occured to me to be honest. That does make it easier to read. :yes
Cyberdude
01-14-2006, 12:05 PM
Thanx, Dennis! Nice terse solution. http://vbaexpress.com/forum/images/smilies/friendship.gif
Bob Phillips
01-14-2006, 01:06 PM
Thanx, Dennis! Nice terse solution. http://vbaexpress.com/forum/images/smilies/friendship.gif
Who is Dennis?
Cyberdude
01-14-2006, 01:19 PM
Oops! .... Terribly sorry, xld. http://vbaexpress.com/forum/images/smilies/ohhmygosh.gif Somewhere along the way I thought I learned that your name is Dennis. Guess not.
Bob Phillips
01-14-2006, 02:01 PM
Oops! .... Terribly sorry, xld. http://vbaexpress.com/forum/images/smilies/ohhmygosh.gif Somewhere along the way I thought I learned that your name is Dennis. Guess not.
You are confusing xld, me - Bob, with XL-Dennis, Dennis.
Zack Barresse
01-14-2006, 05:56 PM
Bob - Dennis, it's easy to get the two mixed up. ;)
geekgirlau
01-14-2006, 08:07 PM
Hey! Cool tip! :) Something that had never occured to me to be honest. That does make it easier to read. :yes
Wow, you learn something new every day! Great tip :thumb
Bob Phillips
01-15-2006, 06:11 AM
Bob - Dennis, it's easy to get the two mixed up. ;)
I take that as a huge compliment http://vbaexpress.com/forum/images/smilies/001.gif
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.