 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2006Help: Need to know function to find the end of worksheet
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 1. Is it the last-used-cell that you are looking for ? 2. Regards. Ctrl-End will get you there.  SignatureHTH RP (remove nothere from the email address if mailing direct) > Question: > [quoted text clipped - 9 lines] > > Thanks 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! > Question: > [quoted text clipped - 15 lines] > jay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2377 > View this thread: http://www.excelforum.com/showthread.php?threadid=501559 SignatureDave Peterson 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. Do you have any #n/a errors in the range you used? > 1. And there, don't put the formula in one of the cells referred to in > the formula -- one will [quoted text clipped - 3 lines] > > 3. Regards. SignatureDave Peterson 1. Do you have any #n/a errors in the range you used ? 2. Yes, indeed. 3. Regards. 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. > 1. Do you have any #n/a errors in the range you used ? > > 2. Yes, indeed. > > 3. Regards. SignatureDave Peterson 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. But you could have used ctrl-end to get there. > 1. Thank you. The formula feels better now. > [quoted text clipped - 9 lines] > > 5. Regards. SignatureDave Peterson 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.