Reference absolute cell values
 

Hi -

I'm using the following formulas to count the number of specific
characters ($K$147) in a given cell and increment by 1:

In cell H74:
=IF($H$5=0,0,(LEN($H$5)-LEN(SUBSTITUTE($H$5,$K$147,""))+1))
In cell I74:
=IF($I$5=0,0,(LEN($I$5)-LEN(SUBSTITUTE($I$5,$K$147,""))+1))

The problem is if a user CUTS the data in cell H5 and pastes it into
cell I5, then the formula in cell I74 fails as follows:
=IF(#REF!=0,0,(LEN(#REF!)-LEN(SUBSTITUTE(#REF!,$K$147,""))+1))

I've tried using the INDIRECT command to create a reference table of
the values in H74 & I74 but the character in cell $K$147 is a Carriage
Return which INDIRECT doesn't seem to recognize.

Any ideas would be welcome. Apologies in advance if this isn't clear.

Eric

Reference absolute cell values
 

Try it like this:

=IF(INDIRECT("I5")=0,0,(LEN(INDIRECT("I5"))-LEN(SUBSTITUTE(INDIRECT("I5"),$K$147,""))+1))

That will *always* refer to I5

--
Biff
Microsoft Excel MVP


wrote in message
...
Hi -

I'm using the following formulas to count the number of specific
characters ($K$147) in a given cell and increment by 1:

In cell H74:
=IF($H$5=0,0,(LEN($H$5)-LEN(SUBSTITUTE($H$5,$K$147,""))+1))
In cell I74:
=IF($I$5=0,0,(LEN($I$5)-LEN(SUBSTITUTE($I$5,$K$147,""))+1))

The problem is if a user CUTS the data in cell H5 and pastes it into
cell I5, then the formula in cell I74 fails as follows:
=IF(#REF!=0,0,(LEN(#REF!)-LEN(SUBSTITUTE(#REF!,$K$147,""))+1))

I've tried using the INDIRECT command to create a reference table of
the values in H74 & I74 but the character in cell $K$147 is a Carriage
Return which INDIRECT doesn't seem to recognize.

Any ideas would be welcome. Apologies in advance if this isn't clear.

Eric

Reference absolute cell values
 

On Feb 8, 10:24*pm, "T. Valko" wrote:
Try it like this:

=IF(INDIRECT("I5")=0,0,(LEN(INDIRECT("I5"))-LEN(SUBSTITUTE(INDIRECT("I5"),$*K$147,""))+1))

That will *always* refer to I5

--
Biff
Microsoft Excel MVP

wrote in message

...



Hi -

I'm using the following formulas to count the number of specific
characters ($K$147) in a given cell and increment by 1:

In cell H74:
=IF($H$5=0,0,(LEN($H$5)-LEN(SUBSTITUTE($H$5,$K$147,""))+1))
In cell I74:
=IF($I$5=0,0,(LEN($I$5)-LEN(SUBSTITUTE($I$5,$K$147,""))+1))

The problem is if a user CUTS the data in cell H5 and pastes it into
cell I5, then the formula in cell I74 fails as follows:
=IF(#REF!=0,0,(LEN(#REF!)-LEN(SUBSTITUTE(#REF!,$K$147,""))+1))

I've tried using the INDIRECT command to create a reference table of
the values in H74 & I74 but the character in cell $K$147 is a Carriage
Return which INDIRECT doesn't seem to recognize.

Any ideas would be welcome. Apologies in advance if this isn't clear.

Eric- Hide quoted text -

- Show quoted text -

Solution worked great. Thanks for the help.

Reference absolute cell values
 

Solution worked great. Thanks for the help.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


wrote in message
...
On Feb 8, 10:24 pm, "T. Valko" wrote:
Try it like this:

=IF(INDIRECT("I5")=0,0,(LEN(INDIRECT("I5"))-LEN(SUBSTITUTE(INDIRECT("I5"),$*K$147,""))+1))

That will *always* refer to I5

--
Biff
Microsoft Excel MVP

wrote in message

...



Hi -

I'm using the following formulas to count the number of specific
characters ($K$147) in a given cell and increment by 1:

In cell H74:
=IF($H$5=0,0,(LEN($H$5)-LEN(SUBSTITUTE($H$5,$K$147,""))+1))
In cell I74:
=IF($I$5=0,0,(LEN($I$5)-LEN(SUBSTITUTE($I$5,$K$147,""))+1))

The problem is if a user CUTS the data in cell H5 and pastes it into
cell I5, then the formula in cell I74 fails as follows:
=IF(#REF!=0,0,(LEN(#REF!)-LEN(SUBSTITUTE(#REF!,$K$147,""))+1))

I've tried using the INDIRECT command to create a reference table of
the values in H74 & I74 but the character in cell $K$147 is a Carriage
Return which INDIRECT doesn't seem to recognize.

Any ideas would be welcome. Apologies in advance if this isn't clear.

Eric- Hide quoted text -

- Show quoted text -

Solution worked great. Thanks for the help.