Formula detection - Unique vs Copied?
 

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or unique?
Or perhaps the question is - Is someone willing to share an easy way of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony

Formula detection - Unique vs Copied?
 

Why would it matter? I can enter two similar formulas without copying. I
think your question should be can you recognize that a formula is used more
than once in a workbook. It is extremely difficult unless you are looking
for an EXACT match. You can check to see if specific locations are
referenced like "B4" but you have problems if it is reference in different
sheets like Sheet1!B4 verses just B4. If you search for B4 you will get both
locations.


"Bony Pony" wrote:

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or unique?
Or perhaps the question is - Is someone willing to share an easy way of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony

Formula detection - Unique vs Copied?
 

Thanks for restating my question. Allow me to try again.

I want to check for any non-dragged formulas in a range of formulas to check
consistency and flag is a well meaning user has changed something they
shouldn't have. Simply stated

Scenario 1
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d1+d2 =e1+e2

should show A as a unique formula and flag others as dragged

Scenario 2
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d3+d4 =e1+e2

should highlight that D is different.

Basically mimic the internal error check "Formulas inconsistent with other
formulas in region" feature.

So my question remains as stated.

Regards,
Bony





"Joel" wrote:

Why would it matter? I can enter two similar formulas without copying. I
think your question should be can you recognize that a formula is used more
than once in a workbook. It is extremely difficult unless you are looking
for an EXACT match. You can check to see if specific locations are
referenced like "B4" but you have problems if it is reference in different
sheets like Sheet1!B4 verses just B4. If you search for B4 you will get both
locations.


"Bony Pony" wrote:

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or unique?
Or perhaps the question is - Is someone willing to share an easy way of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony

Formula detection - Unique vs Copied?
 

Look at VBA Help menu form the following

InconsistentFormula Property


Application.ErrorCheckingOptions.InconsistentFormu la = True


I'm not sure if the On Error will trap this condition. Try it. Ive never
used the option.

"Bony Pony" wrote:

Thanks for restating my question. Allow me to try again.

I want to check for any non-dragged formulas in a range of formulas to check
consistency and flag is a well meaning user has changed something they
shouldn't have. Simply stated

Scenario 1
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d1+d2 =e1+e2

should show A as a unique formula and flag others as dragged

Scenario 2
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d3+d4 =e1+e2

should highlight that D is different.

Basically mimic the internal error check "Formulas inconsistent with other
formulas in region" feature.

So my question remains as stated.

Regards,
Bony





"Joel" wrote:

Why would it matter? I can enter two similar formulas without copying. I
think your question should be can you recognize that a formula is used more
than once in a workbook. It is extremely difficult unless you are looking
for an EXACT match. You can check to see if specific locations are
referenced like "B4" but you have problems if it is reference in different
sheets like Sheet1!B4 verses just B4. If you search for B4 you will get both
locations.


"Bony Pony" wrote:

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or unique?
Or perhaps the question is - Is someone willing to share an easy way of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony

Formula detection - Unique vs Copied?
 

Maybe you should protect the worksheet?
--
Jim Cone
Portland, Oregon USA



"Bony Pony"

wrote in message
Thanks for restating my question. Allow me to try again.

I want to check for any non-dragged formulas in a range of formulas to check
consistency and flag is a well meaning user has changed something they
shouldn't have. Simply stated

Scenario 1
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d1+d2 =e1+e2

should show A as a unique formula and flag others as dragged

Scenario 2
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d3+d4 =e1+e2

should highlight that D is different.

Basically mimic the internal error check "Formulas inconsistent with other
formulas in region" feature.

So my question remains as stated.

Regards,
Bony





"Joel" wrote:

Why would it matter? I can enter two similar formulas without copying. I
think your question should be can you recognize that a formula is used more
than once in a workbook. It is extremely difficult unless you are looking
for an EXACT match. You can check to see if specific locations are
referenced like "B4" but you have problems if it is reference in different
sheets like Sheet1!B4 verses just B4. If you search for B4 you will get both
locations.


"Bony Pony" wrote:

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or unique?
Or perhaps the question is - Is someone willing to share an easy way of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony

Formula detection - Unique vs Copied?
 

Hi Joel,
This seems to work in principle:
Sub formula_consistent()

Dim mycell As Variant

Application.ErrorCheckingOptions.InconsistentFormu la = True

For Each mycell In Selection ' Selection contains a range of formulas some
of which are inconsistent.
If mycell.Errors.Item(xlInconsistentFormula).Value = True Then
MsgBox "Inconsistent"
Else
MsgBox "OK"
End If
Next
End Sub

Often just knowing where to start is the solution

Many thanks for this!

Bony

"Joel" wrote:

Look at VBA Help menu form the following

InconsistentFormula Property


Application.ErrorCheckingOptions.InconsistentFormu la = True


I'm not sure if the On Error will trap this condition. Try it. Ive never
used the option.

"Bony Pony" wrote:

Thanks for restating my question. Allow me to try again.

I want to check for any non-dragged formulas in a range of formulas to check
consistency and flag is a well meaning user has changed something they
shouldn't have. Simply stated

Scenario 1
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d1+d2 =e1+e2

should show A as a unique formula and flag others as dragged

