Category: CELL

GoLast to jump to last cell in a column

Posted on By ANmar

Edit 2021-07-12: I needed this formula to work in a worksheet having space in its name, so original one did not work, here is the new version: =HYPERLINK(“[“&MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-1)&”]'”&MID(CELL(“filename”,A1),SEARCH(“]”,CELL(“filename”,A1))+1,500)&”‘!B”&COUNT(B:B)+6,”Go Last”) Again, above formula works in a worksheet with space in its name (and any other character), it already works in a workbook with space in its…

Read More “GoLast to jump to last cell in a column” »

CELL, COUNT, Formulas - combined functions, HYPERLINK, Information, Lookup and References, Standard functions

Extract current (Active) workbook name

Posted on By ANmar

I need that more often than I thought It is basically extract the full workbook name that we are in, with no folders, and with no extension =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) So, here it is….   And if we want to go 1 step further, I often have the version of the tool as part of the filename,…

Read More “Extract current (Active) workbook name” »

CELL, Formulas - combined functions, Information, Lookup and References, MID, SEARCH, Standard functions, Texts and Strings

Hyperlink usage and HyperlinkOf UDF

Posted on By ANmar No Comments on Hyperlink usage and HyperlinkOf UDF

The Hyperlink function is really powerful and yet not widely used. You can create a whole navigation system with Hyperlink fx or create a custom jump to link to take user to certain area. Of course the “Jump-to” link need other functions, like Match, Indirect and Index Here is a sample of what we can do…

Read More “Hyperlink usage and HyperlinkOf UDF” »

CELL, Formulas - combined functions, HYPERLINK, HyperlinkOf, Information, Lookup and References, MID, Standard functions, User-Defined f(x)s = UDF