BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Posted by Andy Brown on 23 February 2021
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Building a word search in Excel without macros
So last month I shared WOWSER (the Wise Owl Word Search Excel Replicator) using VBA macros. Andrew Howe of Transformaction asked about a non-macro version of a word search in Excel, and I - foolishly - expressed my opinion that it couldn't be done.
I reckoned without Excel formulae like this:
target_top_left_cellno + AbsOffsets0Arr
1 + SUM((LEN(target)>0)*(target=word))
This would be impressive enough, but many of the range names (shown in red above) are formulae themselves. For example, here's the formula for the range that the target_spills range above references:
In addition to all of this, Andrew has included every Excel trick in the book, including the ones shown in the table below.
|The INDIRECT function||This can always be relied upon to do complicated things in Excel (the downside is that the resulting formulae can be very difficult to understand and test).|
|The LET function||A new Excel feature allowing you to use variables in Excel (I blogged about it recently).|
|Lambda functions||An even newer Excel feature which allows you to create your own functions in Excel without using VBA, like this.|
|The FILTER function||The new Excel dynamic array functions allow you to do all sorts of clever filtering, avoiding messy (and slow) SUMPRODUCT functions.|
|Relative ranges||Most people don't even realise that a range reference is stored as a formula rather than as an absolute reference (it's what allows you to create fancy things like dynamic range names in Excel).|
The results are impressive - there's something very satisfying about being able to press F9 to recalculate a workbook and seeing this:
One suggested solution for a set of related words.
Same grid size, same words ... totally different solution!
You can see how the algorithm works, how the formulae were built and other (much-needed!) documentation at Andrew's word search Excel lab page.
A word of advice: make yourself a strong cup of coffee before visiting this page!