Reference absolute cell values

[googlegroup]

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

 

[T. Valko]

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

 

[googlegroup]

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












- Show quoted text -

Solution worked great. Thanks for the help.

 

[T. Valko]

Solution worked great. Thanks for the help.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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












- Show quoted text -

Solution worked great. Thanks for the help.