Forum Discussion
Solved
Non-active references to text formated cells
Hello,
I have created aplication which returns data into empty worksheet called in Excel, in the example below called Raport3. That data is then referenced in other worksheets through
=IF(Raport3!B3="";"";Raport3!B3)
Unfortunately, the formula is not active until I do some operation on the cell, like opening it and clicking in the formula bar or running in the worksheet
REPLACE = WITH =
As I have some 5 worksheets in the file with Report3 references it is quite cumberome to run 5 times with replace = with =. Conversion to Macro is not an option as the report is generated in the app outside of corporate network and there is group policy ofn the files containing macros.
Is there a way to force those references to become active in the whole ocument?
Thanks in advance.
- Is calculation set to manual by any chance? Formula tab, Calculation options drop-down.
14 Replies
Hi, I suppose there is a control issue. If you remove te "v" on the options menu (see screenprint below) you can copy all formulas again. Then it worked.
Regards, DM
Grzegorz Bednarski wrote:
Hello,
I have created aplication which returns data into empty worksheet called in Excel, in the example below called Raport3. That data is then referenced in other worksheets through
=IF(Raport3!B3="";"";Raport3!B3)
Unfortunately, the formula is not active until I do some operation on the cell, like opening it and clicking in the formula bar or running in the worksheet
REPLACE = WITH =
As I have some 5 worksheets in the file with Report3 references it is quite cumberome to run 5 times with replace = with =. Conversion to Macro is not an option as the report is generated in the app outside of corporate network and there is group policy ofn the files containing macros.
Is there a way to force those references to become active in the whole ocument?
Thanks in advance.
Hello,
The formulas error checking option is unchecked.
Otherwise it returns an error ' this cell contains a formula and is not locked to protected it from being changed inadvertently' with top left corner marked green. I tried lock/unlock data in the worksheet containing the formulas but it did not help. Maybe some other ideas?
- Is calculation set to manual by any chance? Formula tab, Calculation options drop-down.
Hello,
The calculation of formulas is set to automatic. The data I am trying to reference is a report from extrnal app. it is generated as csv and pasted into excel as string values. I need to reference that data into main part of excel document. The cells are formatted as 'general' both in the source worksheet as in the worksheet with formulas.
Raport1,2, 3 are the worksheets with source data. Other worsheets take the data from there. E.g. see Ingredients Hazard Analysis. It is enough to click in the formula bar to activate the links. Otherwise those are dead.
- You could do a search and replace, looking for the = sign and replacing with the = sign.
