The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. This policy is subject to change at any time. The owner is not an attorney, and nothing posted on this site should be construed as legal advice. Litigation Support Tip of the Night does not provide confirmation that any e-discovery technique or conduct is compliant with legal, regulatory, contractual or ethical requirements.
While working in Excel, you may have seen this error pop up:
It will lock you out of your current workbook, and other workbooks that you may have open. This message may be prompted if you click on a link to a PDF on a worksheet, and then try to OCR it. The message will prevent you from continuing to edit in Excel until the OCR process has been completed. Clicking OK will not make it go away. Cancel text recognition in the PDF editor to get around this error message.
Today while using Excel 2019 I noticed a very disconcerting problem. When you have hyperlinks added to a large number of cells (static links, not links added with the HYPERLINK formula), sorting and filtering on the worksheet can cause the links to be removed - or shifted to other cells. I saw some sign that the links stayed in position on the worksheet - in the same range - after the data was moved.
This is a glitch that has been documented on the Microsoft support site. See this April 2018 post. As Microsoft points out the problem is particularly bad because after the links are removed, "[a]lthough the blue, underlined text appears in the cell, nothing happens when you click the link." Microsoft states that the problem occurs with links that have been copied, and I noticed it happening today with links that I had entered using CTRL + D.
Microsoft doesn't offer a solution other than editing the links manually one by one. As the great Allen Wyatt of Excel Tips points out, this "isn't a real solution."
In Excel, it's not possible to use VLOOKUP if the value you are searching for in a range is longer than 255 characters. In this example, the value in cell B3 is longer than 255 characters:
. . . so it can't be found in the range G:H and we get a #VALUE! error, even though there is a match for this value in G7. (The lengths of each cell in column G are shown in column I.)
We can get around this problem by using the below function posted here,
Function betterSearch(searchCell, A As Range, B As Range) For Each cell In A If cell.Value = searchCell.Value Then betterSearch = B.Cells(cell.Row, 1) Exit For End If betterSearch = "Not found" Next
End Function
This function will autofill when you type it in the cell.
=BetterSearch(A2,G:G,H:H)
Enter the searched for value, the column to find it in, and then the column the adjacent value should be returned from.
A search for value of greater than 255 characters gets a result.