559 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|How to build an Excel word search without even using VBA|
|Details of an impressive new Excel word search system which allows you to recreate a randomised grid every time you recalculate your formulae!|
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!
Some other pages relevant to the above blog include:
25 Aytoun Street