Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
Hello,
Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
Alexy,
You can link to another sheet with a formula like ='Other Sheet Name'!A1 -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Alexey E. Kolmyk" wrote in message ... Hello, Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
The cell would not change unless you did a calculate I don't think, so you
would have to store the value of the cell, then make a comparison using that calculate event, then store the value for the next time. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... Hello, Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
There is no other way?
I think about your solution... So, it may be: 1. Detect all "interworksheet" cells. 2. Save all values these cells. 3. On Calculate event for my worksheet I compare saved values and new values. Have I understood you aright? P.S.: How I can detect "interworksheet" formula? -----Original Message----- The cell would not change unless you did a calculate I don't think, so you would have to store the value of the cell, then make a comparison using that calculate event, then store the value for the next time. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... Hello, Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
That is pretty much it.
set rng = Cells.SpecialCells(xlFormulas) will get all the cells with formulas. You would have to loop through those and find out which ones have a formula that contains an exclamation point. Dim cell as range, rng as range Dim rng1 as range for each cell in rng if instr(cell.Formula,"!") then if rng1 is nothing then set rng1 = cell else set rng1 = Union(rng1, cell) end if end if Next -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... There is no other way? I think about your solution... So, it may be: 1. Detect all "interworksheet" cells. 2. Save all values these cells. 3. On Calculate event for my worksheet I compare saved values and new values. Have I understood you aright? P.S.: How I can detect "interworksheet" formula? -----Original Message----- The cell would not change unless you did a calculate I don't think, so you would have to store the value of the cell, then make a comparison using that calculate event, then store the value for the next time. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... Hello, Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
But this code founds some formula like this '="!"'.
Has it? -----Original Message----- That is pretty much it. set rng = Cells.SpecialCells(xlFormulas) will get all the cells with formulas. You would have to loop through those and find out which ones have a formula that contains an exclamation point. Dim cell as range, rng as range Dim rng1 as range for each cell in rng if instr(cell.Formula,"!") then if rng1 is nothing then set rng1 = cell else set rng1 = Union(rng1, cell) end if end if Next -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... There is no other way? I think about your solution... So, it may be: 1. Detect all "interworksheet" cells. 2. Save all values these cells. 3. On Calculate event for my worksheet I compare saved values and new values. Have I understood you aright? P.S.: How I can detect "interworksheet" formula? -----Original Message----- The cell would not change unless you did a calculate I don't think, so you would have to store the value of the cell, then make a comparison using that calculate event, then store the value for the next time. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... Hello, Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
You can certainly add more detailed checks - parsing out the front of the
formula and checking it against all worksheet names and so forth. Please make your code as robust as you feel is necessary. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... But this code founds some formula like this '="!"'. Has it? -----Original Message----- That is pretty much it. set rng = Cells.SpecialCells(xlFormulas) will get all the cells with formulas. You would have to loop through those and find out which ones have a formula that contains an exclamation point. Dim cell as range, rng as range Dim rng1 as range for each cell in rng if instr(cell.Formula,"!") then if rng1 is nothing then set rng1 = cell else set rng1 = Union(rng1, cell) end if end if Next -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... There is no other way? I think about your solution... So, it may be: 1. Detect all "interworksheet" cells. 2. Save all values these cells. 3. On Calculate event for my worksheet I compare saved values and new values. Have I understood you aright? P.S.: How I can detect "interworksheet" formula? -----Original Message----- The cell would not change unless you did a calculate I don't think, so you would have to store the value of the cell, then make a comparison using that calculate event, then store the value for the next time. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... Hello, Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
So, there is no way to avoid formula string parsing?
May be some property exists? -----Original Message----- You can certainly add more detailed checks - parsing out the front of the formula and checking it against all worksheet names and so forth. Please make your code as robust as you feel is necessary. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... But this code founds some formula like this '="!"'. Has it? -----Original Message----- That is pretty much it. set rng = Cells.SpecialCells(xlFormulas) will get all the cells with formulas. You would have to loop through those and find out which ones have a formula that contains an exclamation point. Dim cell as range, rng as range Dim rng1 as range for each cell in rng if instr(cell.Formula,"!") then if rng1 is nothing then set rng1 = cell else set rng1 = Union(rng1, cell) end if end if Next -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... There is no other way? I think about your solution... So, it may be: 1. Detect all "interworksheet" cells. 2. Save all values these cells. 3. On Calculate event for my worksheet I compare saved values and new values. Have I understood you aright? P.S.: How I can detect "interworksheet" formula? -----Original Message----- The cell would not change unless you did a calculate I don't think, so you would have to store the value of the cell, then make a comparison using that calculate event, then store the value for the next time. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... Hello, Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. . . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links to Other Worksheet
No, no property exists.
You can use the find method to search for the string within the formulas. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... So, there is no way to avoid formula string parsing? May be some property exists? -----Original Message----- You can certainly add more detailed checks - parsing out the front of the formula and checking it against all worksheet names and so forth. Please make your code as robust as you feel is necessary. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... But this code founds some formula like this '="!"'. Has it? -----Original Message----- That is pretty much it. set rng = Cells.SpecialCells(xlFormulas) will get all the cells with formulas. You would have to loop through those and find out which ones have a formula that contains an exclamation point. Dim cell as range, rng as range Dim rng1 as range for each cell in rng if instr(cell.Formula,"!") then if rng1 is nothing then set rng1 = cell else set rng1 = Union(rng1, cell) end if end if Next -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... There is no other way? I think about your solution... So, it may be: 1. Detect all "interworksheet" cells. 2. Save all values these cells. 3. On Calculate event for my worksheet I compare saved values and new values. Have I understood you aright? P.S.: How I can detect "interworksheet" formula? -----Original Message----- The cell would not change unless you did a calculate I don't think, so you would have to store the value of the cell, then make a comparison using that calculate event, then store the value for the next time. -- Regards, Tom Ogilvy "Alexey E. Kolmyk" wrote in message ... Hello, Could you tell me, how to work with formula, that points to other sheet. I want to know, when cell value was changes - event Worksheet_Change does not raised. Thank you for help. . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links in XL worksheet | Excel Discussion (Misc queries) | |||
Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) | |||
Updating links from one worksheet to another worksheet | Excel Worksheet Functions | |||
Worksheet Links | Excel Discussion (Misc queries) | |||
Worksheet links | Excel Worksheet Functions |