Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Workbook, Formula Not Working
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Workbook, Formula Not Working
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Workbook, Formula Not Working
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Workbook, Formula Not Working
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Workbook, Formula Not Working
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Range in Other workbook - not working in SUMPRODUCT formula? | Excel Programming | |||
Copy formula not working?! | Excel Discussion (Misc queries) | |||
Copy sum formula to Other columns not working | Excel Worksheet Functions | |||
copy & paste formula between spreadsheets - not working | Excel Worksheet Functions | |||
Copy Formula to Different Workbook | Excel Worksheet Functions |