This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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!

Posted by Andy Brown on 23 February 2021

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:

=IF(

target_spills,

LET(

target,

INDEX(

grid_prev,

target_top_left_cellno + AbsOffsets0Arr

word,

IF(

K$4>0,

WordArr,

WordArrRev

IF(

SUM((LEN(target)>0)*(target<>word))>0,

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:

=AND(Calcs!$H8+Calcs!K$2*(Calcs!$D8-1)>=1,Calcs!$H8+Calcs!K$2*(Calcs!$D8-1)<=Rows,Calcs!$I8+Calcs!K$3*(Calcs!$D8-1)>=1,Calcs!$I8+Calcs!K$3*(Calcs!$D8-1)<=Cols)

In addition to all of this, Andrew has included every Excel trick in the book, including the ones shown in the table below.

Trick	Notes
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).