Help: Need to know function to find the end of worksheet

J

jay

Question:

Is there a formula (function) which will tell me the end of th
worksheet.

Assume I have a worksheet whose last row and column is: "M55" (colum
"M", row "55")

Is there a function(formula) which will return this result ?...

I can't seem to find one in books or manuals.

Thank
 
B

Bob Phillips

Ctrl-End will get you there.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

You could use a formula like:

=ADDRESS(MAX(IF($2:$9999<>"",ROW($2:$9999))),
MAX(IF($2:$9999<>"",COLUMN($2:$9999))))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

And don't put the formula in one of the cells referred to in the formula--you'll
get a circular reference. I used a cell in row 1.

Ps. The more cells/rows you use, the slower excel will get. It has a lot of
cells to check!
 
T

tkt_tang

1. And there, don't put the formula in one of the cells referred to in
the formula -- one will
get a circular reference. I used cell A1.

2. But, there's #N/A in cell A1.

3. Regards.
 
T

tkt_tang

1. Do you have any #n/a errors in the range you used ?

2. Yes, indeed.

3. Regards.
 
D

Dave Peterson

1. Can you change the formulas to return something else:
=if(iserror(yourformula),"somethingelse",yourformula)

2. or you could change the formula:

=ADDRESS(
MAX(IF(ISERROR($2:$9999),ROW($2:$9999),IF($2:$9999<>"",ROW($2:$9999)))),
MAX(IF(ISERROR($2:$9999),COLUMN($2:$9999),IF($2:$9999<>"",COLUMN($2:$9999)))))

Still array entered.
 
T

tkt_tang

1. Thank you. The formula feels better now.

2. Turn that into a hyperlink as follows,

3. =HYPERLINK("#"&ADDRESS(

MAX(IF(ISERROR($2:$1000),ROW($2:$1000),IF($2:$1000<>"",ROW($2:$1000)))),

MAX(IF(ISERROR($2:$1000),COLUMN($2:$1000),IF($2:$1000<>"",COLUMN($2:$1000))))),"LUC")

4. And there, the LUC is one click away.

5. Regards.
 
D

Dave Peterson

But you could have used ctrl-end to get there.


1. Thank you. The formula feels better now.

2. Turn that into a hyperlink as follows,

3. =HYPERLINK("#"&ADDRESS(

MAX(IF(ISERROR($2:$1000),ROW($2:$1000),IF($2:$1000<>"",ROW($2:$1000)))),

MAX(IF(ISERROR($2:$1000),COLUMN($2:$1000),IF($2:$1000<>"",COLUMN($2:$1000))))),"LUC")

4. And there, the LUC is one click away.

5. Regards.
Click to expand...
 
T

tkt_tang

1. And now, there are Options.

2. Ctrl + End may or may not land the selection at the LUC
(accurately). There will be all too often the jazz of deleting those
columns to the right and those rows below till the end (in order to get
to the bottom of the true LUC).

3. By using the Hyperlink, one can vacillate between the top-left and
bottom-right corners (if the clickable bi-di route is preferrable over
striking combo-keys) of the Excel Tabulation.

4. Neither one of the methods would take the user to the LUC of the
Formatted Range.

5. Regards.
 
Top