Hi Bob / All,

Thank you for all the great material that you contribute on the web-site and
in the newsgroups.

I have been using your colour counter method for awhile now and I love it. I
just ran into a problem I was hoping you could help me with. I have a helper
column set up to sort a range by font color with the formula "=colorindex(a1,
true)" copied down some 150 rows or so. This works great.

However, if I paste a "cut" selection into a new cell/row, I get a #value
error in my helper column. The formula in the column has changed to
"=colorindex(#Ref!,true)".

This only happens if I paste a "cut" selection into the row/cell. If I paste
a copied selection there is no problem.

Have you / anybody seen this behaviour and can you help me out? Am I missing
something silly?

Thanks in advance.

Dominic

If you have a reference to a cell in row 1, and then you delete
row 1, those references will change to #REF errors. This is true
of any Excel formula; it has nothing to do with Bob's code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Dominic" wrote in message
...
Hi Bob / All,

Thank you for all the great material that you contribute on the
web-site and
in the newsgroups.

I have been using your colour counter method for awhile now and
I love it. I
just ran into a problem I was hoping you could help me with. I
have a helper
column set up to sort a range by font color with the formula
"=colorindex(a1,
true)" copied down some 150 rows or so. This works great.

However, if I paste a "cut" selection into a new cell/row, I
get a #value
error in my helper column. The formula in the column has
changed to
"=colorindex(#Ref!,true)".

This only happens if I paste a "cut" selection into the
row/cell. If I paste
a copied selection there is no problem.

Have you / anybody seen this behaviour and can you help me out?
Am I missing
something silly?

Thanks in advance.

Dominic

Thanks Chip.

Yes, I have seen that behaviour before. But I am not deleting anything. And
the formula referencing the "cut" cell doesn't error, the formula referecing
the "cut to" cell gives an error. If that makes sense.

"Chip Pearson" wrote:

If you have a reference to a cell in row 1, and then you delete
row 1, those references will change to #REF errors. This is true
of any Excel formula; it has nothing to do with Bob's code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Dominic" wrote in message
...
Hi Bob / All,

Thank you for all the great material that you contribute on the
web-site and
in the newsgroups.

I have been using your colour counter method for awhile now and
I love it. I
just ran into a problem I was hoping you could help me with. I
have a helper
column set up to sort a range by font color with the formula
"=colorindex(a1,
true)" copied down some 150 rows or so. This works great.

However, if I paste a "cut" selection into a new cell/row, I
get a #value
error in my helper column. The formula in the column has
changed to
"=colorindex(#Ref!,true)".

This only happens if I paste a "cut" selection into the
row/cell. If I paste
a copied selection there is no problem.

Have you / anybody seen this behaviour and can you help me out?
Am I missing
something silly?

Thanks in advance.

Dominic

You need to think of the cell as a container (like a box containing data) and
not as a particular location.

When you cut and paste a cell, that "container" moves and Excel will adjust
any cell that referenced it in a formula so the formulas are still valid.
The cell "moves" to its new location and a new cell is created to take its
place.

But when you drop that cell on top of another (paste it over another) the
one it is dropped on is wiped out, destroyed. So any formula that pointed at
the old cell now is pointing at nothing, giving you the "#REF" error
(REFERENCE, i.e. there is an invalid reference to another cell - the
destroyed one).

--
- K Dales


"Dominic" wrote:

Thanks Chip.

Yes, I have seen that behaviour before. But I am not deleting anything. And
the formula referencing the "cut" cell doesn't error, the formula referecing
the "cut to" cell gives an error. If that makes sense.

"Chip Pearson" wrote:

If you have a reference to a cell in row 1, and then you delete
row 1, those references will change to #REF errors. This is true
of any Excel formula; it has nothing to do with Bob's code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Dominic" wrote in message
...
Hi Bob / All,

Thank you for all the great material that you contribute on the
web-site and
in the newsgroups.

I have been using your colour counter method for awhile now and
I love it. I
just ran into a problem I was hoping you could help me with. I
have a helper
column set up to sort a range by font color with the formula
"=colorindex(a1,
true)" copied down some 150 rows or so. This works great.

However, if I paste a "cut" selection into a new cell/row, I
get a #value
error in my helper column. The formula in the column has
changed to
"=colorindex(#Ref!,true)".

This only happens if I paste a "cut" selection into the
row/cell. If I paste
a copied selection there is no problem.

Have you / anybody seen this behaviour and can you help me out?
Am I missing
something silly?

Thanks in advance.

Dominic

K,

Thanks, that makes sense.

"K Dales" wrote:

You need to think of the cell as a container (like a box containing data) and
not as a particular location.

When you cut and paste a cell, that "container" moves and Excel will adjust
any cell that referenced it in a formula so the formulas are still valid.
The cell "moves" to its new location and a new cell is created to take its
place.

But when you drop that cell on top of another (paste it over another) the
one it is dropped on is wiped out, destroyed. So any formula that pointed at
the old cell now is pointing at nothing, giving you the "#REF" error
(REFERENCE, i.e. there is an invalid reference to another cell - the
destroyed one).

--
- K Dales


"Dominic" wrote:

Thanks Chip.

Yes, I have seen that behaviour before. But I am not deleting anything. And
the formula referencing the "cut" cell doesn't error, the formula referecing
the "cut to" cell gives an error. If that makes sense.

"Chip Pearson" wrote:

If you have a reference to a cell in row 1, and then you delete
row 1, those references will change to #REF errors. This is true
of any Excel formula; it has nothing to do with Bob's code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Dominic" wrote in message
...
Hi Bob / All,

Thank you for all the great material that you contribute on the
web-site and
in the newsgroups.

I have been using your colour counter method for awhile now and
I love it. I
just ran into a problem I was hoping you could help me with. I
have a helper
column set up to sort a range by font color with the formula
"=colorindex(a1,
true)" copied down some 150 rows or so. This works great.

However, if I paste a "cut" selection into a new cell/row, I
get a #value
error in my helper column. The formula in the column has
changed to
"=colorindex(#Ref!,true)".

This only happens if I paste a "cut" selection into the
row/cell. If I paste
a copied selection there is no problem.

Have you / anybody seen this behaviour and can you help me out?
Am I missing
something silly?

Thanks in advance.

Dominic