Filter in Lookup based on another Option

If you wish to add a filter in a Lookup function which is based on the value of another option, then make sure that the NL and all its filters are in 1 cell. If not, then a new value of the option is not changing the lookup of the second one.

I had this really long function: NL("Lookup";TableX;FieldX;FilterField1;NL("Filter";TableY;FieldY;FilterField1;CellRefToOtherOption))
Actually it was a bit longer than this one - both NLs were based on the Dimension value table :roll:

So I put the NLs in 2 different cells. Great idea - or so I thought, because it did not work. When I combined all into 1 cell it worked.

And this is what I learnt from Support when I reported this phenomena:

""… you are correct that a value from the first lookup is not changing the lookup of the second lookup, however, it has nothing to do with NL("Filter") nor is this any kind of defect in the product. … This is by design. When using the Lookup feature of Jet Reports, automatic calculation is turned off and any cell outside of the scope of the lookup is not updated. We do this for performance reasons. If we did not do this, then when you selected a lookup value, it could be possible to trigger the recalculation of the entire workbook, which we do not want to do yet. If you want to use your NL("Filters"), then they will have to be with the NL("Lookup") formula."