Solved: Strange behaviour of =cell("filename",A1) formula

[vanhunk]

Hi There,

I have discovered something very interesting and I would like someone to explain it to me.

What I have discovered is, that as long as there is a formula, any formula, somewhere in a workbook that has a result of "#REF!", formulas that use "cell("filename",A1)" anywhere in the formula will be updated before the file is saved under a new name, while saving.

In other words the "cell("filename",A1)" formula is already updated with the new name in the saved version. Without the "#REF!" formula, the saved version still has the old file name and only updates when the file is opened again. :think:

It is actually quite useful.

Thanks

[Bob Phillips]

Useful maybe, but do we want a formula with an error floating about?

Does the same thing happen if you create a defined name that resolves to #REF instead of a formula in a cell?

[vanhunk]

Hi xld,

Unfortunately not. Only works if the formula is on one of the sheets.

vanhunk

[Bob Phillips]

Yeah I tried it myself and it keeps firing the browse dialog - not good :)

[vanhunk]

I marked it SOLVED as I believe there is nobody out there that can give an answer.