Scenario 2
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d3+d4 =e1+e2

should highlight that D is different.

Basically mimic the internal error check "Formulas inconsistent with other
formulas in region" feature.

So my question remains as stated.

Regards,
Bony





"Joel" wrote:

Why would it matter? I can enter two similar formulas without copying. I
think your question should be can you recognize that a formula is used more
than once in a workbook. It is extremely difficult unless you are looking
for an EXACT match. You can check to see if specific locations are
referenced like "B4" but you have problems if it is reference in different
sheets like Sheet1!B4 verses just B4. If you search for B4 you will get both
locations.


"Bony Pony" wrote:

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or unique?
Or perhaps the question is - Is someone willing to share an easy way of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony

Formula detection - Unique vs Copied?
 

Lateral thinking!! Good one but not what I need. Thanks anyway!


"Jim Cone" wrote:

Maybe you should protect the worksheet?
--
Jim Cone
Portland, Oregon USA



"Bony Pony"

wrote in message
Thanks for restating my question. Allow me to try again.

I want to check for any non-dragged formulas in a range of formulas to check
consistency and flag is a well meaning user has changed something they
shouldn't have. Simply stated

Scenario 1
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d1+d2 =e1+e2

should show A as a unique formula and flag others as dragged

Scenario 2
A B C D E
=a1+a2 =b1+b2 =c1+c2 =d3+d4 =e1+e2

should highlight that D is different.

Basically mimic the internal error check "Formulas inconsistent with other
formulas in region" feature.

So my question remains as stated.

Regards,
Bony





"Joel" wrote:

Why would it matter? I can enter two similar formulas without copying. I
think your question should be can you recognize that a formula is used more
than once in a workbook. It is extremely difficult unless you are looking
for an EXACT match. You can check to see if specific locations are
referenced like "B4" but you have problems if it is reference in different
sheets like Sheet1!B4 verses just B4. If you search for B4 you will get both
locations.


"Bony Pony" wrote:

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or unique?
Or perhaps the question is - Is someone willing to share an easy way of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony

Formula detection - Unique vs Copied?
 

Another way is to compare the R1C1 version of the formula: they will be the
same if the formula has been copied. Doing it that way allows you to also
check noncontiguous formula if you wish.

regards
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

"Bony Pony" wrote in message
...
Thanks for restating my question. Allow me to try again.

I want to check for any non-dragged formulas in a range of formulas to
check
consistency and flag is a well meaning user has changed something they
shouldn't have. Simply stated

Scenario 1
A B C D
E
=a1+a2 =b1+b2 =c1+c2 =d1+d2 =e1+e2

should show A as a unique formula and flag others as dragged

Scenario 2
A B C D
E
=a1+a2 =b1+b2 =c1+c2 =d3+d4 =e1+e2

should highlight that D is different.

Basically mimic the internal error check "Formulas inconsistent with other
formulas in region" feature.

So my question remains as stated.

Regards,
Bony





"Joel" wrote:

Why would it matter? I can enter two similar formulas without copying.
I
think your question should be can you recognize that a formula is used
more
than once in a workbook. It is extremely difficult unless you are
looking
for an EXACT match. You can check to see if specific locations are
referenced like "B4" but you have problems if it is reference in
different
sheets like Sheet1!B4 verses just B4. If you search for B4 you will get
both
locations.


"Bony Pony" wrote:

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or
unique?
Or perhaps the question is - Is someone willing to share an easy way
of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony

Formula detection - Unique vs Copied?
 

Hi Charles,
Brilliant!!!! Thank you for the tip. Woo hoo!!

Regards and gratitude,
Bony

"Charles Williams" wrote:

Another way is to compare the R1C1 version of the formula: they will be the
same if the formula has been copied. Doing it that way allows you to also
check noncontiguous formula if you wish.

regards
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

"Bony Pony" wrote in message
...
Thanks for restating my question. Allow me to try again.

I want to check for any non-dragged formulas in a range of formulas to
check
consistency and flag is a well meaning user has changed something they
shouldn't have. Simply stated

Scenario 1
A B C D
E
=a1+a2 =b1+b2 =c1+c2 =d1+d2 =e1+e2

should show A as a unique formula and flag others as dragged

Scenario 2
A B C D
E
=a1+a2 =b1+b2 =c1+c2 =d3+d4 =e1+e2

should highlight that D is different.

Basically mimic the internal error check "Formulas inconsistent with other
formulas in region" feature.

So my question remains as stated.

Regards,
Bony





"Joel" wrote:

Why would it matter? I can enter two similar formulas without copying.
I
think your question should be can you recognize that a formula is used
more
than once in a workbook. It is extremely difficult unless you are
looking
for an EXACT match. You can check to see if specific locations are
referenced like "B4" but you have problems if it is reference in
different
sheets like Sheet1!B4 verses just B4. If you search for B4 you will get
both
locations.


"Bony Pony" wrote:

Hi everyone,
Happy New Year to all who believe that sort of thing!

Is there an easy way to determine if adjacent formulas are copied or
unique?
Or perhaps the question is - Is someone willing to share an easy way
of
determining if adjacent formulas are copied or unique?

Thanks in advance!
Bony