Formula using Named Range with EVALUATE function only triggered by in-cell calculation

[XOR LX]

Hi all,

Would be grateful of any assistance.

Please see the attached. I have a Defined Name ("Search") with relative references which incorporates the EVALUATE function. This is incorporated in the formulas in the SEARCH tab so, for example, the formula in E2 may be copied across and down and give the desired results.

This works fine until, for example, an appropriate entry in the DATASET tab is amended. (For example, change Birthplace in row 15 to anything else and return to the SEARCH tab.) No change (I am set to Automatic Calculation). Editing the cell with F2 and then committing the array formula anew does force the correct re-calculation.

The point of this exercise is really an experiment to try to circumvent the need to manually amend the Named Range for each column as I drag across, and, since the Defined Ranges are all offset (though with INDEX, not OFFSET, so I presumed this wouldn't be an issue) I am unable to use INDIRECT, hence the attempt with EVALUATE.

I am not very knowledgeable about macros (if EVALUATE can be said to be such) and if anyone can explain why re-calculation is not being triggered in this case, I would be grateful.

Of course, I could (and may well) just go back to either non-offset ranges (so I can use INDIRECT on the header row to reference them) or simply make it so the order of columns in the data table is fixed and in the same order as that of the SEARCH sheet, so that I can simply increment with COLUMNS() in the original formula.

Apologies if this is a little unnecessary and experimental, but would be very interested to know in any case.

Regards

[JosephP]

you can simply make Search volatile using
=IF(NOW(),EVALUATE(SUBSTITUTE(SEARCH!F$1," ","_")))
:-)

[oeldere]

I changed the names in the bleu cells.

Then I activated cell b1 in sheet 1 (F2 and then F4).

For me it works.

See the attached file.

[XOR LX]

@JosephP - that's wonderful!! Just out of interest, why does it require such intervention in order to force a re-calc?

@oeldere - thanks a lot but not sure I understand. The version you attached is not macro-enabled and/or the Search name containing EVALUATE has disappeared.

I'll mark this solved in any case.

Thanks a lot.

[JosephP]

honestly I don't know why it is required-perhaps simply too many levels of indirection

[oeldere]

@XOR XL

You don't have any code in your posted file, so then there is no differance between xlsx and xlsm (as far as I know).

I changed the data in sheet 2 to test your file.

After that I choose cel b1 => then choose F2 and F4).

Then the changed data in sheet 2 (the blue cells) also change in the first sheet.

See the attached file (xlsm).

[JosephP]

You don't have any code in your posted file, so then there is no differance between xlsx and xlsm (as far as I know).

the use of XLM functions in defined names requires the use of a macro-enabled workbook format

[XOR LX]

@oeldere,

Not sure what you mean by "then choose F2 and F4"

The sheet uses the EVALUATE function, which is an Excel version 4.0 macro function still available in most versions; though any workbook using that feature I understand must be saved as macro-enabled - the reason it didn't appear in the non-macro enabled version you posted (you should have seen a message when you tried to save it).

Anyway, it's all good now.

Thanks a lot