Shortest Formula Challenge #6: Scrabbled 33

The challenge this week is as follows (you can download the workbook here):

It is you to start in a game of Scrabble, and your rack of letters (cell Q2) is DGTAROZ.

You are a strong enough player to have deduced a list of all possible words from the Engligh language of two or more letters which can be formed from this rack. Given that list in Q5:Q77, derive a single formula to return the word from that list which returns the highest score when placed on the board (A5:O15).



Incrementing Indirect Column References Within SUMIF(S)/COUNTIF(S) 13

Most Excel users are aware that, when a formula containing relative column references is copied to further columns, those references are updated accordingly. So, for example, the formula:


when dragged to the right, will become, successively:


etc., etc.

And so we have a relatively (no pun intended) simple means by which we can obtain a conditional sum from successive columns.

But what if the range we wish to increment is being referenced indirectly? For example, what if we are using a version of the above, but in which the sheet being referenced is dynamic, viz:


where A1 contains the sheet name (e.g. “Sheet1”) which is to be referenced at any given time?