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

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
>

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
> >

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
> > >

True, it did recalculate as you expected. The problem is something else.
(Not the different version problem either. Someone here has a copy of the
remote app on his local workstation, with the latest version of Excel, and we
still have the problem.)

I was able to make the sheets recalculate by looping through each sheet in
Workbook_Open and repasting the formula in the columns, but I'd like to know
why it doesn't work.

What it appears is that it's not necessarily Sheet1's data (from that
column) that appears on all other sheets, but rather whatever sheet was the
active sheet when the book was saved shows that calculated values on all the
other sheets.

I'm gonna keep pluggin away at it.

"Tom Ogilvy" wrote:

> 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
> > > >