Make named formula volatile ?

[Jaafar Tribak]

Hi.

I have defined a named formula namely HasComment which returns TRUE if the cell has a comment and which refers to the following XML function :
=GET.CELL(46)
When I enter the named formula in a cell it works but it is not volatile .
I have tried linking it to the Now() Function as follows :
=OR(GET.CELL(46),NOW()*0)
But it is still not volatile and I still have to select the cell and press F2 to update it.

Any thoughts ?

 

[jasonb75]

How about making the cell volatile? =AND(HasComment,NOW())

 

[mikerickson]

How about something like this. It isn't a volatile function, it uses a non-volatile function with the entire sheet as its precedent.

=IF(ROWS(1:65536)>0, GET.CELL(46, A1), )

Another problem was that the second argument of GET.CELL was not specified.

 

[dk]

Hi Jafaar

I did a brief test and this seemed to work:

=GET.CELL(46,INDIRECT("RC",FALSE))

Got the basic idea from here: http://www.sulprobil.com/html/get_cell.html

HTH
DK

 

[Jaafar Tribak]

Wow thanks - both solutions work perfectly !

Mike .
why is that the ROWS Function seems to force an update while Now doesn't ? They are both volatile though.

DK.
Nice link and nice use of the INDIRECT function - I'll take a closer look at the link - I am sure the info will be very useful .

 

[Jaafar Tribak]

How about making the cell volatile? =AND(HasComment,NOW())

Sorry jason I didn't see your answer.

That works as well but I wanted to know why having the NOW volatile function inside the named formula didn't work. - Thanks

 

[jasonb75]

Another problem was that the second argument of GET.CELL was not specified.

I noticed that, but didn't realise the relevance until you mentioned it, thanks for making me look again.

Always thought that with no range specified it defaulted to Application.Caller, not ActiveCell.

Maybe of interest

http://www.mrexcel.com/forum/showpost.php?p=95601&postcount=1

 

[Jaafar Tribak]

I noticed that, but didn't realise the relevance until you mentioned it, thanks for making me look again.

Always thought that with no range specified it defaulted to Application.Caller, not ActiveCell.

That's what I thought as well.

I still wish to know why the Rows always evaluates to TRUE and therefore forces an update of the named formula whereas the NOW function doesn't !

 

[jasonb75]

It isn't a volatile function, it uses a non-volatile function with the entire sheet as its precedent.

Maybe Mike can elaborate a little more, it would appear that with defined names, only the active cell is volatile.

 

[mikerickson]

"why is that the ROWS Function seems to force an update while Now doesn't ? They are both volatile though."

ROWS doesn't force an update.
The recalc only happens when one of the precedent cells is changed. Since the argument of ROWS is 1:65536, every cell on the sheet is a precedent cell. COUNTA(1:65536) or SUM(1:65536) would work as well as ROWS, except that putting them in a cell would cause a circular reference
The reason for using ROWS is that it allows a circular reference without causing an error.