If it did recalculate the whole sheet and not just the one cell, then I
suspect it performed as I intended. I can't say what is going on when the
workbook is created. My intent was to make the function volatile since
Excel doesn't see the references in the argument list as needing to have the
formula recalculate.

--
Regards,
Tom Ogilvy




"Charlie" wrote:

I tried your work-around but it actually had the opposite effect! Now the
workbook created on the server does not calculate Sheet2, although when I
clicked in the formula bar and pressed Enter it DID recalculate the whole
sheet instead of just the one cell.

We are using Citrix to run a remote app on a server. That app creates the
workbook and launches Excel to show it. The version of Excel on the server
is older than on my workstation (where I created the report template.) That
may be the problem. If my company won't upgrade the server version of Excel
I may have to rewrite the report template. :(

Thanks for the suggestion.

P.S. I checked the macro security option -- "Low" both places.


"Tom Ogilvy" wrote:

try changing the formula to

=EditDescription(ROW()+rand()*0,Description)

This should make it update on every recalculation.


--
Regards,
Tom Ogilvy


"Charlie" wrote:

Hi,

I have a workbook that uses a macro function in a formula

=EditDescription(ROW(),Description)

"Description" is a local (sheet-level) named range down one column on
several sheets, "EditDescription" is a string function to remove LineFeeds,
etc.

It works fine when the workbook is generated on our server, but when I copy
it to my local workstation and open it the formulas don't work. Sheet1 is
OK. What I see on the other sheets is the same description from Sheet1. If
I click in the formula bar and press Enter the formula recalculates
correctly. It picks up the correct description from the named range on that
sheet. If I drag the formula down the column again they all recalculate
correctly.

I've tried using the .Calculate method, and recalculating the sheet from
Tools--Options--Calculation. The only thing that works is clicking in the
formula bar or dragging the formula down the column.

Anyone know why this is happening and how to fix it?

TIA
Charlie