If you have the subscription version of Excel you may have the new LET function. This function lets you capture variables within a formula. Let’s see an example.

When I heard about the LET function this is the first thing I thought of.

To get the sheet name in a single formula, the formula is

=RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-SEARCH(“]”,CELL(“filename”,A1)))

Notice that the CELL(“filename”,A1) function is repeated three times. I typically do this calculation using two cells instead of one.

I have the CELL(“filename”,A1) function in a separate cell and then refer to that cell three times in another cell with the RIGHT function.

You may also notice in the image above that I referred to cell A1 in cell A1. This doesn’t cause a circular reference because the CELL function returns information rather than performing a calculation.

LET version

The LET function version is

=LET(x,CELL(“filename”,A1),RIGHT(x,LEN(x)-SEARCH(“]”,x)))

The LET function allows you to specify a variable, in this case x. Then specify its value, in this case CELL(“filename”,A1).

You can then perform a calculation using the variable. All this happens within the LET function brackets.

You can create more variables.

The first advantage is the formula is shorter. In general the shorter the formula, the easier it is to read and follow.

Another advantage is the formula may be calculated faster. In our case the CELL(“filename”,A1) function is only calculated once, not three times as with the original formula.

For a single formula this is unlikely to make much difference, but in a complex model applying this approach may speed things up.

This is a new function. It will take a while to get widely applied, but in certain situations it can shorten formulas and speed up calculations.

I prefer to break up more complex functions across multiple cells. This shortens formulas and make them more transparent, readable an understandable. You can also use range names to hold values. So I am not sure I will use this function much, but time will tell.