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.

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.

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.

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.


.

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.


.

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.


.



.

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.


.



.

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.


.



.



.

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.


.



.



.